Selecting random rows from database initially while preserving that order later

Question

I am building a quiz app, in which the student can login and can select the option of give exam. I want to show multiple choice questions to students randomly.

I stored all the questions in one table. For example, I stored 20 questions for a chemistry exam. Now I want to show 10 random questions to students. I don't want to select questions randomly every time when user refresh the page. Currently I am doing:

SELECT * from table-name order by rand() limit 5;

The problem is this query selects random rows every time the user refreshes the page. This is my code:

<?php
$query="SELECT * from question_bank order by rand() limit 10";
$result= mysqli_query($connection,$query);
confirm_query($result);
?>
<form action="result.php" name="form1" method="post" id="quiz">
<?php
while ($read_all_data = mysqli_fetch_assoc($result))
{ 
 $_SESSION['id']=1;
$id=$read_all_data['question_id'];
$a=$read_all_data['a'];
$b=$read_all_data['b'];
$c=$read_all_data['c'];
$d=$read_all_data['d'];
 echo $read_all_data['question']."</br>";
 echo "A:<input type ='radio' value ='a' name='$id' >".$read_all_data['a']."</br>";
 echo "B:<input type ='radio' value ='b' name='$id' >".$read_all_data['b']."</br>";
 echo "C:<input type ='radio' value ='c' name='$id' >".$read_all_data['c']."</br>";
 echo "D:<input type ='radio' value ='d' name='$id' >".$read_all_data['d']."</br>";
}

• Erik: How long do you want to preserve the order, minutes, hours, days, years?
• Erik: Can you just have X versions of the exam where the questions are pre-scrambled, and give each person one of pre-scrambled versions? This is basically what they did/do with paper multiple choice tests to prevent cheating.
• Paul White: Please ensure you update your question using the edit button in response to comment requests for clarification. Keeping all the information needed to answer the question in one place helps people provide good answers.

Answer 1

You'll want to hold an actual random number for each user then you can call

rand($usersrandomnumber) 

to generate your static random generator start point

the $usersrandomnumber acts as a starting seed

see the mysql documentation here: https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

it will involve you storing the user's seed in a table somewhere, or cookie /local/server storage but will get you what you're after

Answer 2

Likely you'll want to hold the answers, too. That would be a suitable place to persist the ordering.

Table Answer
 Student_id
 Question_id
 Display_sequence
 Answer_value

Once you select questions insert them to this table. Each time you show a student's quiz check this table before creating a list.