I have an array in php containing strings, which I want to use in a query with Red Bean MySQL in the following manner:
$someString = '\'abc\',\'def\',\'ghi\'';
R::getAll("select * from table where name not in (:list)", array(':list'=> $someString));
The problem is that the list is not being evaluated correctly no matter how I set the values in the array string, and the names abc, def, ghi are returned in the result. I've tried the following:
$someString = '\'abc\',\'def\',\'ghi\''
$someString = 'abc\',\'def\',\'ghi'
$someString = 'abc,def,ghi'
running the query in the SQL server manually works and I don't get those values returned, but running it within the php code with redbean is not working, and it seems that the list is not being interpreted correctly syntax-wise. Can anyone shed some light on the matter?
Thanks to RyanVincent's comment I managed to solve the issue using positional parameters in the query, or more specifically, the R::genSlots function.
replaced the following:
$someString = '\'abc\',\'def\',\'ghi\'';
R::getAll("select * from table where name not in (:list)", array(':list'=> $someString));
with:
$someArray = array('abc', 'def', 'ghi');
R::getAll("select * from table where name not in (". R::genSlots($someArray) .")", $someArray);
This creates a $someArray length positions for parameters in the query, which are then filled with the values in the second parameter passed to the getAll function. Notice that in this case I used a set content array (3 variables) but it will work dynamically with any length array you will use.
Furthermore, this can also work for multiple positions in the query, for example:
$surnameArray = array('smith');
$arr1 = array('john', 'pete');
$arr2 = array('lucy', 'debra');
$mergedVarsArray = array_merge($surnameArray,$arr1);
$mergedVarsArray = array_merge($mergedVarsArray,$arr2);
R::getAll("select * from table where surname != ? and name in (." R::genSlots($arr1).") and name not in (". R::genSlots($arr2) .")", $mergedVarsArray);
This code will effectively be translated to:
select * from table where surname != 'smith' and name in ('john','pete') and name not in ('lucy', 'debra')
Each '?' placed in the query (or generated dynamically by genSlots() ) will be replaced by the correlating positioned item in the array passed as parameter to the query.
Hope this clarifies the usage to some people as I had no idea how to do this prior to the help I got here.