Return random rows from database without repeating

Question

I am making a quiz where I stored in a table questions and each time one question is asked I want another question to show. I have tried using rand():

select * from quiz order by rand() LIMIT 1

but the rows are being repeated.

Is there anything else I can use where I can get random rows but without getting same questions again?

For how long must they not repeat? Is there a limited number of questions? Do you want your user to go through all questions in the database once before seeing a duplicate? — deceze
– deceze ♦, Commented Oct 2, 2012 at 7:06
From performance point of view is in general bad idea to use ORDER BY RAND() statement. Anyway if you want to use it, just add grouping in your query, for example GROUP BY quiz_id or some other unique identifier in order to get rid of duplicates. — Bud Damyanov
– Bud Damyanov, Commented Oct 2, 2012 at 7:06

Mihai Iorga · Accepted Answer · 2012-10-02 07:09:19Z

2

You could use sessions and your quiz id.

For example

select * from quiz order by rand() LIMIT 1

add in session quiz the quiz id:

if(!$_SESSION['quiz']) { $_SESSION['quiz'] = array(); }
$_SESSION['quiz'][] = $row['id'];

and call NOT IN:

$session = implode(", ", $_SESSION['quiz']);
$query = "select * from quiz WHRE `id` NOT IN (".$session.") order by rand() LIMIT 1";

answered Oct 2, 2012 at 7:09

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

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

Comments

alex · Accepted Answer · 2012-10-02 07:06:34Z

1

Just get the id of the previous returned row and exclude it from future queries.

select * from quiz WHERE id NOT :previous_id order by rand() LIMIT 1

answered Oct 2, 2012 at 7:06

alex

492k205 gold badges890 silver badges992 bronze badges

Comments

deceze · Accepted Answer · 2012-10-02 07:24:32Z

1

If you require the user to go through all questions once in a random order, you'll have to keep track of an individual "playlist" for each user. Preferably using sessions:

session_start();

if (!$_SESSION['ids']) {
    $_SESSION['ids'] = $db->getAllQuestionIdsFromTheDatabase();
                    // ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                    // Leaving this up to you.

    shuffle($_SESSION['ids']);

    // $_SESSION['ids'] should now look like array(42, 12, 75, ...);
}

$question = $db->getQuestionById(array_shift($_SESSION['ids']));
...

You can do the randomization either in the database using ORDER BY RAND() or in PHP using shuffle, either is fine.

answered Oct 2, 2012 at 7:24

deceze♦

525k89 gold badges806 silver badges954 bronze badges

Comments

Havelock · Accepted Answer · 2012-10-02 07:12:20Z

0

Yes, you could select all the rows (or some of them) and use shuffle() mainly because ORDER BY RAND() is really resource unfriendly and it actually re-queries each row of your table, assigns a random number ID and then delivers the results.

edited Oct 2, 2012 at 7:12

answered Oct 2, 2012 at 7:07

Havelock

7,0044 gold badges37 silver badges42 bronze badges

Collectives™ on Stack Overflow

Return random rows from database without repeating

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related