問題描述

到目前為止，如果準備好的語句只有一個命名占位符，我編寫的代碼可以正常工作，但如果查詢有多個條件，則不會從數據庫返回任何結果.

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.

正在生成的查詢如下所示:

The query being generated looks like this:

SELECT * FROM esl_comments WHERE esl_comments.commentVisible=:eslcommentscommentVisible AND esl_comments.commentID=:eslcommentscommentID;

被綁定的參數如下所示:

And the parameters being bound look like this:

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 多個命名占位符不檢索數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！