Lesson 9 -- Printer Friendly Version

INSTRUCTIONS:

To print this lesson exactly as it appears on your screen, wait a minute or two for the entire page to load. The download should not be considered complete until you see the copyright notice at the bottom of the page. Once the document is ready to print, simply click your browser's File menu and choose Print.
To save a text-only version of this lesson, click your browser's File menu and choose Save As. Select a disk drive and folder to receive the file, and change the name of the file to less9.htm To view the file while you are offline, just go to the drive and folder you selected when you saved the file and double-click the file named less9.htm. Your browser will start and you will have access to the file.
If you use the Microsoft Internet Explorer browser and the Microsoft Outlook mail program, you can e-mail a text-only version of this file to yourself. To do so, click the File menu, choose Send, and then select Page by e-mail.

IMPORTANT: This version of your lesson is for saving or printing only. All links and images have been disabled to decrease download time and help you avoid printer difficulties.

Chapter 1

In this lesson and the next, you're going to learn about building a searchable CGI database. Today, you'll learn how to build a CGI database from scratch, and how to convert an Access table to a CGI database. You'll also learn how to write a CGI program that can read the contents of your database and convert them to an HTML report, and how to create a CGI program that will allow you to add records to your database over the web.
In our next lesson, you'll learn how to write a CGI program that can search and extract records from the database.
-Database Fundamentals-
A database is simply the electronic equivalent of a filing cabinet. It can be used to store any type of information: inventory data, customer names and addresses, price lists, store locations, employee data, and more.
If you want your CGI program to be able to read and manipulate your database, the database must be formatted in a certain fashion:
-First of all, the database must be saved as a text file.
-Secondly, each record in the database must occupy its own line, and the line must end with a hard return (ENTER).
-Thirdly, each record must contain the same number of fields in the exact same order.
-And finally, each field must be separated from the next by a unique character known as a 'delimiter.' The delimiter must not appear anywhere in any of your records except between the fields.
It would not be wise to use a comma or a space or a period as a delimiter, because there is a fairly good chance you might accidentally use one of those characters inside of one of your records in addition to using it to separate the fields.
I usually use the "pipe" character (|) as a delimiter, because the chances I would be using that character elsewhere in a record are very, very slim. You make a pipe by shifting the backslash key.
Because a CGI database consists of a series of delimited fields that are stored in a text file, CGI databases are commonly known as 'delimited text files.' You can create a delimited text file from scratch, or you can export data from an Access table into a delimited text file. Today, you'll learn how to do both.
-Creating a Database-
First, let's create a delimited text file by hand. Start Notepad or other text editor and type the following document--a price list for a wine seller:
1994|Callaway|Cabernet Sauvignon|11.95
1995|BV|Cabernet Sauvignon|14.95  
1995|Gallo|Chablis|5.95  
1994|Maurice Carrie|Chablis|8.95  
1994|Mount Palomar Winery|Chablis|6.95 
1993|Callaway|Chenin Blanc|9.95 
1995|Callaway|Merlot|8.95 
1993|Cilurzo Winery|Merlot|7.95 
1995|Charles Krug|Merlot|9.95  
1994|Frog's Leap|Merlot|11.95 
1995|Gallo|Merlot|6.95  
1996|Cilurzo Winery|Sweet Zinfandel|4.95
Be sure to press the ENTER key at the end of each record (line) above.
Notice how each record consists of four fields. The first field contains the vintage (year). The second field contains the name of the winery. The third field contains the type of wine. And the last field contains the price. Each field is separated from the next by a pipe (|).
Note: When you create your own databases, you will be free to add as many fields and records as you want. There are only two rules you must always observe:
1. make sure each field is separated from the next by a pipe, and
2. make sure each record contains the same number of fields in the same order.

When you finish creating the wine database from above, click the 'File' menu and choose 'Save As.' Select a drive and folder for the file, name the file "data.txt" and click the 'Save' button.
-Creating a Database from an Access Table-
If you use Microsoft Access, please pay a visit to the Supplementary Material page. You will find illustrated, step-by-step instructions designed to help you convert an Access table to the delimited text file format your CGI programs need.

Chapter 2

Now that you have created a database, you will undoubtedly want to write some CGI programs to manipulate or modify the database.
Before we begin, I want you to understand that there are three different commands you can use to help your CGI program open a database:
-open(FILE,"data.txt")-
This command will open the file named "data.txt" in read-only mode. Your program will be able to access all the fields and records in the file, but will not be able to change anything.
-open(FILE,">>data.txt")-
This command will open the file named "data.txt" in append mode. You will be able to add new records to the end of the file, but cannot do any harm to the original records that were in the file before you opened it.
-open(FILE,">data.txt")-
This command will open the file named "data.txt" in write mode. In this mode, you will be able to replace the original records with completely new data.
-readfile.cgi-
Our first CGI program will read the database and then display its records in a table on a web page.
Start a new file in your text editor and get ready to type!
Let's start with the path to the perl interpreter. As you have learned before, this line will vary according to your hosting service's specifications:
#!/usr/local/bin/perl 
Next, let's give our program access to the subroutines in cgi- lib.pl:
require "cgi-lib.pl"; 
Now, let's run the subroutine that tells our program to print the "Content-type:text/html\n\n"; header:
print &PrintHeader; 
Let's open the file in read-only mode. The last part of this line tells our program to shut down with an error message if we forget to upload the file or it can't otherwise find the file:
open(FILE,"data.txt") || die "Can't find database\n";
Now, let's store the contents of the file in an ordinary array named @indata. Each record in our database will become an item in the array:
@indata = <FILE>;
Don't forget to close the file:
close(FILE);
Now, let's start on the web page. The following snippet of HTML code produces the first row of a table. This first row contains four columns, demarcated by <th> and </th> tags. The words 'Year,' 'Winery,' 'Wine,' and 'Price' will appear in these columns.
Note: if your e-mail program routinely strips out HTML tags, please try saving this message as a text file, or visit the class website for the complete code:
print <<PrintTag;
<html>
<head>
<title>Our catalog</title>
</head>
<body>
<table border=1>
<tr>
<th>Year</th>
<th>Winery</th>
<th>Wine</th>
<th>Price</th>
</tr>
PrintTag
Now, let's create a foreach loop to extract each record from the @indata array. Please note the curly bracket on the second line which marks the beginning of our loop:
foreach $i (@indata)
{
Each record in our database comes with a hard return (ENTER) affixed to the end. We no longer need those hard returns. Perl comes with a 'chop' function designed to remove hard return from the end of a text string. Each time we run through the foreach loop, the current record will be stored in a variable called $i. The following command will remove the hard return from the end of this variable:
chop($i);
Now that the hard return has been removed, a typical record looks like this: 1995|Gallo|Chablis|5.95
What we need to do now is split up the four fields. Perl's 'split' function will separate the fields by locating and removing the delimiter (the pipe character).
The split function usually looks something like this:
(list of variables)=split(/delimiter/,$i);
Don't type the line above--it is just a generic example. You will need to replace the words 'list of variables' with unique variable names--one for each field in the record. You will also need to replace the word 'delimiter' with the pipe character (or whatever you used as the field delimiter). To be safe, it is always a good idea to precede the delimiter with a backslash so the program does not confuse it with other special characters, such as the $ or the % sign or the @ sign.
Here is the actual implementation of the 'split' function that you will want to use in your program. The command below will split the four fields on the 'pipe' character and will assign the names $year, $winery, $wine, and $price, respectively, to the four fields in the record:
($year,$winery,$wine,$price) = split(/\|/,$i);
Now, let's add another row to our table. We'll place a different variable into each of the four columns on this row. Each time our program runs through the loop, a different record will be processed. We should wind up with one row in our table for each record in the @indata array:
print "<tr>"; 
print "<td>$year</td>"; 
print "<td>$winery</td>";
print "<td>$wine</td>";
print "<td>$price</td>";
print "</tr>\n";
Next, type the closing curly bracket to indicate the end of the loop.
} 
Finally, type the tags to close the table, the body, and the HTML document itself.
print "</table>"; 
print "</body></html>";
That's it! Here's the complete code:
#!/usr/local/bin/perl
require "cgi-lib.pl";
print &PrintHeader;
#open the database
open(FILE,"data.txt") || die "Can't find database\n";
#store database contents in an array and close file
@indata = <FILE>;
close(FILE);
#start web page, produce first row of table
print <<PrintTag;
<html>
<head>
<title>Our catalog</title>
</head>
<body>
<table border=1>
<tr>
<th>Year</th>
<th>Winery</th>
<th>Wine</th>
<th>Price</th>
</tr>
PrintTag

#use a foreach loop to process each record in the array
foreach $i (@indata)
{
#remove hard return character from each record
chop($i);
#split fields on pipe character
#assign a variable name to each of the fields
($year,$winery,$wine,$price) = split(/\|/,$i);
#add a new row to the table for each record
print "<tr>";
print "<td>$year</td>";
print "<td>$winery</td>";
print "<td>$wine</td>";
print "<td>$price</td>";
print "</tr>\n";
#close the loop
}
#close the table 
print "</table>";
#close the HTML document
print "</body></html>";
#end of program
Now, click the 'File' menu and choose 'Save As.' Save this file on the same drive and in the same directory as your 'data.txt' file, but name it "readfile.cgi"

Chapter 3

1. Start your FTP program and log into the FTP server your host has set up for you. Then, go to the directory you or your host has set aside for CGI programs. This directory is usually called cgi-bin.
2. Next, set your FTP program to transmit in ASCII (text) mode and send the readfile.cgi file to the special CGI directory. Make sure the file is named readfile.cgi and not Readfile.cgi or READFILE.cgi or readfile.cgi.txt or anything other than readfile.cgi. If the name is not correct, rename the file before you upload!
3. Once your file reaches the special CGI directory on your server, point your mouse at the file and click the left mouse button to select the file. The file will now be selected. You must now tell your FTP program to issue the chmod command. If you're using WS_FTP 95, click the right mouse button (while the file is selected). A menu should pop up. Somewhere on this menu, you will find the UNIX chmod command. Click your left mouse button on chmod.
4. After clicking the chmod command, set your file permissions as follows:
Owner - read, write, and execute privileges.
Group - read and execute privileges.
Other/World - read and execute privileges only.
5. IMPORTANT: Make sure the cgi-lib.pl file you uploaded in lesson 7 is still sitting in your CGI directory. If it is not, please upload the file and give it the same permissions as the readfile.cgi file.
6. Now, upload the data.txt file to your CGI directory. Make sure the file name for this file and all the others you upload is spelled correctly and uses lower case letters. Select the file, issue the 'chmod' command, and set the permissions as follows:
Owner - read, write, and execute privileges.
Group - read, write, and execute privileges.
Other/World - read, write, and execute privileges.

IMPORTANT IMPORTANT IMPORTANT:
IMPORTANT NOTE: Notice that I am asking you to assign write privileges to the data.txt file. In this lesson and the next, you will be creating programs that will need to write to (change) the file.
You're now ready to test your program!
Stay logged onto the Internet.
Next, start your web browser and type in the URL of your readfile.cgi program. Don't forget that this file may be stored in a directory named 'cgi-bin' so the URL may be a little longer than normal. For example, if your file is named readfile.cgi and it is located at
http://server2.hypermart.com/yourname/cgi-bin
you would want to type the following:
http://server2.hypermart.com/yourname/cgi-bin/readfile.cgi
The program should run instantly, producing a table for each record in your database. If the table does not appear, check lesson 7 for possible solutions.

Please log off the net and start your text editor again. This time, we're going to create a program that will allow us to add a record to the database simply by filling out a form on the web.
Before we can write the program, we need to create the HTML form that we'll use to add a record:
-addrec.html-
This is just a standard form. It should contain four text box input devices--one for each of the four fields in our database (year, winery, wine, and price). Notice how the 'action' parameter in the <form> tag points to a program named 'addrec.cgi' stored in your 'cgi-bin' directory. This program has yet to be created, but if you plan to give it another name or store it in a different directory, you will need to change your action parameter.
<html>  
<head>  
<title>Add Record </title>  
</head>  
<body>  
<form action="cgi-bin/addrec.cgi" method="POST"> 
<P>This form adds a new product to the database.</P> 
<P>Year: <BR>  
<input type="text" name="year"></P> 
<P>Winery:<BR>  
<input type="text" name="winery"></P> 
<P>Wine:<BR>  
<input type="text" name="wine"></P> 
<P>Price:<BR>  
<input type="text" name="price"></P> 
<input type="submit">  
</form>  
</body>  
</html> 
When you finish typing this form, click the 'File' menu, choose 'Save As' and save the file as "addrec.html" in the same drive and folder as your 'data.txt' file.
-addrec.cgi-
Now, let's write the program that will take our form data and add it to the end of our database. To do this, start a new file in your text editor:
Start with the path to perl (change this if advised by your hosting service):
#!/usr/local/bin/perl 
Next, give your program access to cgi-lib.pl:
require "cgi-lib.pl";
Run the ReadParse subroutine to store the form data in an associative array named %in:
&ReadParse;
If the person doing data entry accidentally typed a dollar sign in front of the price when entering it on our form, it would be inconsistent with the other numbers and could interfere with our ability to do math. Because the $ character is a special character in Perl, it could also cause problems with programs we write in the future.
Perl's 's' (substitute) function can be used to remove one or more characters from a string. Here's how it usually looks:
variable=~s/characters to look for/replacement characters/g;
Please do not type the line above in your program--it is just an example. The 'g' on the end tells your program to replace all occurrences of the 'characters you are searching for. If you leave off the 'g' on the end, only the first occurrence would be replaced.
You would want to replace the word 'variable' with the name of the actual variable containing the text string you want to search through.
You should also replace the words 'characters to look for' with the actual character or characters you want to remove. If the character you are looking for is a punctuation mark (like $, @, %, and so on), you should precede it with a backslash.
Finally, you will need to replace the words 'replacement characters' with the actual characters you want to use as a substitute. If you don't want to substitute anything, don't type anything between the two slashes.
Here's a command that will search the price value from the %in associative array, remove all '$' characters, and replace them with nothing.
$in{'price'} =~ s/\$//g;
Next, open the database file in append mode:
open(FILE,">>data.txt") || die "Can't find database\n";
Now, let's tell our program to add another record to the end of the database file. This new record should contain the four fields that our visitor entered in the four text boxes on the form.
You must make sure that these four fields are entered in the same standard format as the fields in all the other records. Don't forget to insert a pipe between each field, and make sure that the year field is first, the winery field is second, the wine field is third, and the price field is last.
Due to space limitations, I'm going to print this one line on two separate lines. Please remember to type this command as one line:
print FILE "$in{'year'}|$in{'winery'}|
$in{'wine'}|$in{'price'}\n";
Now, close the database file:
close(FILE);
Your program is finished. However, it might not be a bad idea to have your program print the contents of the database on a web page. That way, you'll be able to verify that the record has been added.
If you'll recall, we wrote a program called 'readfile.cgi' that will do exactly that! I'd rather not write out all that perl code again, so let's ask this program to just run the readfile.cgi program instead.
Here's a nifty way to invoke another CGI program (this is just an example--be sure to substitute the URL for YOUR readfile.cgi program after the word 'Location:')
Due to space limitations, I'm going to print this one line on two separate lines. Please remember to type this command as one line:
print "Location:http://username.hypermart.net/
cgi-bin/readfile.cgi\n\n";
That's it! Here's the complete code:
#!/usr/bin/perl
require "cgi-lib.pl";
&ReadParse;
#remove '$' character from price field
$in{'price'} =~ s/\$//g;
#open database in append mode
open(FILE,">>data.txt") || die "Can't find database\n";
#add form data to end of file
#next two lines are actually one line
print FILE "$in{'year'}|$in{'winery'}|
$in{'wine'}|$in{'price'}\n";
#close the database file
close(FILE);
#run readfile.cgi program to print database contents
#next two lines are actually one line
print "Location:http://username.hypermart.net/
cgi-bin/readfile.cgi\n\n";
#end of program
When you finish typing this file, save it as 'addrec.cgi' on the same drive and in the same folder as your data.txt and readfile.cgi files.
Use your FTP program to upload the addrec.html file to the same directory as all your other HTML files. Then, upload the addrec.cgi file to your CGI directory. Use the 'chmod' command to give this file the same permissions as your other cgi files:
Owner - read, write, and execute privileges.
Group - read and execute privileges.
Other/World - read and execute privileges only.
Then, start your browser and get the addrec.html form. Fill out the form and click the 'submit' button. The data you entered should be added to the database! If you have problems, check lesson 7 for possible explanations.

That's enough for today. In our next lesson, we will continue our exploration of CGI database programming. Be sure to get the database programs described in this lesson functioning before you start the next lesson. You will be refining these programs in lesson 10.
In our next lesson, you will learn how to:
-password-protect the database from modification;
-validate the data entered in the 'addrec.html' form;
-lock the 'data.txt' file so only one user can modify it at any one moment in time; and
-search the database file and extract records that meet the user's criteria.

Chapter 4

No quiz or assignment this time. Good luck!

IMPORTANT: This version of your lesson is for saving or printing only. All links and images have been disabled to decrease download time and help you avoid printer difficulties. Do NOT attempt to click any of the links on this page.