Since we need classes in which to give the tests, let's examine the table of subjects. The table structure looks like this:

CREATE TABLE subject (
  id INT NOT NULL,
  name CHAR(500),
  teacher CHAR(100)
) 

CREATE UNIQUE  INDEX idx1 ON subject (
        id,
        name,
        teacher
) 

CREATE SEQUENCE ON subject

The id number is a unique identifier for the class, while the name and teacher fields are the name of the course and the name of the teacher respectively. There is also an index of all three of the fields that speeds up queries. Finally, we define a sequence for the table. The ID numbers are generated by this sequence.

The CGI program to access and manipulate this data must to several things.

• Search for a subject in the database.

• Show the subject that is the result of a search.

• Add a new subject to the database.

• Change the values of a subject in the database.

With the power of Perl and mSQL, we can easily consolidate all of these functions into one file, subject.cgi. We can do this by separating each operation into its own function. The main portion of the program will be a switchboard of sorts that directs incoming requests to the proper function. We will describe the actions themselves later.

# Each of the different parts of the script is selected via the 
# 'action'
# parameter. If no 'action' is supplied, the default() function is
# called.
# Otherwise the appropriate function is called.
&default if not param('action');
# This trick comes from Camel 2 and approximates the 'switch'
# feature of C.
foreach[AO4] (param('action')) {
   /view/ and do { &view; last; };
   /add$/ and do { &add; last; };
   /add2/ and do { &add2; last; };
   /add3/ and do { &add3; last; };
   /add4/ and do { &add4; last; };
   /schange$/ and do { &schange; last; };
   /schange2/ and do { &schange2; last; };
   /lchange$/ and do { &lchange; last; };
   /lchange2/ and do { &lchange2; last; };
   /lchange3/ and do { &lchange3; last; };
   /delete/ and do { &delete; last; };
   &default;
}

Now all we have to do is fill in the details by implementing each function.

The default function prints out the initial form seen by the user. This is the form that allows the user to choose which action to perform. This function is called if the CGI program is accessed without any parameters, as with http://www.myserver.com/teach/subject.cgi, or if the ACTION parameter does not match any of the existing functions. An alternative method would be to create a function that prints out an error if the ACTION parameter is unknown.

sub default {
   print header, start_html('title'=>'Subjects','BGCOLOR'=>'white');
   print <<END_OF_HTML;
<h1>Subjects</h1>
<p>Select an action and a subject (if applicable).
<FORM ACTION="subject.cgi" METHOD=POST>
<p><SELECT NAME="action">
<OPTION VALUE="view">View a Subject
<OPTION value="add">Add a Subject
<OPTION value="schange">Modify a Subject
<OPTION value="lchange" SELECTED>Modify a Class List
<OPTION value="delete">Delete a Subject
</select> 
END_OF_HTML
   # See 'sub print_subjects' below.
   &print_subjects;
   print <<END_OF_HTML;
<p>
<INPUT TYPE=SUBMIT VALUE=" Perform Action "> 
<INPUT TYPE=RESET>
</form></body></html>
HTML

}

There are five main actions: "view," "add," "schange" (change the information about a subject), "lchange" (change the class list for a subject), and "delete". For illustration, we will examine the "add" action in detail here. The "add" action is broken up into four separate functions because interaction with the user is required up to four times. Hidden variables are used to pass information from form to form until the class is finally created.

The first add function generates the form used to enter the initial information about the class, including its name, the teacher's name, and the number of students in the class.

sub add {
   my (%fields);
   foreach ('name','size','teacher') {
      if (param($_)) { $fields{$_} = param($_); }
      else { $fields{$_} = ""; }
   }

   print header, start_html('title'=>'Add a Subject','BGCOLOR'=>'white');
   print <<END_OF_HTML;
<H1>Add a Subject</h1>
<form METHOD=POST ACTION="subject.cgi">
<p>
Subject Name: <input size=40 name="name" value="$fields{'name'}"><br>
Teacher's Name: <input size=40 name="teacher" value="$fields{'teacher'}"><br>
Number of Students in Class: <input size=5 name="size" value="$fields{'size'}">
<p>
<INPUT TYPE=HIDDEN NAME="action" VALUE="add2">
<INPUT TYPE=SUBMIT VALUE=" Next Page "> 
<INPUT TYPE=RESET>
</form>
<p>
<A HREF="subject.cgi">Go</a> back to the main Subject page.<br>
<A HREF=".">Go</a> to the Teacher's Aide Home Page.
</body></html>
END_OF_HTML

}

The function checks to see if any of the fields have preassigned values. This adds extra versatility to the function in that it can now be used as a template for classes with default values -- perhaps generated by another CGI program somewhere.

The values from the first part of the add process are passed back to CGI program into the add2 function. The first thing that add2 does is check whether the class already exists. If it does, an error message is sent to the user and he or she can change the name of the class.

If the class does not already exist, the function checks how many students were entered for the class. If none were entered, the class is created without any students. The students can be added later. If the number of students was specified, the class is created and a form is displayed where the user can enter the information about each student.

sub add2 {
   ...
   my $name = param('name');
   # We need one copy of the name that is encoded for the URL.
   my $enc_name = &cgi_encode($name);
   # We also need a copy of the name that is quoted safely for insertion
   # into the database. Msql provides the Msql::quote() function for that
   # purpose.
   my $query_name = $dbh->quote($name);

   # We now build a query to see if the subject entered already exists.
   my $query = 
      "select id, name, teacher from subject where name=$query_name";

   # If the user supplied a teacher's name, we check for that teacher
   # specifically, since there can be two courses with the same name but
   # different teachers.
   if (param('teacher')) {
      $teacher = param('teacher');
      $enc_teacher = &cgi_encode($teacher);
      my $query_teacher = $dbh->quote($teacher);
      $query .= " and teacher=$query_teacher";
   }

   # Now we send the query to the mSQL server.
   my $out = $dbh->query($query);
   # We check $out->numrows to see if any rows were returned. If
   # there were any, and the user didn't supply an 'override'
   # parameter, then we exit with a message that the class already
   # exists, and giving the user a change to enter the class anyway
   # (by resubmitting the form with the 'override' parameter set. 
    if ($out->numrows and not param('override')) {
      # Print 'Class already exists' page.
      ...
   } else {
      # Now we enter the information into the database.
      # First, we need to select the next number from the
      # table's sequence.
      $out = $dbh->query("select _seq from subject");
      my ($id) = $out->fetchrow;

      # Then we insert the information into the database, using
      # the sequence number we just obtained as the ID.
      $query = "INSERT INTO subject (id, name, teacher) 
         VALUES ($id, '$name', '$teacher')";
      $dbh->query($query);

      # If the user did not specify a class size, we exit with
      # a message letting the user know that he or she can add
      # students later.
      if (not param('size')) {
         # Print success page.
         ...
      } else {
         # Now we print a form, allowing the user to enter the
         # names of each of the students in the class.
         print header, start_html('title'=>'Create Class List',
            'BGCOLOR'=>'white');
         print <<END_OF_HTML;
<H1>Create Class List</h1>
<P>
<B>$name</b> has been added to the database. You can
now enter the names of the students in the class. 
You may add or drop students later from the 
<a href="subject.cgi">main
Subject page</a>.
<p>
<FORM METHOD=POST ACTION="subject.cgi">
<INPUT TYPE=HIDDEN NAME="action" VALUE="add3">
<INPUT TYPE=HIDDEN NAME="id" VALUE="$id">
<TABLE BORDER=0>
<TR><TH><TH>First Name<TH>Middle Name/Initial
<TH>Last Name<TH>Jr.,Sr.,III,etc
</tr>
END_OF_HTML
         for $i (1..$size) {
         print <<END_OF_HTML;
<TR><TD>$i<TD><INPUT SIZE=15 NAME="first$i"><TD><INPUT SIZE=15 
NAME="middle$i">
        <TD><INPUT SIZE=15 NAME="last$i"><TD><INPUT SIZE=5 
NAME="ext$i"></tr>
END_OF_HTML

         }
         print <<END_OF_HTML;
</table>
<INPUT TYPE=SUBMIT VALUE=" Submit Class List ">
<INPUT TYPE=RESET>
</form></body></html>
END_OF_HTML

      }
   }
}

When adding the class to the database, mSQL's sequence feature comes in handy. Remember that a sequence was defined on the class table. The values of this sequence are used as the unique identifiers for each class. In this way two classes can have the same name (or same teacher, etc.) and still be distinct. This also comes in handy when modifying the class later. As long as the unique ID is passed from form to form, any other information about the class can safely be changed.

Finally, notice that the student entry form displayed by this function is dynamically generated. The number of students entered for the class is used to print out a form with exactly the right number of entries. Always remember that the CGI program has complete control over the generated HTML. Any part, including the forms, can be programmatically created.

If the user did not enter any students for the class, we are now finished. The user can use the change feature to add students later. However, if students were requested, the information about those students is passed onto the stage in the add3 function, as shown in the following:

sub add3 {
   if (not param('id')) { &end("An ID number is required"); }
   my $id = param('id');

   my @list = &find_last_student;
   my ($ref_students,$ref_notstudents) = 
&find_matching_students(@list);

   @students = @$ref_students if $ref_students;
   @notstudents = @$ref_notstudents if $ref_notstudents;

   if (@notstudents) {
      # Print form telling the user that there are nonexisting 
      # students in the list. The user can then automatically create 
      # the students or go back and fix any typos.
      ...
   } else {
      &update_students($id,@students);
      # Print success page.
      ...
   }
}

The bulk of this function's work is performed by other functions. This is because other parts of the CGI program have similar needs so it is efficient to factor the common code into shared functions. The first such function is find_last_student, which examined the form data and returns a list of the form numbers -- the form numbers are not related to the ID numbers in the database -- of each student entered by the user. This is necessary because, as mentioned earlier, the previous form is dynamically generated and there is no way to immediately know how many students are included.

sub find_last_student {
   my @params = param;
   my @list = ();
   foreach (@params) {
      next if not param($_); # Skip any 'empty' fields
      if (/^(first|middle|last|ext)(\d+)/) { 
         my $num = $2;
         if (not grep(/^$num$/,@list)) { push(@list,$num); }
      }
   }
   @list = sort { $a <=> $b} @list;
   return @list;
}

Note that the function returns all of the numbers, not just the last number -- which would presumably be the number of students entered. Even though the previous form printed out the number of entries the user requested, there is no guarantee that the user filled all of them out. He or she may have missed or skipped a row, which would not be included with the form data. Therefore, it is necessary to find out each number that was entered. The output of this function is then sent to the next "helper" function: find_matching_students, as shown in the following:

sub find_matching_students {
   my @list = @_;
   my ($i,@students,@notstudents);
   @students = ();
   @notstudents = ();
   if (@list) {
      foreach $i (@list) {
         my @query = ();
         # Build a query that looks for a specific student.
         my $query = "select id, subjects from student where ";
         foreach ('first','middle','last','ext') {
            if (param("$_$i")) {
               my $temp = param("$_$i");
               # Single quotes are the field delimiters for mSQL (and MySQL),
               # so they must be preceded with the escape character "\", 
               # which is escaped itself so that it is interpreted literally.
               $temp =~ s/'/\\'/g;
               push(@query,"$_ = '$temp'");
            }
         }
         $query .= join(" and ",@query);
        
         # Send the query to the database.
         my $out = $dbh->query($query);
         # If the database doesn't return anything, add the
         # student to the @notstudents array.
         if (not $out->numrows) {
            push(@notstudents,[ param("first$i"), 
            param("middle$i"),
            param("last$i"), param("ext$i") ]);
            # Otherwise add the student to the @students array.
         } else {
            my ($id,$subjects) = $out->fetchrow;
            push(@students,[$id,$subjects]);
         }
      }
   }
   return(\@students,\@notstudents);
}

This function goes through each of the given student names and checks the database to see if they already exist. If they do exist their information is stored in an array called @students, otherwise they are put in @notstudents. The information about each student is kept in an anonymous array, creating a student object of sorts. Finally the function returns references to both of the arrays. It cannot return the data as regular arrays because there would be no way to tell where one array ended and the other began.

The final helper function is update_students, which adds the class to each existing student's list of classes.

sub update_students {
   my $id = shift;
   my @students = @_;
   foreach (@students) {
      my($sid,$subjects)=@$_;
      if (not $subjects) { $subjects = ":$id:"; }
      elsif ($subjects !~ /:$id:/) { $subjects .= "$id:"; }
      my $query = "update student set subjects='$subjects'
         where id=$id";
      $dbh->query($query);
   }
}

This function queries the student table, which is entirely separate from the subject table. Within a single CGI program, you can interact with any number of different tables within a database. You can even switch between databases, but you can only have one database selected at a time. This function retrieves the subject list for each given student and adds the new subject to their list if it is not there already.

At this point all contingencies are taken care of except for the case where the subject has students that do not already exist in the student table. For this case, the list of new students are sent to the add4 function as shown in the following:

sub add4 {
        # Get list of @students and @notstudents
        &update_students($id,@students) if @students;
        &insert_students($id,@notstudents) if @notstudents;

        # Print success page.
}

This function separates the list of students into existing and nonexisting students using the same method as add3. It then updates the existing students using update_students shown earlier. Nonexisting students, shown in the following, are sent to the new helper function insert_students:

sub insert_students {
   foreach $i (@list) {
      # This selects the next number in the sequence defined on the
      # table. We then use this number as the ID of the student.
      my $out = $dbh->query('select _seq from student');
      my($sid) = $out->fetchrow;

      # We have to quote all of the text strings for inclusion
      # in the database.
      my ($first, $middle, $last, $ext) = ( 
         $dbh->quote(param("first$i")),
         $dbh->quote(param("middle$i")), 
         $dbh->quote(param("last$i")), 
         $dbh->quote(param("ext$i"))
      );
      my $query = "insert into student (id, first, middle, last, 
         ext, subjects) VALUES ($sid, $first, $middle, 
         $last, $ext, ':$id:')";
      $dbh->query($query);
   }
}