PDO bind unknown number of parameters? [duplicate]

Question

$statement = $db->prepare('SELECT blah FROM blah_table WHERE blahID IN (:a, :b, :c)');

What if the number of parameters is unknown until run-time? The only thing I can think of doing is a hacky kind of building of the sql string to make as many parameter placeholders as I need.

Answer 1

Not really hacky, Loops are part of the language for looping a variable number of times.

$values = array('val1', 'val2');
$sql = 'SELECT * FROM Table Where column IN(';
$params = array();
foreach ($values as $val)
{
    $params[] = '?';
    $binds[] = $val;
}
$prep = $db->prepare($sql . join(', ', $params) . ')');
$i = 0;
foreach($binds as $bind){
   $prep->bindValue(++$i, $bind);
}
$prep->execute();

Loop over each value you need to bind, create an array of binding objects which you loop over after appending the SQL.

Answer 2

You can build the "IN (...)" string dynamically:

$in_string = '(';
foreach ( $array_of_parameters as $parameter ) {
    $in_string .= ':' . chr($i + 97) . ','; // Get the ASCII character
}
$in_string = substr($in_string, 0, -1) . ')';

$statement = $db->prepare("SELECT blah FROM blah_table WHERE blahID IN ($in_string)");

Answer 3

Just another shorter way of doing it.

$values = array(1, 2, 3, 4);
$sql = "SELECT * 
          FROM table
         WHERE column IN (" . join(',', array_map(function() { return '?'; }, $values)) . ")";
$db->prepare($sql);
$db->execute($values);