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
– Rizier123

2015-07-01 13:55:58 +00:00
Commented Jul 1, 2015 at 13:55 — Rizier123
– Rizier123, Commented Jul 1, 2015 at 13:55

chris85 · Accepted Answer · 2015-07-01 14:37:37Z

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 1, 2015 at 14:37

chris85

23.9k7 gold badges37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ka Tech Over a year ago

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!

Subin Over a year ago

What to do both $in_array1 and $in_array2 are the same ?

chris85 Over a year ago

@Subin It should still merge fine, no? Can you make a sample of your issue, or do you have an open question?

Subin Over a year ago

@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 stackoverflow.com/a/34591120/1372424

chris85 Over a year ago

@Subin Not reproducing here 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.

|

score 1 · Accepted Answer · 2015-11-10 07:43:27Z

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, 2015 at 7:43

answered Jul 1, 2015 at 14:02

user3119231

Collectives™ on Stack Overflow

MYSQL - PDO with multiple IN clause

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related