title: loop only going through one set of data
body: <p>I am having trouble with looping through data when selecting an <code>All</code> option in one or both of relevant drop down menus. It is not going through all the records and displaying them, it is only going trough one set of data. It is suppose to display details for each student and for each question they have participated in. But it is only showing one student and one question and that is all. </p>
  
  <p>Below is what it should display:</p>
  
  <p><img src="https://i.stack.imgur.com/KpR61.png" alt="enter image description here"></p>
  
  <p>But instead at the moment it is just displaying this:</p>
  
  <p><img src="https://i.stack.imgur.com/oBfPJ.png" alt="enter image description here"></p>
  
  <p>I believe what is causing this problem is the dynamic WHERE clause I have set up. Below is how dynamic WHERE clause works:</p>
  
  <ul>
  <li>Compulsory WHERE condition of <code>q.SessionId = ?</code></li>
  <li>If user selects single student from student drop down menu, then add <code>AND sa.StudentId = ?</code> to WHERE clause</li>
  <li>If user selects  <code>All</code> students option from student drop down menu, then remove or do not display <code>AND sa.StudentId = ?</code> from WHERE clause</li>
  <li>If user selects single question from question drop down menu, then add <code>AND q.QuestionId = ?</code> to WHERE clause</li>
  <li>If user selects  <code>All</code> questions option from question drop down menu, then remove or do not display <code>AND q.QuestionId = ?</code> from WHERE clause</li>
  </ul>
  
  <p>I have three drop down menus (below is what they look like with sample data):</p>
  
  <p><strong>Session:</strong></p>
  
  <pre><code>&lt;select name="session" id="sessionsDrop"&gt;
  &lt;option value="26"&gt;POKUB1&lt;/option&gt;
  &lt;option value="27"&gt;POKUB2&lt;/option&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p><strong>Student:</strong></p>
  
  <pre><code>&lt;select name="student" id="studentsDrop"&gt;
  &lt;option value="0"&gt;All&lt;/option&gt;
  &lt;option value="39"&gt;Luke Mcfadzen&lt;/option&gt;
  &lt;option value="40"&gt;Chris Tucker&lt;/option&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p><strong>Question:</strong></p>
  
  <pre><code>&lt;select name="question" id="questionsDrop"&gt;
  &lt;option value="0"&gt;All&lt;/option&gt;
  &lt;option value="72"&gt;1&lt;/option&gt;
  &lt;option value="73"&gt;2&lt;/option&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p>Don't forget how the WHERE condition is suppose to work which I mentioned above in this question. Lets say the session chosen from session drop down menu is <code>POKUB 1, drop down value: 26</code>.</p>
  
  <p>If you select a single student and a single question, then it displays the details correctly e.g. </p>
  
  <ul>
  <li><strong>Student:</strong> Luke McFadzen - drop down value: 39</li>
  <li><strong>Question:</strong> 1 - drop down value: 72</li>
  </ul>
  
  <p>So WHERE condition is <code>q.SessionId = 26 AND sa.StudentId = 39 AND q.QuestionId = 72</code>.</p>
  
  <p>But if I select an <code>All</code> option in either or both student and question drop down menu, then the output only displays one student and one question and for some strange reason it combines answers from all questions and combines all student answer answers into the single output.</p>
  
  <p>Now <code>All</code> option in both drop down menus has a drop down value of <code>0</code>, now <code>0</code> is not a value to select from database but we have stated in dynamic where clause that if a <code>0</code> value is chosen from a specific drop down menu, then remove the relevant condition from the WHERE clause, so for example:</p>
  
  <ul>
  <li><code>All</code> students and single question (value <code>72</code>) - <code>WHERE q.SessionId
  = 26 AND q.QuestionId = 72</code></li>
  <li>Single (value <code>39</code>) student and <code>All</code> questions - <code>WHERE q.SessionId
  = 26 AND sa.StudentId = 39</code></li>
  <li><code>All</code> students and <code>All</code> questions - <code>WHERE q.SessionId = 26</code></li>
  </ul>
  
  <p><strong>THE ABOVE SCENARIOS ARE HAVING THE PROBLEMS</strong></p>
  
  <p>If I just left the query with a static WHERE clause <code>WHERE q.SessionId = ?</code> then it outputs the details correctly if I select <code>All</code> students and <code>All</code> Questions, but I need the query to work for all different possible options selected from the drop down menus and hence why I need a dynamic WHERE clause. How can I get it to work so the correct details are outputted?</p>
  
  <p>Code:</p>
  
  <pre><code>    $selectedstudentanswerqry = "
          SELECT
          sa.StudentId, StudentAlias, StudentForename, StudentSurname, q.SessionId, 
          q.QuestionId, 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, 
          (
          SELECT sum( StudentMark )
          FROM Student_Answer sta
          WHERE sa.StudentId = sta.StudentId
          AND sa.QuestionId = sta.QuestionId
          )StudentMark
          FROM Student st
          INNER JOIN Student_Answer sa ON (st.StudentId = sa.StudentId)
          INNER JOIN Student_Response sr ON (sa.StudentId = sr.StudentId) AND sa.QuestionId = sr.QuestionId
          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 if POST is empty
  
          // Check whether a specific student was selected
          $p_student = empty($_POST["student"])?'':$_POST["student"];
  
          switch($p_student){
          case 0:
              //dont' add where filters
              break;
          default:
              $where[] = 'sa.StudentId = ?';
              $parameters[] .= $_POST["student"];
              $parameterTypes .= 'i';
          }
  
          // Check whether a specific question was selected
          $p_question = empty($_POST["question"])?'':$_POST["question"];
  
          switch($p_question){
          case 0:
              //dont' add where filters
              break;
          default:
              $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-&gt;prepare($selectedstudentanswerqry);
              // You only need to call bind_param once
  
              if (count($where) == 1) {
              $selectedstudentanswerstmt-&gt;bind_param($parameterTypes, $parameters[0]);
          }
          else if (count($where) == 2) {
              $selectedstudentanswerstmt-&gt;bind_param($parameterTypes, $parameters[0], $parameters[1]);
          }
          else if (count($where) == 3) {
              $selectedstudentanswerstmt-&gt;bind_param($parameterTypes, $parameters[0], $parameters[1], $parameters[2]);
          }
  
          }
  
          $selectedstudentanswerqry .= "
            GROUP BY sa.StudentId, q.QuestionId
            ORDER BY StudentAlias, q.SessionId, QuestionNo
          ";
  
      // get result and assign variables (prefix with db)
      $selectedstudentanswerstmt-&gt;execute(); 
      $selectedstudentanswerstmt-&gt;bind_result($detailsStudentId,$detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname,$detailsSessionId,
      $detailsQuestionId,$detailsQuestionNo,$detailsQuestionContent,$detailsOptionType,$detailsNoofAnswers,$detailsAnswer,$detailsReplyType,$detailsQuestionMarks,
      $detailsStudentAnswer,$detailsResponseTime,$detailsMouseClick,$detailsStudentMark); 
  
      $selectedstudentanswerstmt-&gt;store_result();
      $selectedstudentanswernum = $selectedstudentanswerstmt-&gt;num_rows(); 
  
   while ($selectedstudentanswerstmt-&gt;fetch()) {
  
                  //Check if the student data exist.
                  if (!isset($questions[$detailsStudentId])) {
                      $questions[$detailsStudentId] = array(
                          'studentalias' =&gt; $detailsStudentAlias,
                          'studentforename' =&gt; $detailsStudentForename,
                          'studentsurname' =&gt; $detailsStudentSurname,
                          'questions' =&gt; array()
                      );
                  }
  
                  $questions[$detailsStudentId]['questions'][$detailsQuestionId] = array(
                      'questionno'=&gt;$detailsQuestionNo,
                      'content'=&gt;$detailsQuestionContent,
                      'optiontype'=&gt;$detailsOptionType,
                      'noofanswers'=&gt;$detailsNoofAnswers,
                      'answer'=&gt;$detailsAnswer,
                      'replytype'=&gt;$detailsReplyType,
                      'questionmarks'=&gt;$detailsQuestionMarks,
                      'studentanswer'=&gt;$detailsStudentAnswer,
                      'responsetime'=&gt;$detailsResponseTime,
                      'mouseclick'=&gt;$detailsMouseClick,
                      'studentmark'=&gt;$detailsStudentMark
                  );
              }
  
              $selectedstudentanswerstmt-&gt;close();
  
      foreach ($questions as $studentId =&gt; $studentData) {
          echo '&lt;p&gt;'.$studentData['studentalias'].' - '.$studentData['studentforename'].' '.$studentData['studentsurname'].'&lt;/p&gt;';
  
          foreach ($studentData['questions'] as $questionId =&gt; $questionData) {
              echo '&lt;p&gt;&lt;strong&gt;'.$questionData['questionno'].': '.$questionData['content'].'&lt;br/&gt;';
              echo $questionData['optiontype'].' - '.$questionData['noofanswers'].' - '.$questionData['answer'].' - '.$questionData['replytype'].' - '.$questionData['questionmarks'].'&lt;br/&gt;';
              echo $questionData['studentanswer'].' - '.$questionData['responsetime'].' - '.$questionData['mouseclick'].' - '.$questionData['studentmark'].'&lt;/strong&gt;&lt;/p&gt;';
          }
      }
  </code></pre>
  
  <p>Below are the possible var_dumps for <code>$_POST['student']</code> and <code>$_POST['question']</code>:</p>
  
  <p><strong>SINGLE STUDENT AND SINGLE QUESTION:</strong></p>
  
  <ul>
  <li>Student: Chris Tucker - string(2) "40" </li>
  <li>Question: 1 - string(2) "72" </li>
  </ul>
  
  <p><strong>SINGLE STUDENT AND ALL QUESTIONS:</strong></p>
  
  <ul>
  <li>Student: Chris Tucker - string(2) "40" </li>
  <li>Question: All - string(1) "0" </li>
  </ul>
  
  <p><strong>ALL STUDENTS AND SINGLE QUESTION:</strong></p>
  
  <ul>
  <li>Student: All - string(1) "0" </li>
  <li>Question: 1 - string(1) "72" </li>
  </ul>
  
  <p><strong>ALL STUDENTS AND ALL QUESTIONS:</strong></p>
  
  <ul>
  <li>Student: All - string(1) "0" </li>
  <li>Question: All - string(1) "0" </li>
  </ul>
  
  <p>Below is example <code>var_dump($questions);</code> if I select <code>All</code> students and <code>All</code> questions:</p>
  
  <pre><code>array(1) { 
  [39]=&gt; array(4) { 
  ["studentalias"]=&gt; string(8) "u4838229" 
  ["studentforename"]=&gt; string(5) "Chris" 
  ["studentsurname"]=&gt; string(6) "Tucker" 
  ["questions"]=&gt; array(1) { 
  [72]=&gt; array(11) { 
  ["questionno"]=&gt; int(1) 
  ["content"]=&gt; string(14) "What is a RAM?" 
  ["optiontype"]=&gt; string(3) "A-E" 
  ["noofanswers"]=&gt; int(1) 
  ["answer"]=&gt; string(7) "B,C,D,E" 
  ["replytype"]=&gt; string(6) "Single" 
  ["questionmarks"]=&gt; int(5) 
  ["studentanswer"]=&gt; string(9) "A,B,C,D,E" 
  ["responsetime"]=&gt; string(8) "00:00:07" 
  ["mouseclick"]=&gt; int(1) 
  ["studentmark"]=&gt; string(1) "2" } } } }
  </code></pre>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>If I keep the mysqli query using a static WHERE clause when it just checks for <code>q.SessionId = ?</code> only, then when I select the <code>All</code> options in etiher or both drop down menus, it outputs the results fine no problem, only wither the dynamic where clause which I need to include as user my be able to select an indivdual student and/or indivdiual question that the <code>All</code> options do not work properly. Below is working mysqli code when using static WHERE clause:</p>
  
  <pre><code>$selectedstudentanswerqry = "
      SELECT
      sa.StudentId, StudentAlias, StudentForename, StudentSurname, q.SessionId, 
      q.QuestionId, 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, 
      (
      SELECT sum( StudentMark )
      FROM Student_Answer sta
      WHERE sa.StudentId = sta.StudentId
      AND sa.QuestionId = sta.QuestionId
      )StudentMark
      FROM Student st
      INNER JOIN Student_Answer sa ON (st.StudentId = sa.StudentId)
      INNER JOIN Student_Response sr ON (sa.StudentId = sr.StudentId) AND sa.QuestionId = sr.QuestionId
      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
      LEFT JOIN Session_Taken sta ON (st.StudentId = sta.StudentId)
        WHERE q.SessionId = ?
        GROUP BY sa.StudentId, q.QuestionId
        ORDER BY StudentAlias, q.SessionId, QuestionNo
      ";
      global $mysqli;
  $selectedstudentanswerstmt=$mysqli-&gt;prepare($selectedstudentanswerqry);
  $selectedstudentanswerstmt-&gt;bind_param('i',$_POST['session']);
  // get result and assign variables (prefix with db)
  $selectedstudentanswerstmt-&gt;execute(); 
  $selectedstudentanswerstmt-&gt;bind_result($detailsStudentId,$detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname,$detailsSessionId,
  $detailsQuestionId,$detailsQuestionNo,$detailsQuestionContent,$detailsOptionType,$detailsNoofAnswers,$detailsAnswer,$detailsReplyType,$detailsQuestionMarks,
  $detailsStudentAnswer,$detailsResponseTime,$detailsMouseClick,$detailsStudentMark); 
  </code></pre>