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.

Endless

Really, marking this as a duplicate of a newer question? – Unhook

@Unhook Why not - if the other question is good and provide helpful answers. – Zulemazullo

@Zulemazullo - why mark as duplicate several years after the same person answered the duplicate and gave essentially the same answer as an existing one here? – Unhook

Answer 1

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)");

Rm

Why would you go through the trouble of using named placeholders? Using a ? works just as well without the extra work? – Kreisler

Answer 2

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.

Kreisler

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);

Camm

It will be really appreciated if you provide some explanation to your code. – Indohittite

@IllegalArgument which part would you like explained? join, array_map – Hiddenite

@IllegalArgument He's utilizing the fact that array_map accepts a callback function which will be performed on each array element. It's a bit of an overkill though =) Something like '?' . str_repeat(',?', count($arr) - 1) or rtrim(str_repeat('?,', count($arr)), ',') would be good enough, since you just want a question mark for each element of the array. – Colobus

Answer 4

A way to do it without an explicit loop but giving specific markers rather than question marks.

$values_array = array(1, 3, 5, 7, 11);
$sql = "SELECT * 
          FROM table
         WHERE column IN (" . implode(",", array_map(function($in){return ':a'.$in;}, range(1, count($values)))) . ")";
$prep = $db->prepare($sql);
$i = 1;
foreach($values_array as $key=>$value)
{
   $prep->bindValue(':a'.$i++, $values_array[$key]);
}

This uses range to generate an array of numbers from 1 to the number of items in the array, then array_map to change those numbers to prepend them with a : and a character (in this case just a).

Only did this due to trying to debug something which used question marks and was failing. Problem turned out to be elsewhere (due to looping through the array to bind the values and having problems with bind using the reference to the variable, which was changed in each iteration of the array - hence landing up having the same value in each of the bind positions), but thought this might be useful to someone.

Unhook

Hope it will help no one. As using named placeholders in this case is just a [...]. While to avoid an explicit loop you just have to avoid it, as shown in the other answer. – Demmer

In noddy cases maybe true, but if you have (for example) a pair of unioned queries each having the same parameters being passed, being able to loop around the parms once to bind them to specific place holders rather than just in the order of the question marks is useful. – Unhook