问题描述
到目前为止,如果编写的语句只有一个命名的占位符,但是如果查询有多个条件,则不会从数据库返回任何结果.
The code I've written so far works fine if there is only one named place holder for a prepared statement but if there are multiple conditions for a query, it doesn't return any results from the database.
例如:
$query = array();
$query['columns'] = array('*');
$query['tables'] = array('esl_comments');
$query['where'] = array(
'esl_comments.commentVisible' => array('=', 'Y')
);
工作正常.但是,如果我尝试:
Works fine. But if I try:
$query = array();
$query['columns'] = array('*');
$query['tables'] = array('esl_comments');
$query['where'] = array(
'esl_comments.commentVisible' => array('=', 'Y'),
'esl_comments.commentID' => array('=', '1'),
);
(请注意附加的commentID参数),尽管mySQL数据库中存在满足条件的数据,它仍然无法返回任何内容.
(Note the additional commentID parameter) it fails to return anything despite there being data in the mySQL database that satisfies the conditions.
我编写的PDO代码是:
The PDO code i've written is:
$sql ='SELECT ';
foreach($query['columns'] as $column){ //What columnns do we want to fetch?
$sql.=$column . ", ";
}
$sql = rtrim($sql, " ,");
$sql .=' FROM '; //Which tables will we be accessing?
foreach($query['tables'] as $tables){
$sql.=$tables . ", ";
}
$sql = rtrim($sql, " ,"); //Get rid of the last comma
$sql .=' WHERE ';
if(array_key_exists('where', $query)) //check if a where clause was provided
{
$fieldnames = array_keys($query['where']);
$count = 0;
$size = sizeof($fieldnames);
$bindings = array();
foreach($query['where'] as $where){
$cleanPlaceholder = str_replace("_", "", $fieldnames[$count]);
$cleanPlaceholder = str_replace(".", "", $cleanPlaceholder);
$sql.=$fieldnames[$count].$where[0].":".$cleanPlaceholder." AND ";
$bindings[$cleanPlaceholder]=$where[1];
$count++;
}
$sql = substr($sql, 0, -5); //Remove the last AND
}
else{ //no where clause so set it to an always true check
$sql.='1=1';
$bindings=array('1'=>'1'); //Provide default bindings for the statement
}
$sql .= ';'; //Add the semi-colon to note the end of the query
echo $sql . "<br/><br/>";
// exit();
$stmt = $this->_connection->prepare($sql);
foreach($bindings as $placeholder=>$bound){
echo $placeholder . " - " . $bound."<br/>";
$stmt->bindParam($placeholder, $bound);
}
$result = $stmt->execute();
echo $stmt->rowCount() . " records<br/>";
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
我正在动态地构建查询,因此我通过删除句点和下划线来清理占位符-因此使用了'cleanPlaceholder'变量.
I'm building queries dynamically and therefore I am cleaning the placeholders, by stripping them of periods and underscores - hence the use of the 'cleanPlaceholder' variable.
正在生成的查询如下:
SELECT * FROM esl_comments WHERE esl_comments.commentVisible=:eslcommentscommentVisible AND esl_comments.commentID=:eslcommentscommentID;
绑定的参数如下所示:
eslcommentscommentVisible - Y
eslcommentscommentID - 1
推荐答案
bindParam需要引用
问题是由您在foreach循环中绑定参数的方式引起的.
bindParam Requires a reference
The problem is caused by the way you bind parameters in the foreach loop.
foreach($bindings as $placeholder=>$bound){
echo $placeholder . " - " . $bound."<br/>";
$stmt->bindParam($placeholder, $bound);
}
bindParam
需要参考.它将变量而不是值绑定到该语句.由于在每次迭代开始时都会重置foreach循环中的变量,因此仅保留对$bound
的最后一个引用,并且最终将所有占位符绑定到该引用.
bindParam
requires a reference. It binds the variable, not the value, to the statement. Since the variable in a foreach loop is reset at the start of each iteration, only the last reference to $bound
is left intact, and you end up binding all your placeholders to it.
这就是为什么当$query['where']
仅包含一个条目但您的代码包含多个条目时会失败的原因.
That's why your code works when $query['where']
contains only one entry, but fails when it contains more than one.
您可以通过两种方式解决问题:
You can solve the problem in 2 ways:
foreach($bindings as $placeholder => &$bound) { //pass $bound as a reference (&)
$stmt->bindParam($placeholder, $bound); // bind the variable to the statement
}
按值传递
使用bindValue
代替bindParam
:
foreach($bindings as $placeholder => $bound) {
$stmt->bindValue($placeholder, $bound); // bind the value to the statement
}
这篇关于PDO多个命名占位符不检索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!