PHP怎么加where,php – 如何创建动态WHERE子句

参见英文答案 > is there a shorter way to set up queries when filtering results?                                    3个

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

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

然后,根据从下拉菜单中选择的选项,它将编译WHERE子句中的其他字段.有两个下拉菜单,适用于学生和问题.可能的结果是：

学生选择！=’全部’：添加StudentId =？在WHERE子句中

学生选择==’全部’：删除StudentId =？来自WHERE子句

选择问题！=’全部’：添加QuestionId =？在WHERE子句中

选择的问题==’全部’：删除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();

}

?>

解决方法:

尝试构建数组

$where = array();

if ((int) $studentID >0) {

$where[] = " studentID = '{$studentID}' ";

}

if ((int) $QuestionId >0) {

$where[] = " QuestionId = '{$QuestionId }' ";

}

并且最后通过AND statment破坏$where

if (!empty($where))

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

这只是一种方式.我没有调试这段代码.

标签：php,mysqli

来源： https://codeday.me/bug/20190926/1820498.html