問題描述

我有 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();