user1914374 user1914374 - 7 months ago 34
HTML Question

Errors appearing in mysqli code and call_user_func_array()

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

Answer

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