如何创建一个mysqli动态WHERE子句 [英] How to create a mysqli dynamic WHERE clause

问题描述

我在这里有一个应用程序：应用程序

I have an application here: Application

该应用程序向您展示了我想用作筛选器的两个下拉菜单，以便能够根据学生答案确定所选学生和问题，从相应的下拉菜单中选择。

The application is there to show you the two drop down menus I want to use as a filter to be able to get Student's Answers determining on the student(s) selected and the question(s) selected form the respective drop down menus.

要查看下拉菜单，请在应用程序中选择评估 评估下拉菜单并提交，您将看到显示在下面的学生和问题下拉菜单。

To see the drop down menus, in the application select an Assessment from the Assessment drop down menu and submit, you will see the students and questions drop down menus displayed underneath.

现在我想要做的是创建一个动态的WHERE子句，这取决于用户单击获取学生答案按钮。

Now what I want to do is to create a dynamic WHERE clause depending on the options selected from the student(s) and question(s) drop down menus when the user clicked on the Get Student Answers button.

以下是当前查询。该查询必须在WHERE子句中始终具有缺省子句 SessionId =？。其他子句 studentId =？和 questionId =？取决于用户
从两个选项中选择的选项下降。

Below is the current query. The query has to have the default clause SessionId = ? at all times in the WHERE clause. The other clauses studentId = ? and questionId = ?depends on the options the user has chose from both drop downs.

$selectedstudentanswerqry = "
SELECT
StudentAlias, q.SessionId, QuestionNo, QuestionContent, o.OptionType, GROUP_CONCAT( DISTINCT Answer
ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, 
GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime
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
WHERE (SessionId = ?)
GROUP BY sa.StudentId, q.QuestionId
ORDER BY StudentAlias, q.SessionId, QuestionNo
";

global $mysqli;
$selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry);
// You only need to call bind_param once
$selectedstudentanswerstmt->bind_param("i",$_POST["session"]);
// get result and assign variables (prefix with db)
$selectedstudentanswerstmt->execute(); 
$selectedstudentanswerstmt->bind_result($detailsStudentAlias,$detailsSessionId,$detailsQuestionNo, 
$detailsQuestonContent,$detailsOptionType,$detailsAnswer,$detailsReplyType,$detailsStudentAnswer,$detailsResponseTime);
$selectedstudentanswerstmt->store_result();
$selectedstudentanswernum = $selectedstudentanswerstmt->num_rows(); 

下面是Students和Questions下拉菜单的示例html：

Below is the Students and Questions drop down menus as a sample html:

学生下拉菜单：

Students Drop Down Menu:

<select name="student" id="studentsDrop">
<option value="All">All</option>
<option value="3">u0499220 - Jack Briggs</option>
<option value="7">u0093220 - Mary Kay</option>
</form>

问题下拉菜单：

Questions Drop Down Menu:

<select name="question" id="questionsDrop">
<option value="All">All</option>
<option value="34">1</option>
<option value="35">2</option>
<option value="36">3</option>
</form>

我正在考虑如果选择了特定的学生，然后包含 StudentId在WHERE子句中，如果选择了具体的问题编号，那么在WHERE子句中包含 QuestionId =？。但是如果在Student下拉菜单中选择 All 值，那么从WHERE子句中移除 StudentId =？，就像我们一样寻找所有学生，而不是为特定的学生削减。这同样适用于如果从问题下拉菜单中选择全部值，但显然处理 QuestionId =？

I am thinking of something like if a specific student is selected then include StudentId = ? in WHERE clause, if specific question number is selected then include QuestionId = ? in WHERE clause. But if the All value is selected in Student drop down menu then remove StudentId = ? from the WHERE clause as we are look for all student, not whittling down for a specific student. This is same for if All value is selected from Question drop down menu but obviously dealing with QuestionId = ?

推荐答案

好的，你知道吗？我想我在这里误解了真正的问题。您可能会发现这样做效果更好：

Ok, you know what? I think I have misunderstood the real question here. You may find this to work better:

    $selectedstudentanswerqry = "
    SELECT
    StudentAlias, q.SessionId, QuestionNo, QuestionContent, o.OptionType, GROUP_CONCAT( DISTINCT Answer
    ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, 
    GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime
    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";

    if (studentId = something && questionId = something) {

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

    } else if (studentId = somethingelse && questionId = somethingelse) {

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

    } else {
    $selectedstudentanswerqry .= "WHERE (SessionId = ?)
    GROUP BY sa.StudentId, q.QuestionId
    ORDER BY StudentAlias, q.SessionId, QuestionNo
    ";
    }

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