How to select question from mysql tables randomly ?

This is where you share YOUR scripts with others

Moderators: egami, macek, gesf

Post Reply
User avatar
freshnet
php-forum Active User
php-forum Active User
Posts: 359
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

Mon Oct 20, 2014 10:21 pm

There are a couple of ways to do it, but I think the easiest would be to retrieve random records through the SQL:

Code: Select all

SELECT question_number, text
FROM questions
ORDER BY RAND()
LIMIT 1
This retrieves one random question, so yes at that point if you refresh the page you'll get a new random question. If you want to guarantee that as the user moves through, they don't get a question repeated even if you're using multiple pages, you'll probably want to store the questions presented already in a session. For maximum efficiency, you could even use an array. Something like this:

Code: Select all

$questionsUsed = 1; //put the pk of the question into a variable
session_start();//create session
$_SESSION['questionsUsed'][] = $questionsUsed;//create session variable array and put question in it
 
//next question (could be on another page)
$questionsUsed = 2;

 $_SESSION['questionsUsed'][]=$questionsUsed; //add it to the array
 
 print_r($_SESSION['questionsUsed']);//you can see the contents here
To complete the process, you'd need to pull back the array before the next question and convert it into a where clause (using implode()) then use that as your where clause to make your query something like

Code: Select all

SELECT question_number, text
FROM questions
WHERE question_number not in (1,2)
ORDER BY RAND()
LIMIT 1


Another way would be to get all your question numbers from the database and put them into an array, then pick a random one from the array using something like:

Code: Select all

$array = [1,2,3,4,5,6,7];//put all question numbers into an array
$random = mt_rand(0,count($array)-1);//pick a random point in the array
$question = $array[$random];//store the random question

$query = "SELECT question_number, text
FROM questions
WHERE question_number = $question
ORDER BY RAND()
LIMIT 1";

unset($array[$random]);//remove the question number from the array
This second approach could still be used if you have multiple pages (you'd have to use sessions as I showed above), but if you have a lot of questions, bear in mind that you'll be creating and manipulating a large array, which will slow things down.

Post Reply