is there a shorter way to set up queries when filt

问题:

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:

• Select All Students and All Questions
• Select All Students and One question
• Select All Questions and one Student
• Select One Student and One Question

Below is drop down menus:

<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>

Now I want to set up a mysqli query which determines on the students and questions selected from the drop down menu.

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?

Do I have to use:

 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

UPDATE:

What I have at moment:

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


}

?>

回答1:

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

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

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.

回答2:

This is code I used where there are 64 permutations of user input. There can be a rider ID from another page, partial first and/or last name, a year, a month or a course ID.

Construct part of the where clause with a variety of ANDs, IS LIKE, etc.

$params = array();
$types = "";


if ( isset($_GET["riderid"]) && $_GET["riderid"] )  {
    $where = sprintf (' AND %s.id = ?', $tables["rider"]);
    $params[]= $_GET["riderid"];
    $types = $types .'i';

} else {
    if ( isset($_GET["last"]) && $_GET["last"] ) {

        $where = sprintf(' AND %s.last like ?', $tables["rider"]);
        $params[] = $_GET["last"] . "%";
        $types = $types . "s";
    }

    if ( isset($_GET["first"]) && $_GET["first"] ) {

        $whereFirst = sprintf(' AND %s.first like ?', $tables["rider"]);
        $where = $where . $whereFirst;
        $params[] = $_GET["first"] . "%";
        $types = $types . "s";
    }
}

if ( isset($_GET["year"]) && $_GET["year"] && $_GET["year"] != -1 )  {
    $whereYear = sprintf(' AND YEAR(%s.date) = ?', $tables["race"]);
    $where = $where . $whereYear;
    $params[] = $_GET["year"];
    $types = $types . "i";
}

if ( isset($_GET["month"]) && $_GET["month"] && $_GET["month"] != -1 )  {
    $whereMonth = sprintf(' AND month(%s.date) = ?', $tables["race"]);
    $where = $where . $whereMonth;
    $params[] = $_GET["month"];
    $types = $types . "i";

}  

if ( isset($_GET["courseid"]) && $_GET["courseid"] && $_GET["courseid"] != -1 ) {
    $whereCourse = sprintf(' AND %s.courseid = ?', $tables["race"]);
    $where = $where . $whereCourse;
    $params[] = $_GET["courseid"];
    $types = $types . "i";
}

Here is the query.

    //
    // Show selected races
    //
    $listQuery = "SELECT {$tables["race"]}.raceid, {$tables["race"]}.typeid, " .
             "           tag,  {$tables["race"]}.date, " .
             "           {$tables["location"]}.name, {$tables["course"]}.dist, " .
             "           {$tables["course"]}.description,  " .
             "           {$tables["result"]}.time, {$tables["result"]}.dnf, " .
             "           {$tables["rider"]}.first, {$tables["rider"]}.last ".
             "  FROM {$tables["race"]}, {$tables["sysracetype"]}, " .
             "       {$tables["course"]}, {$tables["location"]}, " .
             "       {$tables["result"]}, {$tables["rider"]} " .
             " WHERE {$tables["race"]}.courseid = {$tables["course"]}.courseid " .
             "   AND {$tables["race"]}.typeid = {$tables["sysracetype"]}.typeid " .
             "   AND {$tables["course"]}.locid = {$tables["location"]}.locid " .
             "   AND {$tables["race"]}.raceid = {$tables["result"]}.raceid" .
             "   AND {$tables["result"]}.riderid = {$tables["rider"]}.id" .
             $where .
             $orderby;

Prepare and bind the parameters.

$stmt = mysqli_prepare ($db_connection, $listQuery);
// 
// Handle the varying number of SQL parameters / place holders.
// Push the first two parameter of mysqli_stmt_bind_param
//
$bindArgs = array ($stmt, $types);

//
// Change parameters to refs  (depends on PHP version)   
//
foreach ($params as $key => $value)  
    $bindArgs[] =& $params[$key];

//
// Use "call back" to pass all params.
//
call_user_func_array("mysqli_stmt_bind_param", $bindArgs);

回答3:

I solved a similar problem this way. Where $parmMap is constructed with powers of 2 (1,2) as I detect each optional WHERE clause item and append the appropriate AND fieldName = ?.

switch ($parmMap ) {
    case 0:
      break;
    case 1:
      mysqli_stmt_bind_param ($stmt, "i", $courseParm );
      break;
    case 2:
      mysqli_stmt_bind_param ($stmt, "i", $yearParm );
      break;
    case 3:
      mysqli_stmt_bind_param ($stmt, "ii", $courseParm, $yearParm );
      break;
    default:
      break;
}

I won't be using this though for another query that has six optional where clause conditions! I've discovered call_user_func_array.