PDO bind unknown number of parameters?

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.

@Kickstart Why not - if the other question is good and provide helpful answers. — Eiko, Jul 08 '16 at 13:33
@Eiko - why mark as duplicate several years after the same person answered the duplicate and gave essentially the same answer as an existing one here? — Kickstart, Jul 08 '16 at 13:53

score 12 · Answer 1 · edited Feb 23 '13 at 23:48

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.

George Cummins · Accepted Answer · 2011-05-20T12:28:49.897

4

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

edited May 20 '11 at 12:28

answered May 20 '11 at 12:18

George Cummins

28,485
8
71
90

8

Why would you go through the trouble of using named placeholders? Using a ? works just as well without the extra work? – Paystey May 21 '11 at 02:11

FlyingNimbus · Answer 3 · 2014-06-29T13:39:06.953

2

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

edited Jun 29 '14 at 13:39

answered Jun 29 '14 at 12:54

FlyingNimbus

453
2
5
11

1

It will be really appreciated if you provide some explanation to your code. – Illegal Argument Jun 29 '14 at 13:23
@IllegalArgument which part would you like explained? [join](http://php.net/manual/en/function.join.php), [array_map](http://php.net/manual/en/function.array-map.php) – Geoffrey Hale Sep 15 '15 at 22:35
1

@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. – nimmneun Mar 25 '16 at 10:42

score 0 · Answer 4 · answered Jul 08 '16 at 12:55

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.

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. — Your Common Sense, Jul 08 '16 at 12:59
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. — Kickstart, Jul 08 '16 at 13:21

PDO bind unknown number of parameters?

4 Answers4

Linked