10.4. MysqlPerl

NOTE

It is not possible to access a mSQL database with the Mysql.pm module, or MySQL with Msql.pm. The student.cgi program expects to find a MySQL version of the subjects table. Likewise, the subject.cgi program expects an mSQL version of the students table.

To illustrate the operation of Mysql.pm, we will examine in detail the portion of student.cgi that enables a user to change the information about a student. Just like the "add" action in the Msql.pm example was broken up into four separate functions, the "change" action here is separated into three individual functions.

The first function, change, prints out a form that allows the user to search for a student to change, as shown in the following:

sub change {
   print header, start_html('title'=>'Student Change Search',
      'BGCOLOR'=>'white');
   &print_form('search2','Search for a Student to Change',1);
   print <<END_OF_HTML;
<p>
<INPUT TYPE=HIDDEN NAME="subaction" VALUE="change2">
<INPUT TYPE=SUBMIT VALUE=" Search for Students ">
 <INPUT TYPE=SUBMIT NAME="all" VALUE=" View all Students ">
 <INPUT TYPE=RESET>
</form></body></html>
END_OF_HTML
}

The form used for searching for a student to "change" is so similar to the form used to searching for a student to "view" and the one to "add" a student that a single function, print_form, is used for all three purposes, as shown in the following:

sub print_form {
   my ($action,$message,$any) = @_;

   print <<END_OF_HTML;
<FORM METHOD=post ACTION="students.cgi">
<INPUT TYPE=HIDDEN NAME="action" VALUE="$action">
<H1>$message</h1>
END_OF_HTML
   if ($any) {
      print <<END_OF_HTML;
<p>Search for <SELECT NAME="bool">
<OPTION VALUE="or">any
<OPTION VALUE="and">all
</select> of your choices.
END_OF_HTML
   }
   print <<END_OF_HTML;
<p>
First: <INPUT NAME="first" SIZE=20>
 Middle: <INPUT NAME="middle" SIZE=10>
 Last: <INPUT NAME="last" SIZE=20>
 Jr./III/etc.: <INPUT NAME="ext" SIZE=5>
<br>
Address: <INPUT NAME="address" SIZE=40><br>
City: <INPUT NAME="city" SIZE=20> 
 State: <INPUT NAME="state" SIZE=5>
 ZIP: <INPUT NAME="zip" SIZE=10><br>
Phone: <INPUT NAME="phone" SIZE=15><br>
Age: <INPUT NAME="age" SIZE=5> Sex: <SELECT NAME="sex">
END_OF_HTML
   if ($any) {
      print <<END_OF_HTML;
<OPTION VALUE="">Doesn't Matter
END_OF_HTML
   }
   print <<END_OF_HTML;
<OPTION VALUE="1">Male
<OPTION VALUE="2">Female
</select><br>
<p>
Enrolled in:<br>
END_OF_HTML
   &print_subjects("MULTIPLE SIZE=5");

}

By using three parameters, this function customizes a form template so that it can be used for several very different purposes. Notice that this helper function calls another helper function, print_subjects. This function queries the subject table as seen in the Msql.pm example and prints out a list of all of the available subjects.

sub print_subjects {
   my $modifier = "";
   $modifier = shift if @_;
   print qq%<SELECT NAME="subjects" $modifier>\n%;
   my $out = $dbh->query("select * from subject order by name");
   while(my(%keys)=$out->fetchhash) {
      print qq%<OPTION VALUE="$keys{'id'}">$keys{'name'}\n%;
   }
   print "</select>\n";
}

The search parameters entered in this first form are then sent to the search2 function, which actually performs the search. This is actually the function written to search for a student to view. Since its function is exactly what we need, we can piggy-back off of it as long as we tell it that we want to go to the next change function, change2, after the search. That is why we have the hidden variable subaction=change2 in the form. It tells search2, as shown in the following, where to send the user next:

sub search2 {
   my $out = $dbh->query(&make_search_query);
   my $hits = $out->numrows;
   my $subaction = "view";
   $subaction = param('subaction') if param('subaction');
   print header, start_html('title'=>'Student Search Result',
      'BGCOLOR'=>'white');

   if (not $hits) {
      print <<END_OF_HTML;
<H1>No students found</h1>
<p>
No students matched your criteria.
END_OF_HTML
   } else {
      print <<END_OF_HTML;
<H1>$hits students found</h1>
<p>
<UL>
END_OF_HTML
      while(my(%fields)=$out->fetchhash) {
         print qq%<LI>
<A HREF="students.cgi?action=$subaction&id=$fields{'id'}">$fields{'first'}
 $fields{'middle'} $fields{'last'}%;
         print ", $fields{'ext'}" if $fields{'ext'};
         print "\n</a>";
      }
   }
   print <<END_OF_HTML;
</ul>
<p>
<A HREF="students.cgi?action=search">Search</a> again.
</body></html>
END_OF_HTML
}

With help from the make_search_query function, this function first searches for students that match the search term. It then displays a list of the matches from which the user can select. The ID number of the selected entry is then sent to the change2 function, as shown in the following:

sub change2 {
      my $out = $dbh->query("select * from student where id=$id");
        
      my($did,$first,$middle,$last,$ext,$subjects,$age,$sex,$address,
         $city,$state,$zip,$phone) = $out->fetchrow;

      my @subjects = split(/:/,$subjects);
      shift @subjects;
      my $name = "$first $middle $last";
      if ($ext) { $name .= ", $ext"; }

      print header, start_html('title'=>"$name",'BGCOLOR'=>'white');
      print <<END_OF_HTML;
<H1>$name</h1>
<p>
<FORM ACTION="students.cgi" METHOD=POST>
<INPUT TYPE=HIDDEN NAME="action" VALUE="change3">
<INPUT TYPE=HIDDEN NAME="id" VALUE="$id">
First: <INPUT NAME="first" VALUE="$first" SIZE=20>
Middle: <INPUT NAME="middle" VALUE="$middle" SIZE=10>
Last: <INPUT NAME="last" VALUE="$last" SIZE=20>
Jr./III/etc.: <INPUT NAME="ext" VALUE="$ext" SIZE=5>
<br>
Address: <INPUT NAME="address" VALUE="$address" SIZE=40><br>
City: <INPUT NAME="city" VALUE="$city" SIZE=20> 
State: <INPUT NAME="state" VALUE="$state" SIZE=5>
ZIP: <INPUT NAME="zip" VALUE="$zip" SIZE=10><br>
Phone: <INPUT NAME="phone" VALUE="$phone" SIZE=15><br>
Age: <INPUT NAME="age" VALUE="$age" SIZE=5> Sex: 
END_OF_HTML
      my %sexes = ( '1' => 'Male',
         '2' => 'Female'
      );
      print popup_menu('name'=>'sex',
         'values'=>['1','2'],
         'default'=>"$sex",
         'labels'=>\%sexes);
      print <<END_OF_HTML;
<p>
Enrolled in:<br>
END_OF_HTML
      my @ids = ();
      my %subjects = ();
      my $out2 = $dbh->query("select id,name from subject order by name");
      while(my($id,$subject)=$out2->fetchrow) { 
         push(@ids,$id); 
         $subjects{"$id"} = $subject;
      }
      print scrolling_list('name'=>'subjects',
         'values'=>[@ids],
         'default'=>[@subjects],
         'size'=>5,
         'multiple'=>'true',
         'labels'=>\%subjects);
      print <<END_OF_HTML;
<p>
<INPUT TYPE=SUBMIT VALUE=" Change Student ">
 <INPUT TYPE=SUBMIT NAME="delete" VALUE=" Delete Student ">
 <INPUT TYPE=RESET>
</form></body></html>
END_OF_HTML
}

The primary purpose of this function is to print out a form very similar to the one generated from print_from. However, the values of this form must have the values of the chosen student preinserted as default values. This way, the user can edit whichever fields of the student he or she wishes without changing the rest.

The output of this function is a complete set of information, just as if it were coming from an add form. The difference is that the data is for a student which already exists in the database. The change3 function accepts this data and updates the student, as shown in the following:

sub change3 {
   if (param('delete')) { &delete2($id); }
   else {
      my $query = "update student set ";
      my @query = ();
      foreach ('first', 'middle', 'last', 'ext', 'address', 'city',
         'state', 'zip', 'phone') {
         if (param($_)) { push(@query,"$_ = ". 
            $dbh->quote(param($_))); 
         }
      }
      push(@query,"age=".param('age')) if param('age');
      push(@query,"sex=".param('sex')) if param('sex');
        
      my $subjects = "':";
      $subjects .= join(":",param('subjects'));
      $subjects .= ":" unless $subjects eq "':";
      $subjects .= "'";
      push(@query,"subjects=$subjects");

      $query .= join(", ",@query) . " where id=$id";
      $dbh->query($query);

      print header, start_html('title'=>'Student Changed',
         'BGCOLOR'=>'white');
      # Print success form
      ...   
   }
}