Below I have two HTML drop-down menus, one for students and one for questions:
<select name="question" id="questionsDrop"> <option value="0">All</option> <option value="2">What is 2+2</option> <option value="34">What is 3+3</option> <option value="42">What is 4+4</option> <option value="51">What is 5+5/option> </select> <select name="student" id="studentsDrop"> <option value="0">All</option> <option value="23">Jay Hart</option> <option value="32">Bubba Wright</option> <option value="43">Tim Grey</option> <option value="52">Mary Pine</option> </select>
The following is a mysqli query that will display results depending on the options selected from the two drop-down menus above:
$selectedstudentanswerqry = " SELECT sa.StudentId, 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 st INNER JOIN Student_Answer sa ON (st.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 "; // Initially empty $where = array('q.SessionId = ?'); $parameters = array($_POST["session"]); $parameterTypes = 'i'; // Check whether a specific student was selected if($_POST["student"] != '0') { $where[] = 'sa.StudentId = ?'; $parameters[] .= $_POST["student"]; $parameterTypes .= 'i'; } // Check whether a specific question was selected // NB: This is not an else if! if($_POST["question"] != '0') { $where[] = 'q.QuestionId = ?'; $parameters[] .= $_POST["question"]; $parameterTypes .= 'i'; } // If we added to $where in any of the conditionals, we need a WHERE clause in // our query if(!empty($where)) { $selectedstudentanswerqry .= ' WHERE ' . implode(' AND ', $where); global $mysqli; $selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry); // You only need to call bind_param once if (count($where) == 1) { $selectedstudentanswerstmt->bind_param($parameterTypes, $parameters[0]); } else if (count($where) == 2) { $selectedstudentanswerstmt->bind_param($parameterTypes, $parameters[0], $parameters[1]); } else if (count($where) == 3) { $selectedstudentanswerstmt->bind_param($parameterTypes, $parameters[0], $parameters[1], $parameters[2]); } } $selectedstudentanswerqry .= " GROUP BY sa.StudentId, q.QuestionId ORDER BY StudentAlias, q.SessionId, QuestionNo "; ....................................................................................... $arrStudentId = array(); $arrStudentAlias = array(); $arrStudentForename = array(); $arrStudentSurname = array(); $arrQuestionNo = array(); $arrQuestionContent = array(); while ($selectedstudentanswerstmt->fetch()) { $arrStudentId[ $detailsStudentId ] = $detailsStudentId; $arrStudentAlias[ $detailsStudentId ] = $detailsStudentAlias; $arrStudentForename[ $detailsStudentId ] = $detailsStudentForename; $arrStudentSurname[ $detailsStudentId ] = $detailsStudentSurname; $arrQuestionNo[ $detailsStudentId ] = $detailsQuestionNo; $arrQuestionContent[ $detailsStudentId ] = $detailsQuestonContent; } foreach ($arrStudentId as $key=>$student) { echo '<p><strong>Question:</strong> ' .htmlspecialchars($arrQuestionNo[$key]). ': ' .htmlspecialchars($arrQuestionContent[$key]). '</p>' . PHP_EOL; }
Now the data is displayed without problems if I select a specific student or a specific question from the corresponding drop-down menus, since db can extract values from these drop-down menus because they are the actual identifiers in db.
But the problem is that I select the All option from the dropdown menus. The value of the All parameter in both drop-down menus is 0 . Now 0 not in db as id, what I would like to do is that if the user selects the All parameter, then he displays All student information if it is selected in the student’s drop-down menu, All details of the question if they are selected in question drop-down menu.
The query is now working on collecting data for this, as it uses the dynamic WHERE clause built to include the appropriate conditions. But my question is, how can I get it to display all the details of the students / questions if the user has selected the All options in the corresponding drop-down menus?
UPDATE ACTIVITY with answer RING0:
Notice: Array to string conversion in ... on line 358 Warning: mysqli::prepare(): (42S22/1054): Unknown column 'Array' in 'where clause' in ... on line 360 Fatal error: Call to a member function bind_param() on a non-object in ... on line 370
UPDATED CODE:
$selectedstudentanswerqry = " SELECT sa.StudentId, 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 st INNER JOIN Student_Answer sa ON (st.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 "; // Initially empty $where = array(); $parameters = array(); $parameterTypes = ''; // Check whether a specific session was selected if($_POST["session"] != '0') { $where[] = array('q.SessionId = ?'); $parameters[] = array($_POST["session"]); $parameterTypes .= 'i'; } // Check whether a specific student was selected if($_POST["student"] != '0') { $where[] = 'sa.StudentId = ?'; $parameters[] .= $_POST["student"]; $parameterTypes .= 'i'; } // Check whether a specific question was selected // NB: This is not an else if! if($_POST["question"] != '0') { $where[] = 'q.QuestionId = ?'; $parameters[] .= $_POST["question"]; $parameterTypes .= 'i'; } // If we added to $where in any of the conditionals, we need a WHERE clause in // our query if(count($where) > 0) { $selectedstudentanswerqry .= ' WHERE ' . implode(' AND ', $where); global $mysqli; $selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry); // You only need to call bind_param once if (count($where) == 1) { $selectedstudentanswerstmt->bind_param($parameterTypes, $parameters[0]); } else if (count($where) == 2) { $selectedstudentanswerstmt->bind_param($parameterTypes, $parameters[0], $parameters[1]); } else if (count($where) == 3) { $selectedstudentanswerstmt->bind_param($parameterTypes, $parameters[0], $parameters[1], $parameters[2]); }
SCREENSHOT:
As you can see above, it shows that I selected the student, but I selected All Qustions. However, it only displays one question under the Student Answer heading, in this example the total number of questions you see in brackets is 2 , so it should show 2 questions, not one question
DB SCREENSHOT:
A slightly abridged version of a regular query, but this will not affect the main query, since it also shows the same number of rows. It shows 2 lines as there are two questions. Below are the results for one student StudentId = 1 , All questions are in SessionId = 26 26 ( SessionId = 26 ).