dynamic random rows from table database

Question

Consider that I have 3 question types; true or false ToF, multi-choice MC and complete.

Now I want to randomly get 10 questions true or false, 5 questions multi-choice and 2 questions complete ( these numbers are not static and it will be variables to the execute function ).

My table is something like this:

+-----+----------------------------------------------------+----------+--------+
| id  | question                                           | type     | examId |
+-----+----------------------------------------------------+----------+--------+
| 1   | true or false question 1                           | ToF      |     1  |
| 2   | true or false question 2                           | ToF      |     1  |
| 3   | true or false question 3                           | ToF      |     1  |
| 4   | true or false question 4                           | ToF      |     1  |
| 5   | true or false question 5                           | ToF      |     1  |
| 6   | true or false question 6                           | ToF      |     1  |
| 7   | true or false question 7                           | ToF      |     1  |
| 8   | true or false question 8                           | ToF      |     1  |
| 9   | true or false question 9                           | ToF      |     1  |
| 10  | true or false question 10                          | ToF      |     1  |
| 11  | true or false question 11                          | ToF      |     1  |
| 12  | true or false question 12                          | ToF      |     1  |
| 13  | multi choice question 1                            | MC       |     1  |
| 14  | multi choice question 2                            | MC       |     1  |
| 15  | multi choice question 3                            | MC       |     1  |
| 16  | multi choice question 4                            | MC       |     1  |
| 17  | multi choice question 5                            | MC       |     1  |
| 18  | multi choice question 6                            | MC       |     1  |
| 19  | multi choice question 7                            | MC       |     1  |
| 20  | multi choice question 8                            | MC       |     1  |
| 21  | complete question 1                                | complete |     1  |
| 22  | complete question 2                                | complete |     1  |
| 23  | complete question 3                                | complete |     1  |
| 24  | complete question 4                                | complete |     1  |
| 25  | complete question 5                                | complete |     1  |
| 26  | complete question 6                                | complete |     1  |
| 27  | complete question 7                                | complete |     1  |
+-----+----------------------------------------------------+----------+--------+

What I tried so far is to write query for every type like that

SELECT * FROM question WHERE type = 'ToF' ORDER BY rand() LIMIT 10
SELECT * FROM question WHERE type = 'MC' ORDER BY rand() LIMIT 5
SELECT * FROM question WHERE type = 'complete' ORDER BY rand() LIMIT 2

Can I do it with only one query?

Is it good to get all the result sorted by type and then by PHP get the random result I want?

Gordon Linoff · Accepted Answer · 2016-03-25 15:20:14Z

2

You can do this all in one query . . . even the final randomization:

(SELECT * FROM question WHERE type = 'ToF' ORDER BY rand() LIMIT 10
) UNION ALL
(SELECT * FROM question WHERE type = 'MC' ORDER BY rand() LIMIT 5
) UNION ALL
(SELECT * FROM question WHERE type = 'complete' ORDER BY rand() LIMIT 2
)
ORDER BY rand();

This is a reasonable approach -- if you have just a handful of pre-known types.

answered Mar 25, 2016 at 15:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Over a year ago

the types are static so it's the answer i want , thanks :)

Collectives™ on Stack Overflow

dynamic random rows from table database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related