0

I am creating a project which involves getting some questions from mysql database. For instance, if I have 200 questions in my database, I want to randomly choose 20 questions in such a way that no one question will be repeated twice. That is, I want to be able to have an array of 20 different questions from the 200 I have every time the user tries to get the list of questions to answer. I will really appreciate your help.

Rivnat
  • 1,497
  • 1
  • 20
  • 34
wally
  • 58
  • 7

4 Answers4

2
   SELECT * FROM questions ORDER BY RAND() LIMIT 20;

PS^ This method not possible for very big tables

Hett
  • 3,484
  • 2
  • 34
  • 51
0

Use Google to find a function to create an array with 20 unique numbers, with a minimum and a maximum. Use this array to prepare an SQL query such as:

expression IN (value1, value2, .... value_n);

More on the SQL here.

Possible array filling function here too.

Community
  • 1
  • 1
Alex L
  • 470
  • 6
  • 15
  • 1
    This would require he knows valid row id's. If you have 200 rows, and never delete/add, then this is fine. I'd go with zerkms's answer. – Michael Ozeryansky Feb 11 '14 at 04:21
  • I believe either answer is fine! Doesn't hurt to know more than one way :) – Alex L Feb 11 '14 at 04:23
  • Your way is correct, just can cause problems in the future if the data changes. It would require the application to change, and having database specific knowledge in the application level is generally bad practice. But it does work. – Michael Ozeryansky Feb 11 '14 at 04:24
  • Also, to be honest, I've done a for loop in the application selecting single random rows for as many as I needed. Which I don't recommend, just I was being silly. – Michael Ozeryansky Feb 11 '14 at 04:27
0

If you know how many rows there are in the table, you could do use LIMIT to your advantage. With limit you specify a random offset; syntax: LIMIT offset,count. Example:

<?php
$totalRows = 200; // get his value dynamically or whatever...
$limit = 2; // num of rows to select
$rand = mt_rand(0,$totalRows-$limit-1);
$query = 'SELECT * FROM `table` LIMIT '.$rand.','.$limit;
// execute query
?>

This should be safe for big tables, however it will select adjacent rows. You could then mix up the result set via array_rand or shuffle:

<?php
// ... continued
$resultSet = $pdoStm->fetchAll();
$randResultKeys = array_rand($resultSet,$limit); // using array_rand
shuffle($resultSet); // or using shuffle
?>
zamnuts
  • 9,492
  • 3
  • 39
  • 46
0

Assuming you have contiguously number questions in your database, you just need a list of 20 random numbers. Also assuming you want the user to be able to take more than one test and get another 20 questions without duplicates then you could start with a randomised array of 200 numbers and select blocks of 20 sequentially from that set i.e.

$startQuestion=1;
$maxQuestion=200;
$numberlist= range(1,$maxQuestion);
shuffle($numberlist);

function getQuestionSet( $noOfQuestions )
{
  global $numberlist, $maxQuestion, $startQuestion; 
  $start= $startQuestion;
  if( ($startQuestion+$noOfQuestions) > $maxQuestion)
  {
    echo "shuffle...\n";
    shuffle($numberlist);
    $startQuestion=1;
  }else
    $startQuestion+= $noOfQuestions;

  return array_slice($numberlist,$start,$noOfQuestions);
}

// debug...
for($i=0; $i<42; $i++)
{
  $questionset= getQuestionSet( 20 );
  foreach( $questionset as $num )
    echo $num." ";
  echo "\n";
}

then use $questionset to retrieve your questions

TonyWilk
  • 1,447
  • 10
  • 13