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

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 将它们连接起来:

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

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

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