Errors appearing in mysqli code and call_user_func

问题:

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

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

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

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

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

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

回答1:

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

回答2:

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

This should be self-explanatory: the arguments of bind_param are passed by reference and therefore must be variables. What you might have overlooked is that array_merge returns a new array that does not contain references to the original variables but just values.

The following errors are just subsequent to this because the parameters were not bound.

A possible solution is to store references in your $parameters array, those will even be preserved by array_merge:

$parameters[] =& $_POST["student"];

$parameters[] =& $_POST["question"];

Now the array items of $parameters are references to POST variables, and the array items of the array_merge result also.

Edit: looks like this is not possible anymore, see @Explosion Pills

回答3:

This is still not good enough reason to ditch mysqli for PDO, especially when I had very good reasons NOT to use PDO instead, at that time. On the other hand, yes, they did not need to force mysqli_stmt::bind_param and bind_result methods to require the variables to be passed byRef, they could have had them return an array. But, the real issue here is that call-time pass-by-reference is no longer possible, but mysqli still requires pass-by-reference, so anyone who is using dynamic parameters (anyone who has intuitive SQL that is) is getting caught up in this issue, myself included. After spending hours writing a custom mysqli wrapper class that converts array sets into dynamically prepared SQL statements, I am not about to try and rewrite it all to work with PDO, especially since PDO has a requirement to name your parameters as part of the binding, I would have to write multiple functions in order to build the list of what was sent...for each type of query. This is counter-intuitive, and I will not waste countless hours trying to emulate what I have accomplished with mysqli.

The only hitch at this point is that we cannot pass the variables in call_user_func_array() byRef, and mysqli_stmt::bind_* requires them to be passed in byRef... so the only options we have are 1) write a new call_user_func_array() function that is designed to work explicitly for the bind_* methods, where the passed parameters are essentially copied within into a new array or set of variables by args, and then pass those byRef on callback; or 2) rewrite the mysqli class to do it the right way and just look at what is passed, perform the SQL, then return the result as intended. There is no reason that what we pass to a method to perform SQL must be clobbered with results, it should be treated just like a SQL console is treated.

Because I am way too exhausted from coding until my eyes nearly bleed, I am going to take the non-suggested option of keeping my PHP version stuck at 5.3.10-1 until I actually get the problem fixed the right way. For those that would argue "for all that effort, why not just spend it on writing your PDO wrapper instead?"... and my response is simple: I use PHP and MySQL exclusively on many projects where the server is a tiny little computer with very limited room and resources, not to mention CPU capability. PDO loads EVERYTHING it can do, not just what you are going to use, so there are some reservations made in your resources for things you won't even use, which my projects cannot afford to lose.

Thankfully, keeping my PHP version held back is not going to be detrimental at this time. This is what many enterprise level server farms actually do in order to avoid having to bring everything down just to be 'on the current version' of something, which is also insanely expensive to upgrade everything every couple of years.