我很好奇是否有可能使用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();
?>
这当然是工作,但只是想知道是否有一个内置的解决方案,我错过了?
稍微编辑一下施纳勒的代码
<?php
$ids = array(1, 2, 3, 7, 8, 9);
$inQuery = implode(',', array_fill(0, count($ids)-1, '?'));
$db = new PDO(...);
$stmt = $db->prepare(
'SELECT *
FROM table
WHERE id IN(' . $inQuery . ')'
);
foreach ($ids as $k => $id)
$stmt->bindValue(($k+1), $id);
$stmt->execute();
?>
//implode(',', array_fill(0, count($ids)-1), '?'));
//'?' this should be inside the array_fill
//$stmt->bindValue(($k+1), $in);
// instead of $in, it should be $id
您必须构造查询字符串。
<?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();
?>
chris(评论)和一些人都有麻烦了,建议foreach-loop…
(...)
// bindvalue is 1-indexed, so $k+1
foreach ($ids as $k => $id)
$stmt->bindValue(($k+1), $id);
$stmt->execute();
... 可能是多余的,因此foreach循环和$stmt->执行可以被替换为…
<?php
(...)
$stmt->execute($ids);
下面是我的解决方案,基于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'];
}
你可以这样转换:
$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);