如何创建一个动态的WHERE子句[复制](How to create a dynamic WHERE

这个问题已经在这里有一个答案：

• 有一个较短的方式过滤结果时，设置查询？ 3个回答

我想创建一个动态的哪里哪里，这取决于选项，从下拉菜单中选择条款，它将编译正确的WHERE子句。 但我不认为我做正确。

首先，应该有WHERE子句，从下拉菜单中选择无论哪个选项应该有一个WHERE子句检查选择默认SessionId所以这应该是SessionId = ?

然后，根据从下拉菜单中选择的选项，它会编译WHERE子句中的其他领域。 有两个下拉菜单这对于Students和Questions 。 可能的结果是：

Student selected != 'All'添加StudentId =？ 在WHERE子句Student selected == 'All' ：删除StudentId =？ 从WHERE子句Question selected != 'All'添加QuestionId =？ 在WHERE子句Question selected == 'All' ：删除QuestionId =？ 从WHERE子句

我的问题是，我该怎么设置呢？

下面是我目前：

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

    }


    ?>

Answer 1:

尝试是建立一个数组

$where = array();


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

并在年底破灭$其中通过与statment

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

这是唯一的方法。 我没有调试代码。

Answer 2:

您的情况：

- 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{
}
?>

考虑到

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

检查，如果你想要这个？ 我想不是。

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

文章来源: How to create a dynamic WHERE clause [duplicate]