过滤结果时，有没有更短的方法来设置查询? [英] 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 s连接起来:
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屋！
