Random row selection query result

[Pandolfo]

Hey everyone,

 

I have a question about a mysql query result set of randomly selected rows. I am writing an educational web app which pulls content from a table with seven hundred multiple choice questions. Each student answers 120 questions, each associated with a four digit question id. Ideally i'd like to randomly select all 120 ids at once, without duplicates. i can select the rows, but i'm having trouble accessing the selected question ids.  I know that the result is essentially an array, but i've not had any luck extracting the ids. I'm sure the answer is simple, just a bit blocked here for the answer. Any help you might be able to offer would be great. Thanks in advance!

 

PS: The basic idea for my query is below

 

SELECT DISTINCT qid FROM smqcontent ORDER BY RAND ()LIMIT 120

 

 

[ram4nd]

wrong forum, this is php codeing help not mysql help

[Pandolfo]

I apologize if you feel this was posted in the wrong forum...but if you take a moment to actually read the post you'll find that my question is about how to use php to access the mysql result set.

[Pandolfo]

Okay, so i've made some progress, but seem to have hit a wall. The query i wrote works fine when i run it against the database in phpmyadmin. It kicks out three randomly selected question ids. However, when i try to use php to extract those three codes from the array and assign them to variables echoed in html, only one code is produced. I've no idea why. Maybe i've been looking at it too long. Any help you might be able to offer would be great. Thanks in advance.

 

Here's the code:

 

<?php
$server = "localhost";
$database = "database";
$db_user = "root";
$db_pass = "";
$link = mysql_connect($server, $db_user, $db_pass) or die("Could not connect to mysql because " . mysql_error());
// select the database
mysql_select_db($database) or die("Could not select database because <br>" . mysql_error());

$smquery = mysql_query("SELECT smqcontentid from smqcontent where contentchapter=1 ORDER BY RAND() LIMIT 3");

while ($row = mysql_fetch_array($smquery)) {

    $code1 = $row[0];
    $code2 = $row[1];
    $code3 = $row[2];
}
echo $code1;
echo $code2;
echo $code3;
?>

[Pandolfo]

I think i have this issue solved. After more research and playing around with various ideas i learned two things. First, mysql_fetch_array, and most of its variants are designed to return one row only. That's why i only had one question id return when i tested the code. Second, although slower mysql_result allows easy access to the query results in a numeric array. It may not be ideal, but for now its working. Thanks for your help. Here's the code

<?php
$server = "localhost";
$database = "database";
$db_user = "root";
$db_pass = "";
$link = mysql_connect($server, $db_user, $db_pass) or die("Could not connect to mysql because " . mysql_error());
// select the database
mysql_select_db($database) or die("Could not select database because <br>" . mysql_error());
//create the query
$smquery = mysql_query("SELECT DISTINCT smqcontentid from smqcontent where contentchapter=1 ORDER BY RAND() LIMIT 3");
//test the generated results
echo mysql_result($smquery, 0); // outputs first question id
echo"<br>";
echo mysql_result($smquery, 1); // outputs second question id
echo"<br>";
echo mysql_result($smquery, 2); // outputs third question id
echo"<br>";
//Slap all three question ids together
$opcode=mysql_result($smquery,0).mysql_result($smquery,1).mysql_result($smquery,2);
//final product
echo $opcode;
?>

[kickstart]

Hi

 

From you example I would just continue using the array and looping round the rows:-

 

<?php
$server = "localhost";
$database = "database";
$db_user = "root";
$db_pass = "";
$link = mysql_connect($server, $db_user, $db_pass) or die("Could not connect to mysql because " . mysql_error());
// select the database
mysql_select_db($database) or die("Could not select database because <br>" . mysql_error());
//create the query
$smquery = mysql_query("SELECT DISTINCT smqcontentid from smqcontent where contentchapter=1 ORDER BY RAND() LIMIT 3");
//test the generated results
while ($row = mysql_fetch_array($smquery)) 
{
echo $row['smqcontentid'];'<br />';
$opcode.=$row['smqcontentid'];
}
//final product
echo $opcode;
?>

 

Bit concerned with what you intend to do with that list of question ids. Looks worrying like you intend to put a comma seperated list of question ids into a single field for the person taking the test.

 

Slightly different way of doing it, but which might be better if you want to bring the same questions back in future.

 

Set up a table containing one row per person / question (so each person has 120 rows on this table), just containing the person ID and the question ID.

 

Then do a complete select from the question table and insert into this table in one go.

 

This way you can get the list of questions for a person easily (to check answers, etc), and do so with a table join rather than being forced to loop round the comma seperated list of question and get each one in turn.

 

All the best

 

Keith

[Pandolfo]

Keith,

 

Thanks for your help. I appreciate your effort, and understand why you would be worried about that code. I have no intentions of putting a monster list of ids in a single field. That would be a nightmare. Instead, the students are given the questions in bursts of three. Three ids concatenated into a single 12 digit number for any given burst. When the time comes to load the content the 12 digit code is exploded and then called up from the question table according to the question ids.

 

The design for the web app called for the questions to be randomly selected. No two runs of the app are exactly alike. What assures consistency is the difficulty level of the question as well as the chapter of text it corresponds to. This is the third version of the app, and i had something similar to what you suggested in the second version to manage the question distribution etc. It worked well, but we found it limited us to a certain question set. The instructors wanted to be sure students weren't pooling their answers. Your code works well though and i will use it instead of what i pieced together with mysql_result.

 

Thanks again!