Errors occurring in mysqli code and call_user_func_array ()

Question

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

A warning. Parameter 2 for mysqli_stmt :: bind_param () must be a link, the value specified in ... on line 319

Warning: mysqli_stmt :: execute (): (HY000 / 2031): data for parameters in the prepared message in ... on line 328

Warning: mysqli_stmt :: bind_result (): (HY000 / 2031): there is no data for the parameters in the prepared message in ... on line 331

Warning: mysqli_stmt :: store_result (): (HY000 / 2014): Commands from synchronization; you cannot run this command now ... on line 332

I assume that there is a small change needed to solve the problems, but it happens that if one of the two drop-down menus is not equal to All or if both are not equal to All , then it appears with errors.

Below is the code that displays both the drop-down menus and the query (with the dynamic where clause), which follows depending on the selected parameter:

HTML:

Drop-down menu for students:

 <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 Dropdown 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 the demo, select a rating from the dropdown menu and submit. You will see two drop-down menus. Save them as All and send, it will display a request without problems. Not in one of the drop-down menus, change All to a specific student or question, then submit. Now you will see errors

VAR DUMP:

Result var_dump(array_merge(array($parameterTypes), $parameters))); when I selected a session (grade) with a value of 31 , a student number value of 40 and a question number value of 81 , AND WHERE CLAUSE WHERE q.SessionId = ? AND sa.StudentId = ? AND q.QuestionId = ? WHERE q.SessionId = ? AND sa.StudentId = ? AND q.QuestionId = ? :

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

Answer 1

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

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

 $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 it as much as you probably do. I suggest switching from mysqli to PDO , which handles variable parameters much better (and, in my opinion, has excellent syntax):

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

Answer 2

Warning: Parameter 2 for mysqli_stmt :: bind_param () must be a link, the value specified in ... on line 319

This should be self-evident: the bind_param arguments are passed by reference and therefore must be variables. What you might have missed is that array_merge returns a new array that does not contain references to the source variables, but simply values.

The following errors simply follow this because the parameters were not related.

A possible solution is to store references in your $parameters array, which even array_merge will keep:

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

Now the elements of the $parameters array are references to POST variables, as well as the elements of the array of the result array_merge .

Edit: it looks like this is impossible, see @ blast tablets

Answer 3

This is still not a sufficient reason for abandoning 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 the use of the mysqli_stmt :: bind_param and bind_result methods to require the variables to be passed through Ref, they could return an array to them. But the real problem here is that call forwarding is no longer possible, but mysqli still requires passing by reference, so anyone who uses dynamic parameters (anyone who has an intuitive SQL code) is caught in this, I turned it on myself . Having spent hours creating a custom mysqli shell class that converts arrays into dynamically prepared SQL statements, I’m not going to rewrite all this to work with PDO, especially since PDO has a requirement to name your parameters as part of the binding. I would need to write some functions to create a list of what was sent ... for each type of request. This is contrary to intuition, and I will not spend much time trying to imitate what I did with mysqli.

The only problem at this point is that we cannot pass variables to call_user_func_array () byRef, and mysqli_stmt :: bind_ * requires them to be passed to byRef ... so the only parameters we have are: 1) write a new call_user_func_array (), which is designed to work explicitly for the bind_ * methods, where the parameters passed are essentially copied internally to a new array or set of variables by arguments, and then passed them via Ref when the callback is called; or 2) rewrite the mysqli class to do it correctly and just see what is transferred, execute SQL, and then return the result as intended. There is no reason that what we pass to the SQL execution method should be knocked down with the results, it should be handled in the same way that the SQL console is handled.

Since I’m too tired of coding until my eyes almost bleed, I’m going to take the proposed option of keeping my version of PHP stuck in 5.3.10-1 until I really fixed the problem correctly. For those who will argue "for all these efforts, why not just spend it on writing your PDO shell?" ... and my answer is simple: I use PHP and MySQL exclusively in many projects where the server is a small computer with very limited facilities and resources, not to mention the capabilities of the processor. PDO downloads ALL that it can do, and not just what you are going to use, so there are some caveats in your resources that you won’t even use, which my projects cannot lose.

Fortunately, saving my version of PHP is being restrained at this time will not be detrimental. This is what many enterprise server farms actually do to avoid the need to bring everything together to be “in the current version” of something that is also insanely expensive to upgrade in just a couple of years.