从mysql中随机记录选择

Question

I have a database with four fields QiD primary key, Question,answer,score. I want to get one question at a time randomly... but for the user who take the quiz the question nos should be from 1 to N where N is the total no. of questions.

when the user select the option(radio button)..it should be compared with answer field's data for that question and score should be updated.

Several tricks i tried but could not get it...pl. help me..

Answer 1

Many ways to do this, you can use RAND() function but it's not very good in term of performances :

SELECT * FROM question
ORDER BY RAND()
LIMIT 1

A faster method is to determine the random number in PHP (or other language) :

// first request
SELECT MAX(id) FROM question
// PHP part
$random = rand(0, $theMaxId);
// SQL request to get a random question
SELECT * FROM question WHERE id = $random

For a SQL version, please see : MySQL select 10 random rows from 600K rows fast

Edit (complete example with PDO):

$req = $db->query('SELECT MAX(id) as nbr FROM question');
$rep = $req->fetch();
$theMaxId = $rep['nbr'];

$random = rand(0, $theMaxId);

// SQL request to get a random question
$req = $db->prepare("SELECT * FROM question WHERE id = :id");
$req->bindParam(':id', $random, PDO::PARAM_INT);
$req->execute();

$question = $req->fetch();
// here you are :)