Return random rows from the database without repeating

Question

I do a quiz where I keep questions in the table, and every time I ask one question, I need another question. I tried using rand() :

 select * from quiz order by rand() LIMIT 1 

but the lines are repeated.

Is there anything else I can use where I can get random strings, but not getting the same questions again?

Answer 1

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

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

Answer 2

You can use sessions and your survey ID.

for instance

 select * from quiz order by rand() LIMIT 1 

add the quiz id to the session quiz:

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

Answer 3

If you require the user to go through all the questions once in random order, you will need to track an individual “playlist” for each user. Preferred use of 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 randomize either in the database using ORDER BY RAND() , or in PHP using shuffle , or that's fine.

Answer 4

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