CODEWITHSUNDEEP
phpmysql
Pascale Abou Abdo
Pascale Abou Abdo

Reputation: 387

Return random rows from database without repeating

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?

Upvotes: 4

Views: 5731

Answers (4)

deceze
deceze

Reputation: 522626

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.

Upvotes: 1

Havelock
Havelock

Reputation: 6986

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.

Upvotes: 0

Mihai Iorga
Mihai Iorga

Reputation: 39724

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";

Upvotes: 2

alex
alex

Reputation: 490647

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

Upvotes: 1

Related Questions