I have one table (questions) with 54 questions.

The students have to answer all 54 questions.

I have to show 10 questions at once, then next 10 and so on....

I have to select 10 questions randomly and next 10 random questions except the previous 10 questions that already answered, and so on....

I store all the answers in another table named user_answer.

what will be the MySQL Query?


You can do this by using following query.

SELECT * FROM questions WHERE question_id NOT IN (SELECT question_id FROM student_answers WHERE student_id = '5') ORDER BY RAND() LIMIT 10;

I have assumed that there is 3 tables.

  1. question - Hold all questions
  2. student_answers - hold student specific answers of each question.
  3. student - hold student informations.

I expect that student_id is the primary key of student table and foreign key of student_answer table.

The student_is is brought from session or somewhere.

  • I'd remove the DISTINCT: it serves no purpose and will only slow down the query. – Colin 't Hart Feb 12 '15 at 13:20
  • Yes. That's right. – HackerPintu Feb 12 '15 at 13:49

In pure SQL I'd probably do something like this, assuming you save the answers for each 10 answers. Wouldn't reccomend it for huge tables though, NOT IN is kind of slow.

SELECT ... FROM questions 
WHERE question_id NOT IN (SELECT question_id FROM user_answer 
    WHERE user_id = <user_id>) 

Another way would be getting all the question ID's and keeping track of them in the application.

  • 1
    NOT IN is not slow when tables are indexed properly. ORDER BY RAND() gets slower with big tables. Sorting a billion rows cannot be fast. – ypercubeᵀᴹ Feb 12 '15 at 11:17

gets you a random 10. But this is not sufficient to avoid repeating some of them for the next 10. So let's get a bit more sophisticated. Let's prime the random number generator (with a "seed") so that the sequence is repeatable, then use OFFSET to pick which 10.

If this is being handled by web pages, then the seed must be passed from one page to the next, similar to how you use to decide that it is time for the "next 10" questions.

Assuming you don't care if the sequence repeats itself for a second...

SELECT ... FROM tbl ORDER BY RAND($seed) LIMIT 10 OFFSET $offset;

Then remember $seed, but increment $offset by 10 for the next 'page'.

You, of course, need some way to stop after you run out of questions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.