过滤结果时是否有更短的方法来设置查询?

问题描述

我有 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.

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