创建动态WHERE子句时出现mysqli警告 [英] mysqli warnings appearing when creating dynamic WHERE clause

问题描述

我正在尝试创建带有动态where子句的查询.该查询具有默认的where子句，无论从students and questions`下拉菜单中选择了哪个选项，它都将始终在其中检查SessionId = ?.

I am trying to create a query with a dynamic where clause. The query has a default where clause where it will always check for SessionId = ? no matter which option is chosen from the students andquestions` drop down menu.

然后我有一个php代码，如果用户选择了单个学生或单个问题(或者未选择student drop down menu or the user has not selected All in the question drop down menu). If the All`中的选项All)，这2个下拉菜单中的任何一个都不会将这些参数添加到WHERE子句中.

Then I have a php code where if the user has selected an individual student or an individual question (or in other words has not selected the option All in the student drop down menu or the user has not selectedAllin the question drop down menu). If theAll` option is selected in either of the 2 drop down menus then it does not add those parameters to the WHERE clause.

无论如何尝试执行此操作，都会出现以下错误:

Anyway when trying to do this, I am getting these errors:

Warning: mysqli::prepare(): (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE q.SessionId = ?' at line 15 in ... on line 320 

Fatal error: Call to a member function bind_param() on a non-object in ... on line 322

我的问题是，为了纠正错误，我需要在下面的mysqli和php代码中进行哪些整理?

My question is what do I need to sort out in the mysqli and php code below in order to fix the errors?

下面是带有注释的代码，用于显示错误所在的行号:

Below is the code with comments to display line numbers where errors are:

$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';
}

$selectedstudentanswerqry .= "
GROUP BY sa.StudentId, q.QuestionId
ORDER BY StudentAlias, q.SessionId, QuestionNo
";

// 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); //line 320 error
    // You only need to call bind_param once
    $selectedstudentanswerstmt->bind_param($parameterTypes,implode($parameters)); //line 322 error
}




// get result and assign variables (prefix with db)
$selectedstudentanswerstmt->execute(); 
$selectedstudentanswerstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname,$detailsSessionId,$detailsQuestionNo, 
$detailsQuestonContent,$detailsOptionType,$detailsNoofAnswers,$detailsAnswer,$detailsReplyType,$detailsQuestionMarks,$detailsStudentAnswer,$detailsResponseTime,
$detailsMouseClick,$detailsStudentMark);
$selectedstudentanswerstmt->store_result();
$selectedstudentanswernum = $selectedstudentanswerstmt->num_rows(); 

推荐答案

在WHERE语句之前，您似乎在进行GROUP BY和ORDER BY.

It looks like your doing your GROUP BY and ORDER BY before you WHERE statement.

尝试放置这些行，

$selectedstudentanswerqry .= "
  GROUP BY sa.StudentId, q.QuestionId
  ORDER BY StudentAlias, q.SessionId, QuestionNo
";

在if语句之后，

if(!empty($where)) {
  ...
}

这篇关于创建动态WHERE子句时出现mysqli警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！