John Davidson

Message:

I'm curious to know if it's possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an IN() condition.

I'd like to be able to do something like this:

<?php

$ids=array(1,2,3,7,8,9);

$db = new PDO(...);

$stmt = $db->prepare(

    'SELECT *

     FROM table

     WHERE id IN(:an_array)'

);

$stmt->bindParam('an_array',$ids);

$stmt->execute();

?>

And have PDO bind and quote all the values in the array.

At the moment I'm doing:

<?php

$ids = array(1,2,3,7,8,9);

$db = new PDO(...);

foreach($ids as &$val)

    $val=$db->quote($val); //iterate through array and quote

$in = implode(',',$ids); //create comma separated list

$stmt = $db->prepare(

    'SELECT *

     FROM table

     WHERE id IN('.$in.')'

);

$stmt->execute();

?>

Which certainly does the job, but just wondering if there's a built in solution I'm missing?

---

My Answer:
No, unfortunately PDO does not have built-in support for binding an array to an IN() condition in a query. You will need to manually quote and implode the array values as you are currently doing in your second example.

Alternatively, you can dynamically generate the placeholders for the array values and bind each value individually. Here's an example:

php
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);

$placeholders = implode(',', array_fill(0, count($ids), '?'));

$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN(' . $placeholders . ')'
);

foreach ($ids as $key => $value) {
$stmt->bindValue($key + 1, $value);
}

$stmt->execute();


This way, you are dynamically generating the placeholders for the array values and binding each value individually in the loop.