counter

[jaruba]

I am creating a sign up page where you have to fill in your demographic info and choose a certain date for a class to attend from a dropdown and then save it in a database.However, there is a max capacity for the date of the class that is picked. Ex. you would have a choice of nov 1 2004, nov 2, 2004, etc....., but when the class met capacity the database would not show the date anymore. So, i would need a counter and when it met capacity, lets say 30. it would not accept anymore.I am using a mysql database with php. is there certain code for that.

 

Also, not really sure how to store the dates, should i use a separate table from the student table like a classdates table. and use a date field and an id field. Then have the class date in the student table.How would i query the database to get the dates to be populated into the dropdown box.

 

 

thanks in advance.

[algorythm]

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