How to create a dynamic WHERE clause [duplicate]

问题:

This question already has an answer here:

• is there a shorter way to set up queries when filtering results? 3 answers

I am trying to create a dynamic WHERE clause where depending on which options are chosen from the drop down menus, it will compile the correct WHERE clause. But I do not think I am doing it correctly.

First of all there should be a default WHERE clause, no matter which option is selected from the drop down menus there should be a WHERE clause checking for selected SessionId so this should be SessionId = ?

Then depending on the options chosen from the drop down menus it will compile the other fields in the WHERE clause. There are two drop down menus which are for Students and Questions. The possible outcomes are:

Student selected != 'All' : Add StudentId = ? in WHERE clause Student selected == 'All' : Remove StudentId = ? from WHERE clause Question selected != 'All' : Add QuestionId = ? in WHERE clause Question selected == 'All' : Remove QuestionId = ? from WHERE clause

My question is that how can I set this up?

Below is what I have currently:

        if(isset($_POST['answerSubmit'])) // we have subbmited the third form
        {

    $selectedstudentanswerqry = "
    SELECT
    StudentAlias, StudentForename, StudentSurname, q.SessionId, QuestionNo, QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( DISTINCT Answer
    ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, QuestionMarks, 
    GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime, MouseClick, StudentMark
    FROM Student s
    INNER JOIN Student_Answer sa ON (s.StudentId = sa.StudentId)
    INNER JOIN Student_Response sr ON (sa.StudentId = sr.StudentId)
    INNER JOIN Question q ON (sa.QuestionId = q.QuestionId)
    INNER 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
    ";

    if ($_POST['student'] != 'All'){
    $selectedstudentanswerqry .= "
    WHERE (SessionId = ? AND StudentId = ?)
    ";
    }

    if ($_POST['question'] != 'All'){
    $selectedstudentanswerqry .= "
    WHERE (SessionId = ? AND QuestionId = ?)
    ";
    }

    $selectedstudentanswerqry .= "
    GROUP BY sa.StudentId, q.QuestionId
    ORDER BY StudentAlias, q.SessionId, QuestionNo
    ";

    global $mysqli;
    $selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry);
    if ($_POST['student'] != 'All'){
    // You only need to call bind_param once
    $selectedstudentanswerstmt->bind_param("ii",$_POST["session"],$_POST["student"]);
    }
    if ($_POST['question'] != 'All'){
    // You only need to call bind_param once
    $selectedstudentanswerstmt->bind_param("ii",$_POST["session"],$_POST["question"]);
    }
    // get result and assign variables (prefix with db)
    $selectedstudentanswerstmt->execute(); 
    $selectedstudentanswerstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname,$detailsSessionId,$detailsQuestionNo, 
    $detailsQuestonContent,$detailsOptionType,$detailsNoofAnswers,$detailsAnswer,$detailsReplyType,$detailsQuestionMarks,$detailsStudentAnswer,$detailsResponseTime,
    $detailsMouseClick,$detailsStudentMark);
    $selectedstudentanswerstmt->store_result();
    $selectedstudentanswernum = $selectedstudentanswerstmt->num_rows();     

    }


    ?>

回答1:

Try to build where array

$where = array();


if ((int) $studentID >0) {
   $where[] = " studentID = '{$studentID}' ";
}
if ((int) $QuestionId  >0) {
   $where[] = " QuestionId  = '{$QuestionId }' ";
}

and at end implode $where by AND statment

if (!empty($where)) 
$query['where'] = ' WHERE '. implode(' AND ', $where);

It's only a way. I did not debug this code.

回答2:

Your cases:

- Student selected != 'All' : Add StudentId = ? in WHERE clause 
- Student selected == 'All' : Remove StudentId = ? from WHERE clause 
- Question selected != 'All' : Add QuestionId = ? in WHERE clause 
- Question selected == 'All' : Remove QuestionId = ? from WHERE clause

<?php
$selectedstudentanswerqry = "WHERE SessionId = ? ";
if ($_POST['student'] != 'All'){
 $selectedstudentanswerqry .= " and StudentId = ? ";
}
else{
/*
$selectedstudentanswerqry .= "
//what is condition for if student == all ?
";
*/
}
if ($_POST['question'] != 'All'){
  $selectedstudentanswerqry .= " and QuestionId = ? ";
}
else{
}
?>

consider that

student = 1 then:

if student != All = true
if question != AA = true

student = All
if student != All = false
if question != AA = true

question = 1 
if student != All = true
if question != AA = true

question = All
if student != All = true
if question != AA = false

check if you want this? I think no.

    //case1
    if ($_POST['student'] != 'All'){
      $selectedstudentanswerqry .= "
      WHERE (SessionId = ? AND StudentId = ?)
      ";
    }

    //case2
    if ($_POST['question'] != 'All'){
      //case 2.1
      if ($_POST['student'] != 'All'){
        $selectedstudentanswerqry .= "
         and  (QuestionId = ?)
        ";
      }
      //case 2.2
      else{
        $selectedstudentanswerqry .= "
        WHERE (SessionId = ? AND QuestionId = ?)
        ";
      }

    }
/*
    testing
    1- student != All, question != All
    case1: true
    case1: result: $selectedstudentanswerqry = WHERE (SessionId = ? AND StudentId = ?)
    case2 : true
    case 2.1: true
    case2.1 result:  $selectedstudentanswerqry .= and  (QuestionId = ?)

    2- student != All question = All
    case1: true
    case1: result: $selectedstudentanswerqry = WHERE (SessionId = ? AND StudentId = ?)
    case2: false

    3- student = All question != All
    case1: false
    case2: true
    case2.1: false
    case2.2: true
    case2.2 result: $selectedstudentanswerqry = WHERE (SessionId = ? AND QuestionId = ?)

    4- student = All question = All
    case1: false
    case2: false
*/