如何创建动态 WHERE 子句

2021-12-25 00:00:00 php mysqli

我正在尝试创建一个动态 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

我的问题是如何设置?

以下是我目前拥有的:

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

Try to build where array

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

相关文章