PHP - Using PDO with IN clause array

80,529

Solution 1

PDO is not good with such things. You need to create a string with placeholders dynamically and insert it into the query, while binding array values the usual way. With positional placeholders it would be like this:

$in  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($in_array);
$data = $stm->fetchAll();

In case there are other placeholders in the query, you could use the following approach (the code is taken from my PDO tutorial):

You could use array_merge() function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your query:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stm = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stm->execute($params);
$data = $stm->fetchAll();

In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g. :id0,:id1,:id2. So the code would be:

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0; // we are using an external counter 
        // because the actual array keys could be dangerous
foreach ($ids as $item)
{
    $key = ":id".$i++;
    $in .= ($in ? "," : "") . $key; // :id0,:id1,:id2
    $in_params[$key] = $item; // collecting values into a key-value array
}

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();

Luckily, for the named placeholders we don't have to follow the strict order, so we can merge our arrays in any order.

Solution 2

Variable substitution in PDO prepared statements doesn't support arrays. It's one for one.

You can get around that problem by generating the number of placeholders you need based on the length of the array.

$variables = array ('1', '2', '3');
$placeholders = str_repeat ('?, ',  count ($variables) - 1) . '?';

$query = $pdo -> prepare ("SELECT * FROM table WHERE column IN($placeholders)");
if ($query -> execute ($variables)) {
    // ...
}

Solution 3

As PDO doesn't seem to provide a good solution, you might as well consider using DBAL, which mostly follows PDO's API, but also adds some useful features http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

$stmt = $conn->executeQuery('SELECT * FROM articles WHERE id IN (?)',
    array(array(1, 2, 3, 4, 5, 6)),
    array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
);

There are probably some other packages out there that don't add complexity and don't obscure the interaction with the database (like most ORM do), but at the same time make small typical tasks bit easier.

Solution 4

An alternative version of PHP Delusions (@your-common-sense) using closures:

$filter      = ["min_price" => "1.98"];
$editions    = [1,2,10];

$editions = array_combine(
    array_map(function($i){ return ':id'.$i; }, array_keys($editions)),
    $editions
);
$in_placeholders = implode(',', array_keys($editions));
$sql = "SELECT * FROM books WHERE price >= :min_price AND edition IN ($in_placeholders)";
$stm = $pdo->prepare($sql);
$stm->execute(array_merge($filter,$editions));
$data = $stm->fetchAll();

Solution 5

I often use FIND_IN_SET instead of IN, like this:

$in_array = array(1, 2, 3);
$in_values = implode(',', $in_array);
$my_result = $wbdb->prepare("SELECT * FROM my_table WHERE FIND_IN_SET(my_value, :in_values)");
$my_result->execute(array(':in_values' => $in_values));
$my_results = $my_result->fetchAll();

It is not the best solution performance wise, but if the possible number options of the $in_array are limited than it is usually not an issue. I use it often for statuses where my_value is an enum field. Never had any issue with it.

Share:
80,529
iRector
Author by

iRector

Updated on May 29, 2021

Comments

  • iRector
    iRector over 2 years

    I'm using PDO to execute a statement with an IN clause that uses an array for its values:

    $in_array = array(1, 2, 3);
    $in_values = implode(',', $in_array);
    $my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN (".$in_values.")");
    $my_result->execute();
    $my_results = $my_result->fetchAll();
    

    The above code works perfectly fine, but my question is why this doesn't:
     $in_array = array(1, 2, 3);
        $in_values = implode(',', $in_array);
        $my_result = $wbdb->prepare("SELECT * FROM my_table WHERE my_value IN (:in_values)");
        $my_result->execute(array(':in_values' => $in_values));
        $my_results = $my_result->fetchAll();
    

    This code will return the item whose my_value equals the first item in the $in_array (1), but not the remaining items in the array (2, and 3).

  • iRector
    iRector over 10 years
    Thanks, though I see how that could cause some confusion if I were to need those placeholders elsewhere in my statement. Do you recommend any libraries? Maybe the one with the getAll function?
  • Your Common Sense
    Your Common Sense over 10 years
    Sure. I had no answer to that question for a while - so, I had to write it myself. You can find the link in my profile. Feel free to ask any questions regarding usage or whatever issues.
  • sanchez
    sanchez over 8 years
    Your answer helped me for the time being, thank you. I can't find any links in you profile. If your solution is not available any more, are there any intelligent libraries these days you could recommend?
  • Robert
    Robert over 7 years
    Can you explain what the -1 is for after the count()?
  • Robert
    Robert over 7 years
    Why count ($variables) - 1) . '?'; Why not just count($variable)
  • GordonM
    GordonM over 7 years
    @RobertRocha Because you need one fewer commas than there are variables
  • msoft
    msoft over 7 years
    @RobertRocha because of the last '?' in that line, since the last one must not have a comma behind it. That one is always added so we need one less from the array.
  • stack
    stack almost 7 years
    Wthat's that a in ... IN (?a) .. ?
  • Your Common Sense
    Your Common Sense almost 7 years
    @stack it's a thing you might write yourself to simplify the operation
  • stack
    stack almost 7 years
    Do you mean something like :a ?
  • Your Common Sense
    Your Common Sense almost 7 years
    @stack well you can write it whatever - :a or #a or %a. Whatever your program understands
  • Your Common Sense
    Your Common Sense almost 7 years
    @stack you can write a program that understands whatever syntax you want. But you have to write it first. A program.
  • Masivuye Cokile
    Masivuye Cokile almost 7 years
    Thanks, this helped me a lot, been looking for this for hours
  • barryp
    barryp over 6 years
    @RobertRocha I think a cleaner way to generate the placeholder array could be $in = implode(',', array_fill(0, count($in_array), '?')); - avoiding any weird off-by-one or string concatenation errors
  • digitai
    digitai about 6 years
    @YourCommonSense this is the best answer to such a common issue, thanks!
  • Bossman
    Bossman over 5 years
    Without the -1 $in = rtrim(str_repeat('?,', count($array)), ',');
  • Saleh Mosleh
    Saleh Mosleh over 4 years
    sorry for PDO. it does not support array values. i think they have to update it.
  • Usama Ahmed
    Usama Ahmed about 3 years
    I have tried to add another variable in execute statement using array but it isn't working.
  • Eduard Sukharev
    Eduard Sukharev over 2 years
    Depending on the source of $editions using real array keys might be insecure, although your solution looks more legant
  • Alexey Kosov
    Alexey Kosov almost 2 years
    How about implode(', ', array_fill(0, count($variables), '?'))
  • GordonM
    GordonM almost 2 years
    @AlexeyKosov That's also an option, yes. The key point is ensuring you've generated placeholders that match up to the number of values in the input.