Mysql – how to select random and unique records from thesql (in php language)

MySQLPHPrandom

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.

$n1=mt_rand(1,150);

$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:-

$n1=mt_rand(1,150);

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

i am using php language

Best Answer

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...

SELECT
     question_number, question, answer_a, answer_b, answer_c, answer_d, right_answer
FROM
     questions
ORDER BY
     rand()  
LIMIT
     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

References:

Related Question