I have a question table in my database which consists the following columns

  1. question number which ranges from 1 to 150.
  2. question text
  3. text for 4 options as A,B,C,and D
  4. answer option(either a,b,c or d)

Now i want to make a quiz where there will be 15 questions from these 150 questions and each question will be randomly selected from the pool of these 150 questions and also all these 15 questions must be unique i have used the followig approach but with this approach i am getting random question but there is a possibility of 2 same questions.


$q=mysqli_query($mysqli,"select * from quiz WHERE qno = '".$n1."'");

I have also try to use DISTINCT but with this also I get two or more same questions:-


$q=mysqli_query($mysqli,"select DISTINCT qno,ques,a,b,c,d,ans from quiz where qno='".$n1."' " );

i am using php language


Preliminary: I assume this is your table:

CREATE TABLE questions
    question_number INTEGER NOT NULL PRIMARY KEY,
    question VARCHAR(255) NOT NULL, 
    answer_a VARCHAR(255) NOT NULL,
    answer_b VARCHAR(255) NOT NULL,
    answer_c VARCHAR(255) NOT NULL,
    answer_d VARCHAR(255) NOT NULL,
    right_answer ENUM ('a', 'b', 'c', 'd') NOT NULL
) ;

You can let MySQL do the hard work for you, and just execute the following SELECT statement, that will sort randomly all your table (i.e.: it is actually shuffling the rows), and just choose the top 5 rows (NOTE: I've used 5 instead of 15 for the sake of simplicity).

The key point: instead of generating a random number from PHP, let the database deal with the random numbers...

     question_number, question, answer_a, answer_b, answer_c, answer_d, right_answer
     5 ;

(Converting this to PHP code is quite straightforward)

You will get something like:

question_number | question                                             | answer_a     | answer_b     | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :----------- | :----------- | :----------------- | :---------------------- | :-----------
              2 | Which is the colour of the White HOuse?              | Black        | Green        | Blue               | White                   | d           
              3 | What's the tallest building on Earth as of 2017?     | Empire State | Burj Khalifa | Eiffel Tower       | Tokyo Skytree           | b           
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes          | No           | Depends on gravity | Depends on kind of bird | b           
              6 | Which is the third letter of the alphabet?           | a            | b            | c                  | d                       | c           
              7 | Which is the first vowel?                            | a            | e            | i                  | o                       | a           

Repeating the same query will give you a different set of questions (although some might have already be asked):

question_number | question                                             | answer_a | answer_b | answer_c           | answer_d                | right_answer
--------------: | :--------------------------------------------------- | :------- | :------- | :----------------- | :---------------------- | :-----------
              9 | How many primary colours are there (for a human)?    | 3        | 2        | 6                  | 4                       | a           
              5 | How many wheels does a (most common) car have?       | 4        | 6        | 8                  | 10                      | b           
              8 | Does 1 kg of lead weight more than 1 kg of feathers? | Yes      | No       | Depends on gravity | Depends on kind of bird | b           
              2 | Which is the colour of the White HOuse?              | Black    | Green    | Blue               | White                   | d           
              6 | Which is the third letter of the alphabet?           | a        | b        | c                  | d                       | c           

When you work with a database, it is normally a good idea to let the database give you the complete answer to your problem, instead of looping in your code.

You can see all the setup and some more executions at dbfiddle here



You are picking up the random number between 1 to 150 so there is always possibility that you might get the same number twice.

So, ideally you would need to keep list of the questions which already served and each time when you generate a random number you need to check that number in the list, if it already exists you need to skip it and again generate the random number.

You can try the following code:

$servedQuestions = array();
while(count($servedQuestions) <= 15) {
    $n1 = mt_rand(1,150);
    if (in_array($n1, $servedQuestions)) continue;
    $q = mysqli_query($mysqli,"select * from quiz WHERE qno = '".$n1."'");
    $servedQuestions[] = $n1;
  • @PankajAgrawal, don't forget to mark this as a solution. It will help others looking for solutions to similar problems. – Lennart Jul 24 '17 at 13:59
  • wait sir i recently come to know that in $q the condition is not correct it will give the random but not unique becasue in the where clause instead of using servedQuestions only $n1 is used which will again produce the same results @lennart – Pankaj Agrawal Jul 24 '17 at 16:24
  • Unless I'm misreading the code in the answer, the loop: gets a random number; check to see if it's already been used (and goes back to the start of the loop if it has); if it hasn't been used, retrieves the new question's row; and adds the new question's ID to the array of used questions. Now, $q may be overwritten each time the loop is run, but it shouldn't come back with the same row twice. – RDFozz Jul 24 '17 at 16:35
  • but the number used to get the rows is not updated it is still $n1 which should be replaced by array Elements @RDFozz – Pankaj Agrawal Jul 24 '17 at 17:46
  • $n1 is updated every run through the loop to a new random number between 1 and 150. Maybe what you're saying is that the query should be run after the loop ends, and should use the numbers in $servedQuestions as the IN clause in a modified query, select * from quiz WHERE qno IN (<IDs from $servedQuestions>). (Note that the text in the angle brackets is a placeholder for an actual, comma-separated list of numbers)? – RDFozz Jul 24 '17 at 17:59

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.