MYSQL - PDO with multiple IN clause

Ka Tech Published at Dev

Ka Tech

I can successfully implement a IN clause within a PDO prepared statement using the following code.

in_array = array(1,2,3);
$in  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($in_array);
$data = $stm->fetchAll();

How can I do the same for multiple $in? For example I've unsuccessfully tried the following:

in_array1 = array(1,2,3);
$in1  = str_repeat('?,', count($in_array) - 1) . '?';
in_array2 = array(4,5,1);
$in2  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE (my_value1 IN ($in1)) AND (my_value2 IN ($in2))";
$stm = $db->prepare($sql);
$stm->execute($in_array1,$in_array2);
$data = $stm->fetchAll();

I think its got to do with stm->execute but not sure, help appreciated

chris85

Your current query comes out as

SELECT * FROM my_table WHERE (my_value1 IN (?,?,?)) AND (my_value2 IN (?,?,?))

So your execute usage is incorrect, http://php.net/manual/en/pdostatement.execute.php. It should only be passing one array with values inside it.

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

I think using array_merge, http://php.net/manual/en/function.array-merge.php, will allow you to accomplish what you are trying

$stm->execute(array_merge($in_array1,$in_array2));

This way the execute is the equivalent of

$stm->execute(array(1,2,3,4,5,1));

This may seem incorrect because the array pairings are now gone but placeholder 1 (the first question mark) will map to 1, placeholder 4 to 4, and so on.

Collected from the Internet

Please contact [email protected] to delete if infringement.