Need a MySQL query that can filter results

Question

Here chapter_id may be two or more than two. I need to query so that each chapter produces a result equal to no. questions.

i.e. If the general question is 50, and out of 5 sections, then each chapter should give 10 questions.

SELECT id, chapter_id, question, answer FROM `questions`
WHERE `chapter_id` IN (19, 20, 21, 22, 23)
ORDER BY `chapter_id`

I tried this by requesting individual queries. that is, first check the number of chapters and loop them through the array.

<?php
  $total_qsn = 50;
  $chap[] = {19, 20, 21, 22, 23};
  $avg = $total_qsn/count($chap);
  for($i=0, $i<count($chap); i++){
      $sql = "SELECT id, chapter_id, question, answer FROM `questions` WHERE chapter_id = {$chap[i]} LIMIT 0, {$avg}";
      $result = mysql_query($sql);
      while($row = mysql_fetch_assoc($result)){
          // Print the array members
      }
  }
?>

Is there no way, I can do all this with just one request! Thanks! Any idea please!

Answer 1

Try the following:

SELECT id, chapter_id, question, answer 
FROM (SELECT IF(@chapterId=@chapterId:=chapter_id, @id:=@id+1, @id:=0) queNo, id, chapter_id, question, answer 
      FROM `questions`, (SELECT @chapterId:=0, @id:=0) AS A 
      WHERE `chapter_id` IN (19, 20, 21, 22, 23)
      ORDER BY `chapter_id`
     ) AS A 
WHERE queNo < 10