問題描述

我在嘗試使用 mysqli 創(chuàng)建動態(tài) where 子句時遇到不少錯誤:

I am getting quite a few errors when trying to create a dynamic where clause using mysqli:

警告:mysqli_stmt::bind_param() 的參數(shù) 2 預(yù)期為參考，在第 319 行中給出的值

Warning: Parameter 2 to mysqli_stmt::bind_param() expected to be a reference, value given in ... on line 319

警告:mysqli_stmt::execute(): (HY000/2031): 沒有提供數(shù)據(jù)第 328 行的準(zhǔn)備語句中的參數(shù)

Warning: mysqli_stmt::execute(): (HY000/2031): No data supplied for parameters in prepared statement in ... on line 328

警告:mysqli_stmt::bind_result(): (HY000/2031): 未提供數(shù)據(jù)在第 331 行的準(zhǔn)備語句中的參數(shù)

Warning: mysqli_stmt::bind_result(): (HY000/2031): No data supplied for parameters in prepared statement in ... on line 331

警告:mysqli_stmt::store_result(): (HY000/2014): 命令超出同步;你現(xiàn)在不能在……第 332 行運行這個命令

Warning: mysqli_stmt::store_result(): (HY000/2014): Commands out of sync; you can't run this command now in ... on line 332

我猜想解決問題需要做一些改動，但是如果兩個下拉菜單中的一個不等于 All 或者兩者都不等于 全部然后出現(xiàn)錯誤.

Im guessing there is a little change that is needed to solve the problems but what happens is that if one of the two drop down menu's do not equal All or if both don't equal All then it comes up with the errors.

下面的代碼顯示了下拉菜單和后面的查詢(帶有動態(tài) where 子句)，具體取決于所選的 n 個選項:

Below is the code display both the drop down menus and the query (with dynamic where clause) that follows depending n options selected:

HTML:

學(xué)生下拉菜單:

<select name="student" id="studentsDrop">
<option value="All">All</option>
<option value="11">John May</option>
<option value="23">Chris Park</option>
</select>

問題編號下拉菜單

<select name="question" id="questionsDrop">
<option value="All">All</option>
<option value="123">1</option>
<option value="124">2</option>
<option value="125">3</option>
</select>

PHP/MYSQLI:

      function StudentAnswers()
        {


    /*BELOW IS THE QUERY WHERE I AM TRYING TO RETRIEVE DATA DEPENDING ON THE ASSESSMENT CHOSEN AND
    THEN DEPENDING ON OPTIONS CHOSEN IN STUDENT AND QUESTION NUMBER DROP DOWN MENU */

        $selectedstudentanswerqry = "
        SELECT
        StudentAlias, StudentForename, StudentSurname, q.SessionId, QuestionNo, QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( DISTINCT Answer
        ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, QuestionMarks, 
        GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime, MouseClick, StudentMark
        FROM Student s
        INNER JOIN Student_Answer sa ON (s.StudentId = sa.StudentId)
        INNER JOIN Student_Response sr ON (sa.StudentId = sr.StudentId)
        INNER JOIN Question q ON (sa.QuestionId = q.QuestionId)
        INNER JOIN Answer an ON q.QuestionId = an.QuestionId
        LEFT JOIN Reply r ON q.ReplyId = r.ReplyId
        LEFT JOIN Option_Table o ON q.OptionId = o.OptionId
        ";

        // Initially empty
        $where = array('q.SessionId = ?');
        $parameters = array($_POST["session"]);
        $parameterTypes = 'i';

        // Check whether a specific student was selected
        if($_POST["student"] !== 'All') {
            $where[] = 'sa.StudentId = ?';
            $parameters[] =& $_POST["student"];
            $parameterTypes .= 'i';
        }

        // Check whether a specific question was selected
        // NB: This is not an else if!
        if($_POST["question"] !== 'All') {
            $where[] = 'q.QuestionId = ?';
            $parameters[] =& $_POST["question"];
            $parameterTypes .= 'i';
        }

        // If we added to $where in any of the conditionals, we need a WHERE clause in
        // our query
        if(!empty($where)) {
            $selectedstudentanswerqry .= ' WHERE ' . implode(' AND ', $where);
            global $mysqli;
            $selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry);
            // You only need to call bind_param once
                call_user_func_array(array($selectedstudentanswerstmt, 'bind_param'),
                array_merge(array($parameterTypes), $parameters)); //LINE 319 ERROR 1
        }

    //Add group by and order by clause to query
        $selectedstudentanswerqry .= "
          GROUP BY sa.StudentId, q.QuestionId
          ORDER BY StudentAlias, q.SessionId, QuestionNo
        ";

        // get result and assign variables (prefix with db)
        $selectedstudentanswerstmt->execute(); //LINE 328 ERROR 2

    //bind database fields 
$selectedstudentanswerstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname,$detailsSessionId,$detailsQuestionNo, 
        $detailsQuestonContent,$detailsOptionType,$detailsNoofAnswers,$detailsAnswer,$detailsReplyType,$detailsQuestionMarks,$detailsStudentAnswer,$detailsResponseTime,
        $detailsMouseClick,$detailsStudentMark); //LINE 331 ERROR 3

    //store results retrieved
        $selectedstudentanswerstmt->store_result(); //LINE 332 ERROR 4

    //count number of rows retrieved
        $selectedstudentanswernum = $selectedstudentanswerstmt->num_rows();     

    //output query
        echo "$selectedstudentanswerqry";

        }

        ?>

這是一個演示: 演示

在演示中，從下拉菜單中選擇一個評估并提交.您將看到兩個下拉菜單.將它們都設(shè)置為 All 并提交，它將毫無問題地輸出查詢.在其中一個下拉菜單中選擇否，將 All 更改為特定的學(xué)生或問題，然后提交.現(xiàn)在你會看到錯誤

In demo select an assessment from drop down menu and submit. You will see the two drop down menus. Keep them both set as All and submit, it will output query with no problems. No in one of the drop down menus, change All to a specific student or question, then submit. Now you will see the errors

VAR 轉(zhuǎn)儲:

var_dump(array_merge(array($parameterTypes), $parameters))); 當(dāng)我選擇值為 31 的 session(評估)時的結(jié)果，學(xué)生編號值 40，問題編號值 81，和 WHERE CLAUSE WHERE q.SessionId = ?和 sa.StudentId = ?AND q.QuestionId = ?:

The result of the var_dump(array_merge(array($parameterTypes), $parameters))); when I chose session (assessment) with value 31, student number value 40, and question number value 81, AND WHERE CLAUSE WHERE q.SessionId = ? AND sa.StudentId = ? AND q.QuestionId = ?:

我得到這個輸出:array(4) { [0]=>string(3) "iii" [1]=>字符串(2)31"[2]=>字符串(2)40"[3]=>字符串(2) "81" }

推薦答案

這是一個棘手的情況，由 PHP 5.4 中的 call_user_func_array 行為改變引起(我必須假設(shè)):文檔

This is a sticky situation that is caused by changing of call_user_func_array behavior in PHP 5.4 (I have to assume): Documentation

盡管如此丑陋，它仍然可以這樣調(diào)用 bind_param:

As ugly as this is, it will work to call bind_param this way:

$selectedstudentanswerqry .= ' WHERE ' . implode(' AND ', $where);
global $mysqli;
$stmt =$mysqli->prepare($selectedstudentanswerqry);

if (count($where) === 1) {
    $stmt->bind_param($parameterTypes, $parameters[0]);
}
else if (count($where) === 2) {
    $stmt->bind_param($parameterTypes, $parameters[0], $parameters[1]);
}
else if (count($where) === 3) {
    $stmt->bind_param($parameterTypes, $parameters[0], $parameters[1],
       $parameters[2]);
}

我和你一樣討厭這個.我建議從 mysqli 切換到 PDO，它以更好的方式處理可變參數(shù)(在我看來，它的語法總體上更出色):

I hate this as much as you probably do. I suggest switching from mysqli to PDO which handles variable parameters in a much nicer fashion (and has superior syntax in general, in my opinion):

$pdo = new PDO('mysql:host=localhost', 'username', 'password');
$stmt = $pdo->prepare($selectedstudentanswerqry);
$stmt->execute($parameters);
$selectedstudentanswernum = $stmt->rowCount();

這篇關(guān)于mysqli 代碼和 call_user_func_array() 中出現(xiàn)的錯誤的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！