how to randomize retrieval of question from database?

Question

.i have the following code:

<?
session_start();
$host = 'localhost';
$user = 'root';
$pw = '';
$db = 'pmdb';

mysql_connect($host,$user,$pw); 
mysql_select_db($db);

$result = mysql_query("SELECT * FROM questions WHERE QuizID=1");
$num_rows = mysql_num_rows($result);
$_SESSION['totalquestions']=$num_rows;
while($row = mysql_fetch_assoc($result))
{
    $array[] = $row;
}

//Start the form
echo '<form method="post" action="result.php">';

for($i=0; $i<=($num_rows-1); $i++)
{
  //Render a question + the answer choices
  echo $array[$i]['Title']."<br />\n";
  for ($j=1;$j<=4;$j++) 
  {
    echo "<input type=\"radio\" name=\"ans$i\" value=\"$j\">".
      $array[$i]['Answer'.$j]."<br />\n";
  }
}

//End the form
echo "<input type=\"submit\" value=\"submit\" id=\"submit\">\n</form>";
?>

.the code above displays all the questions and their corresponding choices of answers which are retrieved from the database. my question is, how do you randomize the display of questions and display only 5 at a time and upon clicking a next button the next five will be displayed. Thanks in advance!

Answer 1

You can use LIMIT m,n to both limit the number of results you get and offset the results by a given amount.

Now you could do something like:

 SELECT * FROM questions WHERE QuizID=1 LIMIT $page,5;

Where you calculate the $page based on a $_GET variable. But this won't solve your randomness.

You could always seed RAND($key) by a given key that you save in your session so you could ORDER BY RAND($key) and use the above limit technique.

Probably the simplest to implement would be to get the entire result set, shuffle it and cache it. Then use a php to show only a specific chunk of the cache.

Since this is related to pagination. Let me tell you, LIMIT m,n may not be as fast as it sounds. Learn how to improve it and read more about Efficient Pagination Using MySQL