Assignment - Perl and DBI

In this assignment, you will create a CGI script that will create a MySQL table from a file of California Certificated wrestling coaches. The name of your script will be make_coach_table.cgi. The file containing the coaches’ data, which you must use exactly as it is, is named coachlist.txt. Each line in the text file gives data for one coach. Each line consists of these tab-separated fields:

• Last name
• First name
• City (this may not have any data in it)
• Copper: this field contains the letter X if the coach has copper-level certification.
• Bronze: this field contains the letter X if the coach has bronze-level certification.
• Silver: this field contains the letter X if the coach has silver-level certification.

Some coaches may have more than one certification. This happens when a coach takes the entry-level test and gets the “copper” certification, and then later takes a higher-level test and passes it.

Part Two

You will then write a CGI script that gets its data from the form shown below. The script will search through a file of California Certificated coaches. You may try the code below to see it in action. (This script also gives debug output at the top of the results page; you do not have to.)

Certificated Coaches

Here is the HTML for the form. You want to copy and paste this into a file of your own. You don’t want to type it all yourself. You will need to change the action attribute on the opening <form> tag to reflect the URL where your CGI script resides.

Program Requirements and Hints

If all the checkboxes are empty, you must return an error telling the client to select at least one checkbox.

If the first name, last name, or city name is left empty, that is presumed to be the same as “all values”. For example, if you enter Joe in the first name field, it will find all people in all cities whose first name starts with the word Joe.

The last name, first name, and city searches are case-insensitive, and will match if they are found at the beginning of the string. Thus, searching for Ger in the first name field will match Gerald and Geraldine, but not Roger. (Hint: use LIKE "Ger%" in your search.)

The results of the checkboxes are “or”ed together. In mathematical notation, you are doing a search for:

Last name AND First name AND City AND ( copper OR silver OR bronze )

Note: that is the mathematical notation; your MySQL statement may very well look different.

Your results must be returned in the form of a table, appropriately titled. The entries must be sorted by last name, which makes your programming a lot easier, since that’s how the original file is sorted. You must also say how many matching records were found. If there are no matching entries, don’t show a table.

Put the coachlist.txt file in the same directory as your create-the-table CGI script. This will make your job much easier. Make sure that you give an error message if the data file doesn’t exist or can’t be opened!

The name of your database is the same as your username. If you are perl1nn, then your database name is also perl1nn.

To open the database from a Perl program, you would use code like this:

$dbh = DBI->connect(
   "DBI:mysql:perl1nn",  # source
   "perl1nn",            # user name
   "the3pas",            # MySQL password
   { RaiseError => 1, AutoCommit => 0 }
);

References

• SQL Syntax (long summary) - see the sections on SELECT and CREATE TABLE.
• A Short Guide to DBI