Categories
arrays pdo php prepared-statement where-in

Can I bind an array to an IN() condition in a PDO query?

615

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?

3

189

For something quick:

//$db = new PDO(...);
//$ids = array(...);

$qMarks = str_repeat('?,', count($ids) - 1) . '?';
$sth = $db->prepare("SELECT * FROM myTable WHERE id IN ($qMarks)");
$sth->execute($ids);

12

  • 7

    Excellent, I had not thought to use the input_parameters argument in this way. For those whose queries have more parameters than the IN list, you can use array_unshift and array_push to add the necessary arguments to the front and end of the array. Also, I prefer $input_list = substr(str_repeat(',?', count($ids)), 1);

    – orca

    May 31, 2012 at 2:34


  • 7

    You could also try str_repeat('?,', count($ids) - 1) . '?'. One less function call.

    – orca

    Jun 1, 2012 at 23:30

  • 5

    @erfling, this is a prepared statement, where’s the injection going to come from? I’ll be more than happy to make any corrections if you can you back that up with some actual proof of that.

    Nov 30, 2015 at 18:06

  • 5

    @erfling, yes, that is correct, and binding the params is exactly what we are doing in this example by sending execute an array of ids

    Dec 1, 2015 at 18:18

  • 5

    Oh indeed. Somehow missed the fact that you were passing the array. This does indeed appear to be safe and a good answer. My apologies.

    – erfling

    Dec 1, 2015 at 23:28

47

Is it so important to use IN statement? Try to use FIND_IN_SET op.

For example, there is a query in PDO like that

SELECT * FROM table WHERE FIND_IN_SET(id, :array)

Then you only need to bind an array of values, imploded with comma, like this one

$ids_string = implode(',', $array_of_smth); // WITHOUT WHITESPACES BEFORE AND AFTER THE COMMA
$stmt->bindParam('array', $ids_string);

and it’s done.

UPD: As some people pointed out in comments to this answer, there are some issues which should be stated explciitly.

  1. FIND_IN_SET doesn’t use index in a table, and it is still not implemented yet – see this record in the MYSQL bug tracker. Thanks to @BillKarwin for the notice.
  2. You can’t use a string with comma inside as a value of the array for search. It is impossible to parse such string in the right way after implode since you use comma symbol as a separator. Thanks to @VaL for the note.

In fine, if you are not heavily dependent on indexes and do not use strings with comma for search, my solution will be much easier, simpler, and faster than solutions listed above.

4

  • 28

    IN() can use an index, and counts as a range scan. FIND_IN_SET() can’t use an index.

    Oct 3, 2013 at 0:59

  • 1

    That’s a point. I didn’t know this. But any way there is no any requirements for performance in the question. For not so big tables it’s much more better and cleaner than separate class for generating query with different numbers of placeholders.

    Oct 8, 2013 at 0:03

  • 11

    Yes, but who has a not-so-big table these days? 😉

    Oct 8, 2013 at 0:17

  • 4

    Another problem with this approach that what if there will be string with comma inside? For example... FIND_IN_SET(description,'simple,search') will work, but FIND_IN_SET(description,'first value,text, with coma inside') will fail. So the function will search "first value", "text", "with coma inside" instead of desired "first value", "text, with coma inside"

    – VaL

    Feb 17, 2015 at 15:41