PHP - 在 IN 子句数组中使用 PDO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14767530/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP - Using PDO with IN clause array
提问by iRector
I'm using PDO to execute a statement with an INclause that uses an array for it's values:
我正在使用 PDO 执行一个语句，该语句带有一个IN使用数组作为其值的子句：
$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 who's my_valueequals the first item in the $in_array(1), but not the remaining items in the array (2, and 3).
此代码将返回my_value等于$in_array(1) 中第一项的项，但不返回数组中的其余项（2 和 3）。
回答by Your Common Sense
PDO is not good with such things. You need to create a string with question marks dynamically and insert into query.
PDO 不擅长这样的事情。您需要动态创建一个带问号的字符串并插入到查询中。
$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):
如果查询中有其他占位符，您可以使用以下方法（代码取自我的PDO 教程）：
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:
您可以使用array_merge()function 将所有变量连接到一个数组中，以数组的形式添加其他变量，按照它们在查询中出现的顺序：
$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:
如果您使用命名占位符，则代码会稍微复杂一些，因为您必须创建命名占位符的序列，例如:id0,:id1,:id2. 所以代码将是：
// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];
$ids = [1,2,3];
$in = "";
foreach ($ids as $i => $item)
{
$key = ":id".$i;
$in .= "$key,";
$in_params[$key] = $item; // collecting values into key-value array
}
$in = rtrim($in,","); // :id0,:id1,:id2
$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.
幸运的是，对于命名占位符，我们不必遵循严格的顺序，因此我们可以以任何顺序合并我们的数组。
回答by GordonM
Variable substitution in PDO prepared statements doesn't support arrays. It's one for one.
PDO 准备语句中的变量替换不支持数组。这是一对一的。
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)) {
// ...
}
回答by Dienow
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
由于 PDO 似乎没有提供好的解决方案，您不妨考虑使用 DBAL，它主要遵循 PDO 的 API，但也添加了一些有用的功能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.
可能还有一些其他包不会增加复杂性，也不会掩盖与数据库的交互（就像大多数 ORM 一样），但同时使小型典型任务更容易一些。
回答by Victor
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
这是未命名占位符 (?) 的解决方案。如果你传递带有问号的 $sql 像"A=? AND B IN(?) "和 $args 其中一些元素是像 [1, [1,2,3]] 这样的数组，它将返回具有适当数字的 SQL 字符串占位符 - "A=? AND B IN(?,?,?)"。它只需要 $args 参数来查找哪个元素是数组以及它需要多少个占位符。您可以使用此方法找到运行查询的小型 PDO 扩展类：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;
}
回答by celsowm
An alternative version of PHP Delusions (@your-common-sense) using closures:
使用闭包的 PHP Delusions (@your-common-sense) 的替代版本：
$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();
回答by PCaligari
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
据我了解，这是因为 PDO 会将 $in_values 内容视为单个项目并相应地进行处理。PDO 会将 1,2,3 视为单个字符串，因此查询将类似于
SELECT * FROM table WHERE my_value IN ("1,2,3")
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.
您可能认为将内爆更改为包含引号和逗号会修复它，但事实并非如此。PDO 将看到引号并更改它引用字符串的方式。
As to why your query matches the first value, I have no explanation.
至于为什么你的查询匹配第一个值，我没有解释。
回答by Codemonkey
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.
它不是防弹的，但作为满足我需求的唯一开发者，它可以很好地完成工作，无论如何。
回答by g770like
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 :
类得到这个 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.
类将这个 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_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;
}
}
}
}
回答by PM7Temp
I know this is old, but I just wanted to add to his/her(your common sense)'s answer and how I got it done in my library:
我知道这很旧，但我只是想补充他/她（你的常识）的答案以及我是如何在我的图书馆中完成的：
I would not recommend using ? placeholders as they make it hard to add complex queries, a simple clean method I have created which creates unique keys: This might be helpful:
我不建议使用 ? 占位符，因为它们很难添加复杂的查询，这是我创建的一个简单的干净方法，它创建了唯一的键：这可能会有所帮助：
public static function bind_clause(string $key, array $values):array
{
$param_values = [];
$stmt_values = '(';
foreach ($values as $k => $v) {
$newkey = ":$key$k";
$param_values[$newkey] = $v;
$stmt_values .= "$newkey,";
}
$stmt_values = rtrim($stmt_values, ',');
$stmt_values .= ')';
$params = self::create_parameter($key, $param_values);
return [
'stmt' => $stmt_values,
'params' => $params
];
}
public function bind_query_params(array $params):bool
{
foreach ($params as $key => $param) {
$bindby = $param->get_bind_by();
$bind = "bind$bindby";
$key = $param->get_bind_key();
$value = $param->get_value();
if (is_array($value)) {
foreach ($value as $k => $v) {
if (!$this->prepared_stmt->$bind($k, $v)) {
return false;
}
}
}elseif (!$this->prepared_stmt->$bind($key, $value)) {
return false;
}
}
return true;
}
