and

and

关注
发信
关注(28)粉丝(399)

出现在mysqli代码和call_user_func_array（）中的错误

本文介绍了出现在mysqli代码和call_user_func_array（）中的错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我猜测有一点变化是需要解决的发生的问题是，如果两个下拉菜单中的一个不等于全部或者两者不等于全部

下面的代码显示下拉菜单和查询（使用动态where子句），后面跟随n个选项选择：

HTML： 学生下拉菜单：

 < select name =studentid =studentsDrop> 
< option value =All>全部< / option> 
< option value =11> John May< / option> 
< option value =23> Chris Park< / option> 
< / select>

问题编号下拉菜单

 < select name =questionid =questionsDrop> 
< option value =All>全部< / option> 
< option value =123> 1< / option> 
< option value =124> 2< / option> 
< option value =125> 3< / option> 
< / select>

PHP / MYSQLI： 

 函数StudentAnswers（）
 {
 
 
 / *下面是我正在试图检索数据的依据的查询评估选择和
，然后根据学生选择的选项和问题编号删除菜单* / 
 
 $ selectedstudentanswerqry =
 SELECT 
 StudentAlias，StudentForename，StudentSurname， q.SessionId，QuestionNo，QuestionContent，o.OptionType，q.NoofAnswers，GROUP_CONCAT（DISTINCT回答
 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问题q ON（sa.QuestionId = q.QuestionId）
 INNER JOIN回答一个ON q.QuestionId = an.QuestionId 
 LEFT JOIN回复r ON q.ReplyId = r.ReplyId 
 LEFT JOIN Option_Table o ON q.OptionId = o.OptionId 
; 
 
 //最初为空
 $ where = array（'q.SessionId =？'）; 
 $ parameters = array（$ _ POST [session]）; 
 $ parameterTypes ='i'; 
 
 //检查是否选择了特定的学生
 if（$ _ POST [student]！=='All'）{
 $ where [] ='sa。 StudentId =？'; 
 $ parameters [] =& $ _ POST [ 学生]; 
 $ parameterTypes。='i'; 
} 
 
 //检查是否选择了特定的问题
 //注意：这不是别的！ 
 if（$ _ POST [question]！=='All'）{
 $ where [] ='q.QuestionId =？'; 
 $ parameters [] =& $ _ POST [ 问题]; 
 $ parameterTypes。='i'; 
} 
 
 //如果我们在任何条件中添加了$，那么我们需要
中的WHERE子句//我们的查询
 if（！empty（ $ where））{
 $ selectedstudentanswerqry。='WHERE'。 implode（'AND'，$ where）; 
 global $ mysqli; 
 $ selectedstudentanswerstmt = $ mysqli-> prepare（$ selectedstudentanswerqry）; 
 //你只需要调用bind_param一次
 call_user_func_array（array（$ selectedstudentanswerstmt，'bind_param'），
 array_merge（array（$ parameterTypes），$ parameters））; // LINE 319 ERROR 1 
} 
 
 //添加group by和order by子句来查询
 $ selectedstudentanswerqry。=
 GROUP BY sa.StudentId，q .QuestionId 
 ORDER BY StudentAlias，q.SessionId，QuestionNo 
; 
 
 //获得结果并赋值变量（前缀为db）
 $ selectedstudentanswerstmt-> execute（）; // LINE 328错误2 
 
 //绑定数据库字段
 $ selectedstudentanswerstmt-> bind_result（$ detailsS​​tudentAlias，$ detailsS​​tudentForename，$ detailsS​​tudentSurname，$ detailsS​​essionId，$ detailsQuestionNo，
 $ detailsNuestTypeContent，$ detailsOptionType，$ detailsNoofAnswers，$ detailsAnswer，$ detailsReplyType，$ detailsQuestionMarks，$ detailsS​​tudentAnswer，$ detailsResponseTime，
 $ detailsMouseClick，$ detailsS​​tudentMark）; // LINE 331错误3 
 
 //存储结果检索
 $ selectedstudentanswerstmt-> store_result（）; // LINE 332 ERROR 4 
 
 //计数检索的行数
 $ selectedstudentanswernum = $ selectedstudentanswerstmt-> num_rows（）; 
 
 //输出查询
 echo$ selectedstudentanswerqry; 
 
} 
 
？>

这是一个DEMO：



在演示中，从下拉菜单中选择评估菜单并提交。你会看到两个下拉菜单。保持它们都设置为 All 并提交，它将输出没有问题的查询。在其中一个下拉菜单中，将全部更改为特定的学生或问题，然后提交。现在，您将看到错误消息：

VAR DUMP：

var_dump（array_merge（array（$ parameterTypes），$ parameters）））; 当我选择值 31的会话（评估），学号码值 40 和问题号码值 81 ，以及WHERE CLAUSE WHERE q.SessionId =？ AND sa.StudentId =？ AND q.QuestionId =？：



我得到这个输出： array（4）{[0] = >字符串（3）iii[1] =>字符串（2）31[2] =>字符串（2）40[3] => string（2）81}

解决方案

这是一个棘手的情况， PHP 5.4中的 call_user_func_array 行为（我必须假设）：



尽管丑陋，但仍然可以调用 bind_param 这种方式： 

  $ 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]，
 $参数[2]）; 
}

我讨厌这个，就像你可能做的那样。我建议从> mysqli 切换到 PDO ，它以更好的方式处理变量参数（并且通常具有优越的语法，在我看来）：

predo = new PDO（'mysql：host = localhost'，'username'，'password' ）;
$ stmt = $ pdo-> prepare（$ selectedstudentanswerqry）;
$ stmt-> execute（$ parameters）;
$ selectedstudentanswernum = $ stmt-> rowCount（）;


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

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.

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

HTML:

Student Drop down menu:

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

Question Number Drop down menu

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

        }

        ?>

Here is a DEMO: DEMO

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 DUMP:

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 = ?:

I am getting this output: array(4) { [0]=> string(3) "iii" [1]=> string(2) "31" [2]=> string(2) "40" [3]=> string(2) "81" }

解决方案

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

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]);
}

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（）中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

10-29 02:40
Powered by LMLPHP ©2024 and 0.001943