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
