PHP Red Bean MySQL multi-value binding evaluation in getAll()

Question

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?

Answer 1

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.