过滤结果时是否有更短的方法来设置查询? [英] is there a shorter way to set up queries when filtering results?
问题描述
我有 2 个下拉菜单，用户可以在其中使用下拉菜单过滤他们希望看到的学生和问题.可能的过滤器类型有:
I have 2 drop down menus where the user can use the drop down menus to filter whih students and questions they wish to see. The possible types of filters are:
- 选择所有学生和所有问题
- 选择所有学生和一个问题
- 选择所有问题和一名学生
- 选择一名学生和一个问题
下面是下拉菜单:
<p>
<strong>Student:</strong>
<select name="student" id="studentsDrop">
<option value="All">All</option>
<?php
while ( $currentstudentstmt->fetch() ) {
$stu = $dbStudentId;
if(isset($_POST["student"]) && $stu == $_POST["student"])
echo "<option selected='selected' value='$stu'>" . $dbStudentAlias . " - " . $dbStudentForename . " " . $dbStudentSurname . "</option>" . PHP_EOL;
else
echo "<option value='$stu'>" . $dbStudentAlias . " - " . $dbStudentForename . " " . $dbStudentSurname . "</option>" . PHP_EOL;
}
?>
</select>
</p>
<p>
<strong>Question:</strong>
<select name="question" id="questionsDrop">
<option value="All">All</option>
<?php
while ( $questionsstmt->fetch() ) {
$ques = $dbQuestionId;
if(isset($_POST["question"]) && $ques == $_POST["question"])
echo "<option selected='selected' value='$ques'>" . $dbQuestionNo . "</option>" . PHP_EOL;
else
echo "<option value='$ques'>" . $dbQuestionNo . "</option>" . PHP_EOL;
}
?>
</select>
</p>
现在我想设置一个 mysqli 查询来确定从下拉菜单中选择的学生和问题.
Now I want to set up a mysqli query which determines on the students and questions selected from the drop down menu.
我的问题只是我是否需要设置 4 个查询来检查我从下拉菜单中提到的 4 种可能性，还是有更短的方法?
My question is simply do I need to set up 4 queries checking for the 4 possibilities I mentioned from the drop down menus or is there are a shorter way?
我是否必须使用:
if ($_POST['question'] == 'All' && if ($_POST['student'] == 'All'){){
//NO WHERE CLAUSE
if ($_POST['question'] == 'All' && if ($_POST['student'] != 'All'){){
//WHERE CLAUSE FOR FINDING SELECTED STUDENT
if ($_POST['question'] != 'All' && if ($_POST['student'] == 'All'){){
//WHERE CLAUSE FOR FINDING SELECTED QUESTION
if ($_POST['question'] != 'All' && if ($_POST['student'] != 'All'){){
//WHERE CLAUSE FOR FINDING SELECTED QUESTION AND SELECTED STUDENT
更新:
我现在拥有的:
function AssessmentIsSubbmitted()
{
if(isset($_POST['answerSubmit'])) // we have subbmited the first form
{
//QUERY 1: Student details depending on selected student(s)
if ($_POST['student'] == 'All'){
$selectedstudentqry = "
SELECT
StudentAlias, StudentForename, StudentSurname
FROM Student s
INNER JOIN Student_Session ss ON s.StudentId = ss.StudentId
WHERE SessionId = ?
ORDER BY StudentAlias
";
global $mysqli;
$selectedstudentstmt=$mysqli->prepare($selectedstudentqry);
// You only need to call bind_param once
$selectedstudentstmt->bind_param("i",$_POST["session"]);
// get result and assign variables (prefix with db)
$selectedstudentstmt->execute();
$selectedstudentstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname);
$selectedstudentstmt->store_result();
$selectedstudentnum = $selectedstudentstmt->num_rows();
}else{
$selectedstudentqry = "
SELECT
StudentAlias, StudentForename, StudentSurname
FROM
Student
WHERE
(StudentId = ?)
ORDER BY StudentAlias
";
global $mysqli;
$selectedstudentstmt=$mysqli->prepare($selectedstudentqry);
// You only need to call bind_param once
$selectedstudentstmt->bind_param("i",$_POST["student"]);
// get result and assign variables (prefix with db)
$selectedstudentstmt->execute();
$selectedstudentstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname);
$selectedstudentstmt->store_result();
$selectedstudentnum = $selectedstudentstmt->num_rows();
}
//QUERY 2: Question details depending on selected question(s)
if ($_POST['question'] == 'All'){
$selectedquestionqry = " SELECT q.QuestionNo, q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( DISTINCT Answer
ORDER BY Answer
SEPARATOR ',' ) AS Answer, r.ReplyType, q.QuestionMarks
FROM Question q
LEFT 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 q.QuestionId
ORDER BY q.QuestionId";
";
global $mysqli;
$selectedquestionstmt=$mysqli->prepare($selectedquestionqry);
// You only need to call bind_param once
$selectedstudentstmt->bind_param("i",$_POST["session"]);
// get result and assign variables (prefix with db)
$selectedquestionstmt->execute();
$selectedquestionstmt->bind_result($detailsQuestionNo,$detailsQuestionContent,$detailsOptionType,$detailsNoofAnswers,
$detailsAnswer,$detailsReplyType,$detailsQuestionMarks);
$selectedquestionstmt->store_result();
$selectedquestionnum = $selectedquestionstmt->num_rows();
}else{
$selectedquestionqry = "
SELECT q.QuestionNo, q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( DISTINCT Answer
ORDER BY Answer
SEPARATOR ',' ) AS Answer, r.ReplyType, q.QuestionMarks
FROM Question q
LEFT 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 QuestionId = ?
GROUP BY q.QuestionId
ORDER BY q.QuestionId
";
global $mysqli;
$selectedquestionstmt=$mysqli->prepare($selectedquestionqry);
// You only need to call bind_param once
$selectedquestionstmt->bind_param("i",$_POST["question"]);
// get result and assign variables (prefix with db)
$selectedquestionstmt->execute();
$selectedquestionstmt->bind_result($detailsQuestionNo,$detailsQuestionContent,$detailsOptionType,$detailsNoofAnswers,
$detailsAnswer,$detailsReplyType,$detailsQuestionMarks);
$selectedquestionstmt->store_result();
$selectedquestionnum = $selectedquestionstmt->num_rows();
}
//QUERY 3: Student Answers depending on selected student(s) and selected question(s)
$studentanswerqry = "
SELECT
sa.StudentId, sa.QuestionId, GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime, MouseClick, StudentMark
FROM Student_Answer sa
INNER JOIN Student_Response sr ON sa.StudentId = sr.StudentId
WHERE
(sa.StudentId = ? AND sa.QuestionId = ?)
GROUP BY sa.StudentId, sa.QuestionId
";
global $mysqli;
$studentanswerstmt=$mysqli->prepare($studentanswerqry);
// You only need to call bind_param once
$studentanswerstmt->bind_param("ii",$_POST["student"], $_POST["question"]);
// get result and assign variables (prefix with db)
$studentanswerstmt->execute();
$studentanswerstmt->bind_result($detailsStudentAnswer,$detailsResponseTime,$detailsMouseClick,$detailsStudentMark);
$studentanswerstmt->store_result();
$studentanswernum = $studentanswerstmt->num_rows();
}
?>
推荐答案
您可以迭代地构建一个 WHERE 子句.考虑到 WHERE 子句执行显式过滤，因此对于您选择所有"某些内容的情况，您不需要添加任何过滤器.其他过滤器相互建立，所以我们可以简单地在 WHERE 中使用 AND 将它们连接起来:
You can iteratively build a WHERE clause. Consider that a WHERE clause does explicit filtering, so for cases where you're selecting "all" of something, you don't need to add any filters. The other filters build upon each other, so we can simply join them with ANDs in the WHERE:
$query = 'SELECT ... FROM ...';
// Initially empty
$where = array();
$parameters = array();
// Check whether a specific student was selected
if($stu !== 'All') {
$where[] = 'stu = ?';
$parameters[] = $stu;
}
// Check whether a specific question was selected
// NB: This is not an else if!
if($ques !== 'All') {
$where[] = 'ques = ?';
$parameters[] = $ques;
}
// If we added to $where in any of the conditionals, we need a WHERE clause in
// our query
if(!empty($where)) {
$query .= ' WHERE ' . implode(' AND ', $where);
}
$result = prepare_and_execute_query($query, $parameters);
<小时>
好的，看看您的更新，您有一组相当复杂的查询，但可以将其合并为一个语句.试试这个:
Okay, so looking at your update, you have a fairly complex set of queries, but it's possible to combine it into one statement. Give this a try:
SELECT
s.StudentId, s.StudentAlias, s.StudentForename, -- Student fields
s.StudentSurname,
q.QuestionId, q.QuestionNo, q.QuestionContent, -- Question fields
q.OptionType, q.NoofAnswers, q.Answer, q.ReplyType,
q.QuestionMarks,
GROUP_CONCAT(DISTINCT sa.StudentAnswer ORDER BY -- Answer fields
sa.StudentAnswer SEPARATOR ',') AS StudentAnswer,
sr.ResponseTime, sr.MouseClick, sr.StudentMark
FROM Student s
INNER JOIN Student_Answer sa ON (s.StudentId = sa.StudentId)
INNER JOIN Question q ON (sa.QuestionId = q.QuestionId)
INNER JOIN Student_Response sr ON (sa.StudentId = sr.StudentId)
WHERE -- This WHERE clause may be entirely removed,
-- depending on the filters
s.StudentId = ? AND -- This is removed if $_POST['student'] is 'All'
q.QuestionId = ? -- This is removed if $_POST['question'] is 'All'
GROUP BY sa.StudentId, q.QuestionId
我认为这会满足您的需求.我不太确定哪些字段是 Student_Response 的一部分，哪些是 Student_Answer 的一部分，所以你可能不得不摆弄 SELECT.
I think this will do what you want. I wasn't quite sure about which fields are part of Student_Response and which are part of Student_Answer so you might have to fiddle with the columns in the SELECT.
不幸的是，这种方法不适用于您的用例.但是我们仍然可以考虑我提出的原始逻辑如何处理您给出的查询之一:
Unfortunately that approach doesn't work for your use case. But we can still consider how the original logic I proposed would work with one of your queries given:
$selectedstudentqry = "
SELECT
StudentAlias, StudentForename, StudentSurname
FROM
Student ";
if($_POST['student'] !== 'All') { // Check here
$selectedstudentqry .= "
WHERE
(StudentId = ?) ";
}
$selectedstudentqry .= "
ORDER BY StudentAlias
";
global $mysqli;
$selectedstudentstmt=$mysqli->prepare($selectedstudentqry);
if($_POST['student'] !== 'All') {
// You only need to call bind_param once
$selectedstudentstmt->bind_param("i",$_POST["student"]);
}
// get result and assign variables (prefix with db)
$selectedstudentstmt->execute();
$selectedstudentstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname);
$selectedstudentstmt->store_result();
$selectedstudentnum = $selectedstudentstmt->num_rows();
请注意我们如何简单地将外部 if 移动到代码中更具体的位置以减少代码重复.如果您看到类似的重复，则很有可能您正在做诸如使条件过于宽泛之类的事情.不过，您在尝试简化这一过程并减少冗余方面肯定走在正确的轨道上.
Notice how we've simply moved the outer ifs to more specific places within the code to decrease code duplication. If you see duplication like you have, there's a very good chance that you're doing something like making your conditionals too broad. You're definitely on the right track with trying to simplify this and reduce redundancy, though.
这篇关于过滤结果时是否有更短的方法来设置查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
