MYSQL - PDO with multiple IN clause

Question

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

`execute()` takes **1** parameter, which are the arguments... — Rizier123, Jul 01 '15 at 13:55

score 3 · Accepted Answer · answered Jul 01 '15 at 14:37

3

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.

answered Jul 01 '15 at 14:37

chris85

23,846
7
34
51

Thank you so much for explaining this to me. Honestly I didn't understand the purpose of placing the array in the execute(), but now I know its got to do with the placeholders which now makes sense! This has saved me from trying to do a query in a for loop, thanks again! – Ka Tech Jul 02 '15 at 06:38
What to do both $in_array1 and $in_array2 are the same ? – Subin Sep 10 '17 at 16:39
@Subin It should still merge fine, no? Can you make a sample of your issue, or do you have an open question? – chris85 Sep 10 '17 at 16:42
@chris85 Well if the array is same, the keys will be same and merged array will be equal to one array. I had to use this to solve it https://stackoverflow.com/a/34591120/1372424 – Subin Sep 10 '17 at 17:05
@Subin Not reproducing here https://3v4l.org/bcXY1... or maybe I'm not following. What do you mean by `merged array will be equal to one array`, thats the purpose of the merge. – chris85 Sep 10 '17 at 17:06
@chris85 I don't know why. But a simple array_merge didn't work. Instead doing array_values first and then array_merge worked. – Subin Sep 10 '17 at 17:31

score 1 · Answer 2 · 2015-11-10T07:43:27.877

1

Doesn't make sense. Look at this:

$query = $db->prepare("SELECT * FROM table WHERE value1 = ? AND value2 = ?");
$query ->execute(array($value1, $value2));

and this:

http://php.net/manual/en/pdo.prepare.php

edited Nov 10 '15 at 07:43

answered Jul 01 '15 at 14:02

MYSQL - PDO with multiple IN clause

2 Answers2