mysqli 代码和 call_user_func_array() 中出现的错误

问题描述

我在尝试使用 mysqli 创建动态 where 子句时遇到不少错误:

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

警告:mysqli_stmt::bind_param() 的参数 2 预期为参考，在第 319 行中给出的值

警告:mysqli_stmt::execute(): (HY000/2031): 没有提供数据第 328 行的准备语句中的参数

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

警告:mysqli_stmt::bind_result(): (HY000/2031): 未提供数据在第 331 行的准备语句中的参数

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): 命令超出同步;你现在不能在……第 332 行运行这个命令

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

我猜想解决问题需要做一些改动，但是如果两个下拉菜单中的一个不等于 All 或者两者都不等于 全部然后出现错误.

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.

下面的代码显示了下拉菜单和后面的查询(带有动态 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:

学生下拉菜单:

<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";

        }

        ?>

这是一个演示: 演示

在演示中，从下拉菜单中选择一个评估并提交.您将看到两个下拉菜单.将它们都设置为 All 并提交，它将毫无问题地输出查询.在其中一个下拉菜单中选择否，将 All 更改为特定的学生或问题，然后提交.现在你会看到错误

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 转储:

var_dump(array_merge(array($parameterTypes), $parameters))); 当我选择值为 31 的 session(评估)时的结果，学生编号值 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 行为改变引起(我必须假设):文档

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

尽管如此丑陋，它仍然可以这样调用 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，它以更好的方式处理可变参数(在我看来，它的语法总体上更出色):

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();

这篇关于mysqli 代码和 call_user_func_array() 中出现的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！