如何创建动态WHERE子句 [英] How to create a dynamic WHERE clause

问题描述

我试图创建一个动态的WHERE子句，根据从下拉菜单中选择的选项，它将编译正确的WHERE子句.但是我认为我做的不正确.

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.

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

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

然后根据从下拉菜单中选择的选项，它将编译WHERE子句中的其他字段.有两个下拉菜单，分别用于Students和Questions.可能的结果是:

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':添加StudentId =?在WHERE子句中 Student selected == 'All':删除StudentId =吗?来自WHERE子句 Question selected != 'All':添加QuestionId =吗?在WHERE子句中 Question selected == 'All':删除QuestionId =吗?来自WHERE子句

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

    }


    ?>

推荐答案

尝试在数组中构建

$where = array();


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

最后通过AND语句爆破$ where

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.

这篇关于如何创建动态WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！