This question already has an answer here:
-
PDO binding values for MySQL IN statement
8 answers
-
PHP - Using PDO with IN clause array
8 answers
From the PHP Manual :
You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.
But can you use multiple values using question mark parameter markers? Or do you have to prepare() a new statement every time the number of values changes?
SELECT * FROM `table` WHERE `column` IN(?)
If this is allowed how do you make it work?
EDIT: The two previous questions indicated were both about named variables, which the manual says can't be bound to multiple parameters. I was asking about question mark (anonymous) variables.
Here's some code I whipped up to simulate the desired outcome:
<?php
function preprepare(&$query, &$data) {
preg_match_all('/\?/', $query, $matches, PREG_OFFSET_CAPTURE);
$num = count($matches[0]);
for ($i = $num; $i;) {
--$i;
if (array_key_exists($i, $data) && is_array($data[$i])) {
$query = substr_replace($query, implode(',', array_fill(0, count($data[$i]), '?')), $matches[0][$i][1], strlen($matches[0][$i][0]));
array_splice($data, $i, 1, $data[$i]);
}
}
}
$query = 'SELECT * FROM `table` WHERE `col1` = ? AND `col2` IN(?) AND `col3` = ? AND `col4` IN(?)';
$data = array('foo', array(1, 2, 3), 'bar', array(4, 2));
preprepare($query, $data);
var_dump($query, $data);
?>
It outputs:
array (size=2)
0 => string 'SELECT * FROM `table` WHERE `col1` = ? AND `col2` IN(?,?,?) AND `col3` = ? AND `col4` IN(?,?)' (length=93)
1 =>
array (size=7)
0 => string 'foo' (length=3)
1 => int 1
2 => int 2
3 => int 3
4 => string 'bar' (length=3)
5 => int 4
6 => int 2
The query and data can then be used in a normal PDO prepared statement. I don't know if it accounts for all PDO magic niceness but it works pretty well so far.
