I have an application here: Application
The application should show you two drop-down menus that I want to use as a filter in order to be able to determine the answers of students to the selected students, and the question (s) selected from the corresponding drop-down menus.
To see the drop-down menus, in the application, select Assessment from the Assessment drop-down menu and submit, you will see how menus and topics for students and questions are displayed below.
Now what I want to do is create a dynamic WHERE clause depending on the options selected from the drop-down menus of the student (s) and questions (s) when the user clicked the Get Student Answers button.
Below is the current request. The request should have a default SessionId = ? always in a WHERE clause. The remaining sentences are studentId = ? and questionId = ? Depend on user options selected from both drop-down lists.
$selectedstudentanswerqry = " SELECT StudentAlias, q.SessionId, QuestionNo, QuestionContent, o.OptionType, GROUP_CONCAT( DISTINCT Answer ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime 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 WHERE (SessionId = ?) GROUP BY sa.StudentId, q.QuestionId ORDER BY StudentAlias, q.SessionId, QuestionNo "; global $mysqli; $selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry); // You only need to call bind_param once $selectedstudentanswerstmt->bind_param("i",$_POST["session"]); // get result and assign variables (prefix with db) $selectedstudentanswerstmt->execute(); $selectedstudentanswerstmt->bind_result($detailsStudentAlias,$detailsSessionId,$detailsQuestionNo, $detailsQuestonContent,$detailsOptionType,$detailsAnswer,$detailsReplyType,$detailsStudentAnswer,$detailsResponseTime); $selectedstudentanswerstmt->store_result(); $selectedstudentanswernum = $selectedstudentanswerstmt->num_rows();
Below the drop-down menu "Students and Questions" as a sample html:
Student menu:
<select name="student" id="studentsDrop"> <option value="All">All</option> <option value="3">u0499220 - Jack Briggs</option> <option value="7">u0093220 - Mary Kay</option> </form>
Drop-down menu:
<select name="question" id="questionsDrop"> <option value="All">All</option> <option value="34">1</option> <option value="35">2</option> <option value="36">3</option> </form>
I am thinking of something like if a particular student is selected and then enable studentId = ? in the WHERE clause if a specific question number is selected, then include questionId = ? in the WHERE clause. But if All selected in the Student drop-down menu, then remove studentId = ? from the WHERE clause because we are looking for all students, not decreasing for a specific student. This is the same if All selected from the Question drop-down menu, but obviously deals with questionId = ?