我很好奇是否有可能使用PDO将值数组绑定到占位符。这里的用例试图传递一个值数组，以便与IN()条件一起使用。
我希望能够这样做:
<?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();
?>
并让PDO绑定并引用数组中的所有值。
目前我正在做:
<?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();
?>
这当然是工作，但只是想知道是否有一个内置的解决方案，我错过了?
下面是我的解决方案，基于alan_mm的答案。我还扩展了PDO类:
class Db extends PDO
{
/**
* SELECT ... WHERE fieldName IN (:paramName) workaround
*
* @param array $array
* @param string $prefix
*
* @return string
*/
public function CreateArrayBindParamNames(array $array, $prefix = 'id_')
{
$newparams = [];
foreach ($array as $n => $val)
{
$newparams[] = ":".$prefix.$n;
}
return implode(", ", $newparams);
}
/**
* Bind every array element to the proper named parameter
*
* @param PDOStatement $stmt
* @param array $array
* @param string $prefix
*/
public function BindArrayParam(PDOStatement &$stmt, array $array, $prefix = 'id_')
{
foreach($array as $n => $val)
{
$val = intval($val);
$stmt -> bindParam(":".$prefix.$n, $val, PDO::PARAM_INT);
}
}
}
下面是上面代码的示例用法:
$idList = [1, 2, 3, 4];
$stmt = $this -> db -> prepare("
SELECT
`Name`
FROM
`User`
WHERE
(`ID` IN (".$this -> db -> CreateArrayBindParamNames($idList)."))");
$this -> db -> BindArrayParam($stmt, $idList);
$stmt -> execute();
foreach($stmt as $row)
{
echo $row['Name'];
}
以下是我的解决方案:
$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));
注意array_values的使用。这可以修复键排序问题。
我正在合并id数组，然后删除重复的项。我是这样写的:
$ids = array(0 => 23, 1 => 47, 3 => 17);
那就是失败。
你可以这样转换:
$stmt = $db->prepare('SELECT * FROM table WHERE id IN('.$in.')');
在此:
$stmt = $db->prepare('SELECT * FROM table WHERE id IN(:id1, :id2, :id3, :id7, :id8, :id9)');
然后用这个数组执行它:
$stmt->execute(array(
:id1 =>1, :id2 =>2, :id3 =>3, :id7 =>7, :id8 =>8, :id9 => 9
)
);
因此:
$in = array();
$consultaParam = array();
foreach($ids as $k => $v){
$in[] = ':id'.$v;
$consultaParam[':id'.$v] = $v;
}
最后的代码:
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
$in = array();
$consultaParam = array();
foreach($ids as $k => $v){
$in[] = ':id'.$v;
$consultaParam[':id'.$v] = $v;
}
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN('.$in.')'
);
$stmt->execute($consultaParam);
您首先在查询中设置“?”的个数，然后由一个“for”发送参数
像这样:
require 'dbConnect.php';
$db=new dbConnect();
$array=[];
array_push($array,'value1');
array_push($array,'value2');
$query="SELECT * FROM sites WHERE kind IN (";
foreach ($array as $field){
$query.="?,";
}
$query=substr($query,0,strlen($query)-1);
$query.=")";
$tbl=$db->connection->prepare($query);
for($i=1;$i<=count($array);$i++)
$tbl->bindParam($i,$array[$i-1],PDO::PARAM_STR);
$tbl->execute();
$row=$tbl->fetchAll(PDO::FETCH_OBJ);
var_dump($row);
