Pulling random data from database without repeating

[asaschool]

Hey everyone,

I wanted to see if somebody can give me some help with a SQL statement that I am using to run an exam. Here is what is going on. I have a database with 1000 sample questions in it. I break this up into 10 exams each consisting of 100 questions or into category specific exams. What I am attempting to do is have each exam go through the 100 questions randomly so that people do not get the same exact exam each time they enter it. I am having problems with my questions repeating themselves with the current statement I made. Here is the SQL statement and supporting PHP code I am using with explanations as to what I am doing in each part of the code:

 

<?

session_start();
$sesId = session_id();


// This is limiting my exams to 100 questions using a session variable.
if ($_SESSION['count'] >= 100 ) { $_SESSION['count']  = 0; header(sprintf("Location: grade.php?&ex=PO")); exit; }


require_once('../databaseConnection.php'); 

//My exams are limited to 100 by counting each time the page loads 
$_SESSION['count']++ ;

// I am attempting to eliminate random questions by using a session variable to remember each ID of the questions being displayed.
$grabSesVar = $_SESSION['grabPrepId'];

// This is what is creating my SQL statement. If this is the first question, pull any ID number. If this is any other question, other than the first, show any question other than what was already displayed,
if ($_SESSION['count'] == '1') { $sqlStatement = "SELECT * FROM exam WHERE cat = 'Category' ORDER BY RAND()"; } else { $sqlStatement = "SELECT * FROM exam WHERE cat = 'Category' $grabSesVar ORDER BY RAND()"; }

// Pull the question from the database.
mysql_select_db($database_name, $name);
$query_rsPrep = "$sqlStatement";
$rsPrep = mysql_query($query_rsPrep, $name) or die(mysql_error());
$row_rsPrep = mysql_fetch_assoc($rsPrep);
$totalRows_rsPrep = mysql_num_rows($rsPrep);

// Grab the ID number of what question is currently being displayed.
$grabPrepId = $row_rsPrep['prep_id'];

// This is building my list of what questions have already been shown. I attempted to have the list built as " AND prep_id <> '1, 2, 3, 4, etc...' " but that method was not working so I am building the list as 'AND prep_id <> '1' AND prep_id <> '2' etc.... '
$_SESSION['grabPrepId'] = $grabSesVar;
$_SESSION['grabPrepId'] .= "AND prep_id <> '$grabPrepId' ";

?>

 

 

Any help is greatly appreciated.

 

THANKS

[The Little Guy]

What I would do, is first build a list of questions, and save them into a session, kind of like this:

<?php
session_start();
$limit = 100;
// Prebuild the exam from the beginning before the first question
if(count($_SESSION['exam']) == 0){
$sql = mysql_query("SELECT exam_id FROM exam WHERE cat = 'Category' ORDER BY RAND() limit $limit");
// build the full list
$i = 1;
while($row = mysql_fetch_assoc($sql)){
	$_SESSION['exam'][$i] = (int)$row['exam_id'];
}
}
?>

 

now that all your questions have been pre-determined, you can now load the question based off a page number, and you could do it like this:

<?php
session_start();
$page = (int)$_GET['page'];
$page = $page < 1?1:$page;
$page = $page > 100?100:$page;
$question_id = $_SESSION['exam'][$i];
$sql = mysql_query("SELECT exam_id FROM exam WHERE exam_id = $question_id");
$row = mysql_fetch_assoc($sql);
echo $row['question'];
?>

 

Any questions?

[asaschool]

Thank you very much for your reply. I am not as familiar with using objects in PHP as I should be and as a result I cannot get your sample code to display the data. I am in the process of learning how to work with objects better, so I apologize if I ask you the wrong questions here. Also, I take it that both codes are working on the page. So, every time the page loads, is the code going to pull a new list of 100 questions randomly? If so, how would the questions not be repeating here. Sorry again, if I am not understand exactly what the code is doing.