問題描述

我正在嘗試創建一個動態 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.

這篇關于如何創建動態 WHERE 子句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！