Need a MySQL query that can filter the results

Question

Here, the chapter_id can be two or more than two. I need to query in a way that every chapter give the output of equal no. of questions.

i.e. If the total question is 50, and from 5 chapters, 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 it by quering separate queries. i.e. first check the number of chapters and loop it through an 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
      }
  }
?>

Isn't there any way, that, I can do the whole thing by a single query only! Thank you! Any Idea please!

Answer 1

Try this:

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