it sounds like you're looking to store at least 4 different things:
1. student info
2. class dates
3. maximum number of seats in each class date
4. number of students registered in each class date
I'd probably use something like the following tables:
CREATE TABLE classes (
class_id varchar(10) NOT NULL,
class_date datetime NOT NULL,
class_txt varchar(255) NOT NULL,
class_numseats int(11) NOT NULL,
PRIMARY KEY(class_id,class_date)
);
CREATE TABLE registration (
reg_id int(11) NOT NULL auto_increment,
reg_class varchar(10) NOT NULL,
reg_date datetime NOT NULL,
reg_stdnt int(11) NOT NULL,
PRIMARY KEY(reg_id),
UNIQUE KEY reg_class_idx(reg_class,reg_date,reg_stdnt)
);
CREATE TABLE students (
stdnt_fn varchar(30) NOT NULL,
stdnt_ln varchar(30) NOT NULL,
stdnt_email varchar(100) NOT NULL,
stdnt_id int(11) NOT NULL auto_increment,
PRIMARY KEY(stdnt_id)
);
Then, to see how many seats are used, you can loop through the classes you have, and see how many of them still have seats by comparing the number of registrations for a particular class and date/time, to the maximum number of seats available in that class at that date/time.
For example:
function db_connect() {
$result = mysql_pconnect("localhost", "test", "test");
if (!$result) {
return false;
}
if (!mysql_select_db("students")) {
return false;
}
return $result;
}
if (!( $conn = db_connect() )) {
echo "Can't connect";
} else {
// start the class dropdown
print "<select name='classes'>";
// get the list of classes
$class_query_string = "
SELECT class_id, class_txt
FROM classes
";
// loop through the list
if ($class_query = mysql_query($class_query_string)) {
while ($class_query_results = mysql_fetch_assoc($class_query)) {
$class = $class_query_results["class_id"];
$class_txt = $class_query_results["class_txt"];
// find out what the max number of seats for this class is
$max_query_string = "
SELECT class_numseats AS max_reg
FROM classes
WHERE class_id = '$class';
";
if ($max_query = mysql_query($max_query_string)) {
$max_query_results = mysql_fetch_assoc($max_query);
$max_reg = $max_query_results["max_reg"];
}
// find out how many seats are already taken
$reg_query_string = "
SELECT COUNT(*) AS cur_reg
FROM registrations
WHERE reg_class = '$class'
AND reg_date = '2004-10-08 09:00:00'
";
if ($reg_query = mysql_query($reg_query_string)) {
$reg_query_results = mysql_fetch_assoc($reg_query);
$cur_reg = $reg_query_results["cur_reg"];
}
if ( $max_reg > $cur_reg ) {
print "<option value='". $class ."'>". $class_txt ."</option>\n";
}
} // end the 'while results' loop
} // end the 'if query' loop
// close the class dropdown
print "</select>";
}
(note, this code has a bug in that if there are no seats available in any of the classes, an empty drop down is created)
Hope that helps. There's likely better ways of accomplishing the goal, but this may at least get you pointed the right way ...
jason