PHP PDO: Can I bind an array to an IN() condition?

Question

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'm not very good at explaining, so here's some psuedocode to demonstrate... 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?

Cheers!

Answer 1

Looking at PDO :Predefined Constants there is no PDO::PARAM_ARRAY which you would need as is listed on PDOStatement->bindParam

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type [, int $length [, mixed $driver_options ]]] )

So I don't think it is achievable.

Answer 2

i think soulmerge is right. you'll have to construct the query-string.

<?php
$ids     = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids), '?'));

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

// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
    $stmt->bindValue(($k+1), $id);

$stmt->execute();
?>

fix: thx dan, you were right. fixed the code (didn't test it though)

Answer 3

What database are you using? In PostgreSQL I like using ANY(array). So to reuse your example:

<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id = ANY (:an_array)'
);
$stmt->bindParam('an_array',$ids);
$stmt->execute();
?>

Unfortunately this is pretty non-portable.

On other databases you'll need to make up your own magic as others have been mentioning. You'll want to put that logic into a class/function to make it reusable throughout your program of course. Take a look at the comments on mysql_query page on PHP.NET for some more thoughts on the subject and examples of this scenario.

Answer 4

Solution from EvilRygy didn't worked for me. In Postgres you can do another workaround:

$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id = ANY (string_to_array(:an_array, ','))'
);
$stmt->bindParam(':an_array', implode(',', $ids));
$stmt->execute();