title: Optimizing looping select statements
body: <p>So I'm currently have a database that holds survey questions and am now wanting to display the results. <strong>Things to note:</strong> There can be 2-6 answers per question depending on the question. </p>
  
  <p>These are the tables I'm using and some example data:</p>
  
  <pre><code> Table: answers_only         Table: questions_only
  ╔════════════════╦════════╗ ╔═════════════════╦═══════════════════╗
  ║ answer_ID (PK) ║ answer ║ ║question_ID (PK) ║ question          ║
  ╠════════════════╬════════╣ ╠═════════════════╬═══════════════════╣
  ║    65114       ║ yes    ║ ║       123       ║ Are you happy?    ║
  ╚════════════════╩════════╝ ╚═════════════════╩═══════════════════╝
  
                      Table: questions
  ╔════════════════╦══════════════════╦════════════════╦════════════════╗
  ║ unique_ID (PK) ║ question_ID (FK) ║ answer_ID (FK) ║ person_ID (FK) ║
  ╠════════════════╬══════════════════╬════════════════╬════════════════╣
  ║              1 ║              123 ║          65114 ║           5521 ║
  ╚════════════════╩══════════════════╩════════════════╩════════════════╝
  </code></pre>
  
  <p>So I have a table <strong>questions</strong> that contains IDs (FK) to <strong>questions_only</strong> which holds actual questions. Here I am grabbing the top 100 questions that were answered:</p>
  
  <pre><code>$result = mysqli_query($con,"SELECT question, questions.question_ID AS question_ID 
  FROM questions 
  INNER JOIN questions_only ON questions_only.question_ID=questions.question_ID 
  GROUP BY questions.question_ID 
  ORDER BY COUNT(*) DESC limit 100");
  </code></pre>
  
  <p>Next I go through each question and gather how many of each answer there was:</p>
  
  <pre><code>while($row = mysqli_fetch_array($result)) {
  
      $question_ID = $row['question_ID'];
  
      $result2 = mysqli_query($con,"SELECT answer, COUNT(*) AS 'count' 
      FROM questions 
      INNER JOIN answers_only ON answers_only.answer_ID=questions.answer_ID 
      WHERE questions.question_ID= '" . $question_ID . "' 
      GROUP BY answer ORDER BY COUNT(*) DESC"); 
  }
  </code></pre>
  
  <p>Currently the first line of code takes about 8 seconds to execute. The second part (looping through 100 select statements) is eating up around 350 seconds. So I'm looking for better ways to do this as currently it's not usable at this kind of time. Currently this code runs in the head tag of the document.</p>