'PHP - Using PDO with IN clause array
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).
Solution 1:[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:[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:[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:[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:[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.
Solution 6:[6]
I've just come up against this problem and coded a small wrapper. It's not the prettiest or best code I'm sure, but it might help somebody so here it is:
function runQuery(PDO $PDO, string $sql, array $params = [])
{
if (!count($params)) {
return $PDO->query($sql);
}
foreach ($params as $key => $values) {
if (is_array($values)) {
// get placeholder from array, e.g. ids => [7,12,3] would be ':ids'
$oldPlaceholder = ':'.$key;
$newPlaceholders = '';
$newParams = [];
// loop through array to create new placeholders & new named parameters
for($i = 1; $i <= count($values); $i++) {
// this gives us :ids1, :ids2, :ids3 etc
$newKey = $oldPlaceholder.$i;
$newPlaceholders .= $newKey.', ';
// this builds an associative array of the new named parameters
$newParams[$newKey] = $values[$i - 1];
}
//trim off the trailing comma and space
$newPlaceholders = rtrim($newPlaceholders, ', ');
// remove the old parameter
unset($params[$key]);
// and replace with the new ones
$params = array_merge($params, $newParams);
// amend the query
$sql = str_replace($oldPlaceholder, $newPlaceholders, $sql);
}
}
$statement = $PDO->prepare($sql);
$statement->execute($params);
return $statement;
}
E.g, passing these in:
SELECT * FROM users WHERE userId IN (:ids)
array(1) {
["ids"]=>
array(3) {
[0]=>
int(1)
[1]=>
int(2)
[2]=>
int(3)
}
}
Becomes:
SELECT * FROM users WHERE userId IN (:ids1, :ids2, :ids3)
array(3) {
[":ids1"]=>
int(1)
[":ids2"]=>
int(2)
[":ids3"]=>
int(3)
}
It's not bulletproof, but as a sole dev for my needs it does the job fine, so far anyway.
Solution 7:[7]
Here is a solution for unnamed placeholders (?). If you pass $sql with question mark like "A=? AND B IN(?) " and $args where some of the elements are arrays like [1, [1,2,3]] it will return SQL string with appropriate number of placeholders - "A=? AND B IN(?,?,?)". It needs $args parameter only to find which element is array and how many placeholders it needs. You can find the small PDO extension class with this method that will run your query: https://github.com/vicF/pdo/blob/master/src/PDO.php
public function replaceArrayPlaceholders($sql, $args)
{
$num = 0;
preg_match_all('/\?/', $sql, $matches, PREG_OFFSET_CAPTURE); // Captures positions of placeholders
//echo $matches[0][1][1];
$replacements = [];
foreach($args as $arg) {
if(is_array($arg)) {
$replacements[$matches[0][$num][1]] = implode(',',array_fill(0, count($arg), '?')); // Create placeholders string
}
$num++;
}
krsort($replacements);
foreach($replacements as $position => $placeholders) {
$sql = substr($sql, 0, $position).$placeholders.substr($sql, $position+1); // Replace single placeholder with multiple
}
return $sql;
}
Solution 8:[8]
As I understand it it is because PDO will treat the $in_values contents as a single item and will quite it accordingly. PDO will see 1,2,3 as a single string so the query will look something like
SELECT * FROM table WHERE my_value IN ("1,2,3")
You may think that changing the implode to have quotes and commas will fix it, but it will not. PDO will see the quotes and change how it quotes the string.
As to why your query matches the first value, I have no explanation.
Solution 9:[9]
Here is my full code, sorry for my stupid coding, bad structure and comment lines. Maybe someone recode my stupid code :)
sending to class:
$veri_sinifi = new DB_Connect;
$veri_sorgu_degerleri=array(
"main_user_id" => (int)$_SESSION['MM_UserId'],
"cari_grup_id" => [71,72,73],
"cari_grup_adi" => ['fatih','ahmet','ali']
);
$siteler =$veri_sinifi->query("Select cari_grup_adi,cari_grup_id FROM m_cari_gruplar WHERE main_user_id= :main_user_id and cari_grup_id in (:cari_grup_id) and cari_grup_adi in (:cari_grup_adi)",$veri_sorgu_degerleri) ;
class get this sql :
Select cari_grup_adi,cari_grup_id FROM m_cari_gruplar WHERE main_user_id= :main_user_id and cari_grup_id in (:cari_grup_id) and cari_grup_adi in (:cari_grup_adi)
class convert this sql to this.
Select cari_grup_adi,cari_grup_id FROM m_cari_gruplar WHERE main_user_id= :main_user_id and cari_grup_id in (:cari_grup_id0,:cari_grup_id1,:cari_grup_id2) and cari_grup_adi in (:cari_grup_adi0,:cari_grup_adi1,:cari_grup_adi2)
class binding params:
Select cari_grup_adi,cari_grup_id FROM m_cari_gruplar WHERE main_user_id= 1 and cari_grup_id in (71,72,73) and cari_grup_adi in ('fatih','ahmet','ali')
code:
class DB_Connect{
var $dbh;
function __construct(){
$host = "";
$db = "";
$user = "";
$password = "";
$this -> dbh = $this -> db_connect($host, $db, $user, $password);
}
public function getDBConnection(){
return $this -> dbh;
}
protected function db_connect($host, $db, $user, $password){
//var_dump($host, $db, $user, $password);exit();
try {
$dbh = new PDO("mysql:host=$host;dbname=$db", $user, $password);
}
catch(PDOException $err) {
echo "Error: ".$err->getMessage()."<br/>";
die();
}
return $dbh;
}
public function query($statement,$bind_params){
$keyword = substr(strtoupper($statement), 0, strpos($statement, " ")); // sql in en ba??ndaki kelimeye bak?yor SELECT UPDATE vs gibi ordaki ilk bo?lu?a kadar olan kelimeyi al?yor.
//echo $keyword;
$dbh = $this->getDBConnection();
if($dbh){
try{
$sql = $statement;
/*GELEN PARAMETRELERE BAKIP ?Ç?NDE ARRAY VAR ?SE SQL STATEMENT KISMINI ONA GÖRE DE???T?R?YORUZ.
Alttaki döngünün yapt??? i?lem ?u. E?er alttaki gibi bir sorgu de?erleri gönderilirse
$veri_sorgu_degerleri=array(
"main_user_id" => (int)$_SESSION['MM_UserId'],
"cari_grup_id" => [71,72,73],
"cari_grup_adi" => ['fatih','ahmet','ali']
);
burada main_user_id tek bir de?er di?erleri sise array olarak gönderiliyor. Where IN sorgusu birden fazla de?er alabilece?i için bunlar? PDO kabul ederken string olarak kabul ediyor yani yukardaki 71,72,73 de?erini t?rnak içine tek de?ermi? gib '71,72,73' ?eklinde al?yor yap?lmas? gereken sorgunun de?i?tirilmesi. bu döngü ile
Select cari_grup_adi,cari_grup_id FROM m_cari_gruplar WHERE main_user_id= :main_user_id and cari_grup_id in (:cari_grup_id) and cari_grup_adi in (:cari_grup_adi)
?eklindeki sorgu in k?s?mlar? de?i?tirilerek
Select cari_grup_adi,cari_grup_id FROM m_cari_gruplar WHERE main_user_id= :main_user_id and cari_grup_id in (:cari_grup_id0,:cari_grup_id1,:cari_grup_id2) and cari_grup_adi in (:cari_grup_adi0,:cari_grup_adi1,:cari_grup_adi2)
halini al?yor bir sonraki foreach de ise yine benzer yap? ile arary olarak gelen de?erler in için tek tek bind ediliyor, normal gelen de?erler ise normal bind yap?l?yor.
*/
foreach($bind_params as $paramkey => $param_value) {
//echo "Key=" . $paramkey ."-".($param_value)."-, Value=" . $param_value;
//echo "<br>";
//echo "is_numeric($param_value)>".is_numeric($param_value)."<br>";
//echo "is_string($param_value)>".is_string($param_value)."<br>";
//echo "is_array($param_value)>".is_array($param_value)."<br>";
$in_key="";
$in_parameters="";
if (is_array($param_value)) // Gelan parametre array ise yeniden yap?land?r.
{
foreach ($param_value as $i => $item)
{
$in_key = ":$paramkey".$i;
//echo "<br>$in_key = ".$paramkey.".$i";
$in_parameters .= "$in_key,";
//echo "<br>$in_parameters = ".$in_key;
}
$in_parameters = rtrim($in_parameters,","); // :id0,:id1,:id2
//echo "<br>in_parameters>$in_parameters";
$sql = str_replace(":".$paramkey, $in_parameters,$sql);
//echo "<br>olu?an sql>".$sql."<br>";
}
}
$sql = $dbh->prepare($sql);
foreach($bind_params as $paramkey => $param_value) {
//echo "Key=" . $paramkey ."-".($param_value)."-, Value=" . $param_value;
//echo "<br>";
//echo "is_numeric($param_value)>".is_numeric($param_value)."<br>";
//echo "is_string($param_value)>".is_string($param_value)."<br>";
//echo "is_array($param_value)>".is_array($param_value)."<br>";
if (is_numeric($param_value)==1) // gelen veri numerik ise
{
$param_value = (int)$param_value;
$pdo_param_type = PDO::PARAM_INT;
}
elseif (is_string($param_value)==1)// gelen veri string ise
{$pdo_param_type = PDO::PARAM_STR; }
if (is_array($param_value)) // gelen veri array tipinde ise
{
foreach ($param_value as $i => $param_array_value) // bu döngünün aç?klamas? yukardaki döngü için yaz?lan aç?klama içinde mevcut
{
$in_key = ":$paramkey".$i;
if (is_numeric($param_array_value)==1) // gelen veri numerik ise
{
$param_array_value = (int)$param_array_value;
$pdo_param_type = PDO::PARAM_INT;
}
elseif (is_string($param_array_value)==1)// gelen veri string ise
{$pdo_param_type = PDO::PARAM_STR; }
$sql->bindValue($in_key, $param_array_value, $pdo_param_type );
//echo "<br>oldu1";
//echo "<br> -$in_key-";
}
//$sql = str_replace(":".$paramkey, $in_parameters, $sql);
//echo "olu?an sql>".$sql."<br>";
}
else // array de?ilse a?a??daki ?ekilde bind yap.
{
//echo "<br>oldu2";
$sql->bindValue(":$paramkey", $param_value, $pdo_param_type ); // bindparam foreach içinde kullan?lmaz çünkü execute esnas?nda bind yapt??? için yani anl?k olarak de?erleri atamadd??? için for döngüsünde en sonda value de?i?keni neyse tüm parametrelere onu at?yor, bu sebeple bindvalue kullan?yoruz.PDO::PARAM_INT
}
} // foreach
$exe = $sql->execute();
$sql->debugDumpParams();
//echo $exe;
}
catch(PDOException $err){
return $err->getMessage();
}
//BU KISMA A?T AÇIKLAMA A?A?IDAIR.
switch($keyword){ // sorgu çal??t?ktan sonra sorgu sonucuna göre gerekli i?lemler yap?l?yor.
case "SELECT": // Sorgu select sorgusu ise
$result = array(); //sonuçlar? diziye aktaracak.
while($row = $sql->fetch(PDO::FETCH_ASSOC)){ // sonuç sat?rlar?n? tek tek okuyup
//echo $row;
$result[] = $row; // her bir sat?r? dizinin bir eleman?na aktar?yor.bu de?er diziden nas?l okunur aç?klamas? a?a??da
}
return $result; // sorgudan dönen diziyi do?rudan ana programa aktar?yor orada dizi olarak okunabilir.
break;
default:
return $exe;
break;
}
}
else{
return false;
}
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | |
| Solution 3 | Dienow |
| Solution 4 | celsowm |
| Solution 5 | Ted |
| Solution 6 | |
| Solution 7 | Victor |
| Solution 8 | PCaligari |
| Solution 9 | g770like |