从mysql中随机记录选择

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..

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 :)

Try this one in your mysql query

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

get random questions from table by using 'order by rand()'

and to update score:

assign Qid as id to radio button. For Example : input type="radio" id="2"

on click check using ajax weather ans is correct or not and update score respectively.

Save questions to an array.

while ($row = mysqli_fetch_array($results, MYSQL_NUM)){  $questions[] = $row[0];}
shuffle($questions);shuffle($questions);shuffle($questions);
$ndx = 0;

Then grab the questions one at a time.

$question= array_slice($questions, $ndx, 1);
$ndx++;

You will not get duplicate questions.
Performance is excellent.

Line1: Creates array questions from query results (query excluded)
Line 2: Shuffles questions 3x array for randomization.

Generate Question HTML
(Untested)

echo '<div id="questionBox"><form method="POST" action="score.php" >';
  foreach ($question as $k=>$q){
    echo "<div id=\"q$k\"><fieldset><legend>$k</legend><div class=\"q\">$q[1]</div><input class=\"true\" type=\"radio\" name=\"a$q[0]\" value=\"1\"  />&ensp;True</div><br><div id=\"a$question[0]\" class=\"false\"><input class=\"false\" type=\"radio\" name=\"a$q[0]\" value=\"0\"  />&ensp;False</div><button onclick=\"next($k)\">Next</button></fieldset></div>
";
  }
echo '<div id="q' . $k . '"><input type="submit" name="Score"  Value="Score" /></div></form></div>";

JS
(Untested)

echo <<<EOT
<script type="text/javascript">
//<![CDATA[
var q = new Array;
var divs = document.getElementsByTagName("div");
for (var div=1;div<divs.length;div++){
  did = divs[div].getAttribute("id");
  if (did == null){continue;}
  divs[div].style.display='none;
  q.push(div);

} 
document.getElementById['q1'].style.display='block';

function next(a){
  q[a].style.display='none;
  q[a+1].style.display='block;

}

//]]>
</script>
EOT;