PHP - Using PDO with IN clause array
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.
iRector
Updated on May 29, 2021Comments
-
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 over 10 yearsThanks, 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 over 10 yearsSure. 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 over 8 yearsYour 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 over 7 yearsCan you explain what the
-1
is for after thecount()
? -
Robert over 7 yearsWhy
count ($variables) - 1) . '?';
Why not justcount($variable)
-
GordonM over 7 years@RobertRocha Because you need one fewer commas than there are variables
-
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 almost 7 yearsWthat's that
a
in... IN (?a) ..
? -
Your Common Sense almost 7 years@stack it's a thing you might write yourself to simplify the operation
-
stack almost 7 yearsDo you mean something like
:a
? -
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 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 almost 7 yearsThanks, this helped me a lot, been looking for this for hours
-
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 about 6 years@YourCommonSense this is the best answer to such a common issue, thanks!
-
Bossman over 5 yearsWithout the -1
$in = rtrim(str_repeat('?,', count($array)), ',');
-
Saleh Mosleh over 4 yearssorry for PDO. it does not support array values. i think they have to update it.
-
Usama Ahmed about 3 yearsI have tried to add another variable in execute statement using array but it isn't working.
-
Eduard Sukharev over 2 yearsDepending on the source of
$editions
using real array keys might be insecure, although your solution looks more legant -
Alexey Kosov almost 2 yearsHow about
implode(', ', array_fill(0, count($variables), '?'))
-
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.