php 我可以将数组绑定到 IN() 条件吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/920353/
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
Can I bind an array to an IN() condition?
提问by Andru
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.
我很想知道是否可以使用 PDO 将一组值绑定到占位符。这里的用例是尝试传递一组值以与IN()条件一起使用。
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.
并让 PDO 绑定并引用数组中的所有值。
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?
这当然可以完成工作，但只是想知道是否有我缺少的内置解决方案？
回答by stefs
i think soulmerge is right. you'll have to construct the query-string.
我认为灵魂合并是对的。你必须构造查询字符串。
<?php
$ids = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids), '?'));
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN(' . $inQuery . ')'
);
// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
$stmt->bindValue(($k+1), $id);
$stmt->execute();
?>
fix:dan, you were right. fixed the code (didn't test it though)
修复：丹，你是对的。修复了代码（虽然没有测试）
edit:both chris (comments) and somebodyisintrouble suggested that the foreach-loop ...
编辑：克里斯（评论）和someoneisintroble都建议foreach-loop ...
(...)
// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
$stmt->bindValue(($k+1), $id);
$stmt->execute();
... might be redundant, so the foreachloop and the $stmt->executecould be replaced by just ...
... 可能是多余的，所以foreach循环和 the$stmt->execute可以只替换为 ...
<?php
(...)
$stmt->execute($ids);
?>
(again, i didn't test it)
（同样，我没有测试它）
回答by DonVaughn
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);
回答by Tim Tonkonogov
Is it so important to use INstatement? Try to use FIND_IN_SETop.
使用IN语句有那么重要吗？尝试使用FIND_IN_SETop。
For example, there is a query in PDO like that
例如，在 PDO 中有一个这样的查询
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.
UPD：正如有些人在对此答案的评论中指出的那样，有些问题应该明确说明。
FIND_IN_SETdoesn'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.- 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
implodesince you use comma symbol as a separator. Thanks to @VaL for the note.
FIND_IN_SET不在表中使用索引，它仍然没有实现 - 请参阅MYSQL 错误跟踪器中的此记录。感谢@BillKarwin 的通知。- 您不能使用带有逗号的字符串作为搜索数组的值。
implode由于您使用逗号符号作为分隔符，因此无法以正确的方式解析此类字符串。感谢@VaL 的注释。
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.
好吧，如果您不太依赖索引并且不使用带逗号的字符串进行搜索，那么我的解决方案将比上面列出的解决方案更容易、更简单、更快。
回答by prograhammer
Since I do a lot of dynamic queries, this is a super simple helper function I made.
由于我做了很多动态查询，这是我做的一个超级简单的辅助函数。
public static function bindParamArray($prefix, $values, &$bindArray)
{
$str = "";
foreach($values as $index => $value){
$str .= ":".$prefix.$index.",";
$bindArray[$prefix.$index] = $value;
}
return rtrim($str,",");
}
Use it like this:
像这样使用它：
$bindString = helper::bindParamArray("id", $_GET['ids'], $bindArray);
$userConditions .= " AND users.id IN($bindString)";
Returns a string :id1,:id2,:id3and also updates your $bindArrayof bindings that you will need when it's time to run your query. Easy!
返回一个字符串:id1,:id2,:id3并更新您$bindArray在运行查询时需要的绑定。简单！
回答by ESCOBAR
very clean way for postgres is using the postgres-array ("{}"):
postgres 的非常干净的方法是使用 postgres 数组（“{}”）：
$ids = array(1,4,7,9,45);
$param = "{".implode(', ',$ids)."}";
$cmd = $db->prepare("SELECT * FROM table WHERE id = ANY (?)");
$result = $cmd->execute(array($param));
回答by Sergey Galkin
Solution from EvilRygy didn't worked for me. In Postgres you can do another workaround:
EvilRygy 的解决方案对我不起作用。在 Postgres 中，您可以执行另一种解决方法：
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id = ANY (string_to_array(:an_array, ','))'
);
$stmt->bindParam(':an_array', implode(',', $ids));
$stmt->execute();
回答by Progrock
Here is my solution:
这是我的解决方案：
$total_items = count($array_of_items);
$question_marks = array_fill(0, $total_items, '?');
$sql = 'SELECT * FROM foo WHERE bar IN (' . implode(',', $question_marks ). ')';
$stmt = $dbh->prepare($sql);
$stmt->execute(array_values($array_of_items));
Note the use of array_values. This can fix key ordering issues.
注意 array_values 的使用。这可以解决关键的排序问题。
I was merging arrays of ids and then removing duplicate items. I had something like:
我正在合并 id 数组，然后删除重复项。我有这样的事情：
$ids = array(0 => 23, 1 => 47, 3 => 17);
And that was failing.
那是失败的。
回答by Chris
I extended PDO to do something similar to what stefs suggests, and it was easier for me in the long run:
我扩展了 PDO 来做一些类似于 stefs 建议的事情，从长远来看，这对我来说更容易：
class Array_Capable_PDO extends PDO {
/**
* Both prepare a statement and bind array values to it
* @param string $statement mysql query with colon-prefixed tokens
* @param array $arrays associatve array with string tokens as keys and integer-indexed data arrays as values
* @param array $driver_options see php documention
* @return PDOStatement with given array values already bound
*/
public function prepare_with_arrays($statement, array $arrays, $driver_options = array()) {
$replace_strings = array();
$x = 0;
foreach($arrays as $token => $data) {
// just for testing...
//// tokens should be legit
//assert('is_string($token)');
//assert('$token !== ""');
//// a given token shouldn't appear more than once in the query
//assert('substr_count($statement, $token) === 1');
//// there should be an array of values for each token
//assert('is_array($data)');
//// empty data arrays aren't okay, they're a SQL syntax error
//assert('count($data) > 0');
// replace array tokens with a list of value tokens
$replace_string_pieces = array();
foreach($data as $y => $value) {
//// the data arrays have to be integer-indexed
//assert('is_int($y)');
$replace_string_pieces[] = ":{$x}_{$y}";
}
$replace_strings[] = '('.implode(', ', $replace_string_pieces).')';
$x++;
}
$statement = str_replace(array_keys($arrays), $replace_strings, $statement);
$prepared_statement = $this->prepare($statement, $driver_options);
// bind values to the value tokens
$x = 0;
foreach($arrays as $token => $data) {
foreach($data as $y => $value) {
$prepared_statement->bindValue(":{$x}_{$y}", $value);
}
$x++;
}
return $prepared_statement;
}
}
You can use it like this:
你可以这样使用它：
$db_link = new Array_Capable_PDO($dsn, $username, $password);
$query = '
SELECT *
FROM test
WHERE field1 IN :array1
OR field2 IN :array2
OR field3 = :value
';
$pdo_query = $db_link->prepare_with_arrays(
$query,
array(
':array1' => array(1,2,3),
':array2' => array(7,8,9)
)
);
$pdo_query->bindValue(':value', '10');
$pdo_query->execute();
回答by RousseauAlexandre
For me the sexier solution is to construct a dynamic associative array & use it
对我来说，更性感的解决方案是构建一个动态关联数组并使用它
// A dirty array sent by user
$dirtyArray = ['Cecile', 'Gilles', 'Andre', 'Claude'];
// we construct an associative array like this
// [ ':name_0' => 'Cecile', ... , ':name_3' => 'Claude' ]
$params = array_combine(
array_map(
// construct param name according to array index
function ($v) {return ":name_{$v}";},
// get values of users
array_keys($dirtyArray)
),
$dirtyArray
);
// construct the query like `.. WHERE name IN ( :name_1, .. , :name_3 )`
$query = "SELECT * FROM user WHERE name IN( " . implode(",", array_keys($params)) . " )";
// here we go
$stmt = $db->prepare($query);
$stmt->execute($params);
回答by Daniel Miloca - Brazil
When you have other parameter, you may do like this:
当您有其他参数时，您可以这样做：
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
$query = 'SELECT *
FROM table
WHERE X = :x
AND id IN(';
$comma = '';
for($i=0; $i<count($ids); $i++){
$query .= $comma.':p'.$i; // :p0, :p1, ...
$comma = ',';
}
$query .= ')';
$stmt = $db->prepare($query);
$stmt->bindValue(':x', 123); // some value
for($i=0; $i<count($ids); $i++){
$stmt->bindValue(':p'.$i, $ids[$i]);
}
$stmt->execute();
