A
simple database example
Interacting with complex DBMS programs
A database is an
organized collection of related data. For example, a phone book is a
database of names, addresses, and phone numbers. A phone book contains all
the names and addresses of individuals and companies who have phones and
want their number to be listed, and is usually organized alphabetically.
For users, the phone book is a database. For the phone company, it is only
a hard copy of the database they have in electronic form.
Many companies and
individuals now use computerized, or electronic, databases to store
information. Such databases enable you to retrieve data very rapidly.
Computers can quickly sort through thousands of records, returning only
those that match specified criteria. It's also easy to update the data in
an electronic database. Finally, electronic databases can store large
amounts of data in a small amount of space.
Databases can bring the
same benefits to your Web site. You can store large amounts of data for
publishing on the World Wide Web in databases that Web users can access
easily. You just need to build the interface to the database once, and
your Web pages can be dynamically generated from your database. If you
take this approach, your HTML pages will always be current, with no
outdated links or information. You can store your data in a database,
where you can easily manipulate and change it. You can even use CGI
scripts to build in search capabilities that wouldn't be possible in plain
HTML pages. For example, a database of information about restaurants in
your city could contain data such as food type, average meal price, and
restaurant phone number. You could interface this database to the Web by
building a search interface, one part of which is an HTML form that lets
users enter parameters to search for, such as Mexican food. The rest of
the search interface lies in the CGI script, which takes the user's input
and searches through all the records in the database, returning only the
records of restaurants serving Mexican food.
In this chapter, you
develop a database that uses a simple text file to store data. If you read
the file-based shopping cart example in Chapter 5, you might recognize
that the shopping cart files were small databases, a separate one for each
user. Text file databases are relatively easy to use and require no
special software to implement.
After the text file
database example is a brief discussion about interfacing with more complex
database programs. (Database programs are sometimes called DBMSs, for
database management systems.) DBMSs control input to and output from a
database. The CGI script you write to interact with the text file database
is a very simple database management program. Most commercially available
database management programs--such as Oracle, Sybase, or Paradox--are
highly specialized and more difficult to interact with than a text file
database. For the sake of simplicity, the upcoming example contains only a
text file database.
A simple database example
For the database
example in this chapter, you develop a Web site listing advertisements of
cars for sale. The database consists of a single file, car.dat. This data
file will contain fields for several types of information. A field is a
single category of data within a database record--such as car make or car
model. A record is a collection of field values that make up an entry in
the database--for example, all the fields that make up a single car
advertisement. Typically, each record's fields are separated by some
delimiter, such as spaces, colons, commas, or quotes. In the phone book,
the listing
Robert McDaniel 732 Sunset Blvd 432-3232
is a single record composed of the three fields name, address, and phone
number.
In the car database,
only certain authorized users, referred to as administrators, can
manipulate records within the database--adding, modifying, and deleting
entries. All other users interact with the database by searching through
the records for certain criteria, such as the make or model of a car.
Because the car database is used very differently by administrators and
general users, it has two scripts, one for administering and one for
searching.
Administering the Database
Instead of editing the
database file with a text editor every time you want to make a change, you
will develop some Web pages and a CGI script that you or the administrator
can use to add, delete, and modify records. For this example, you will
password protect access to these administration pages so that only
authorized users can make the changes. Entering the correct password
displays the contents of the database. You can add new records by pressing
an Add push button. You can modify or delete existing records by selecting
the record and pressing the appropriate push button. You can perform all
database administration from these pages.
Because access to the
database is restricted and because almost all of the Web pages for
administering the database will contain dynamic information, you must
generate the administration Web pages from a CGI script. Your
administration script must be able to display the current contents of the
database, add new records, modify and delete existing records, and
restrict access by checking a password.
Password Protecting Access to the
Database
To password protect the
pages, you need an HTML page that requires the administrator to enter a
password. This HTML page will contain a form with a password input element
in which the administrator types the password. Listing 7.1 contains the
HTML code for the admin.html file. When displayed in a Web browser, this
file allows the administrator to enter the password for accessing the
database.
Listing 7.1: The admin.html File
<HTML>
<HEAD>
<TITLE>On-line Car Ads Administration Home Page</TITLE>
</HEAD>
<BODY>
<H1>On-line Car Ads Administration Page</H1>
This page is for administrators to add, delete and modify records in
the On-line Car Ads Database.
<P>You must be authorized to make changes to the database. Please
enter your password to access the database.
<FORM METHOD=POST ACTION="/cgi-bin/admin.pl">
<P>Password: <INPUT TYPE=password NAME="password" SIZE=1Ø>
<P><INPUT TYPE=submit NAME="submit" VALUE="Enter">
</FORM>
</BODY>
</HTML>
|
When the administrator
enters the password into the password input field and presses the Enter
push button (or simply presses the Return or Enter key on the keyboard),
the administration script is sent the password value for verification. If
the password matches, the script allows access to the database. This
password verification is accomplished with the Perl code shown in Listing
7.2, which contains the Authorize subroutine.
Listing 7.2: The Authorize Subroutine
sub Authorize {
local (%data) = @_;
if ($data{'password'} ne $password) {
print "Content-type: text/html\n\n";
print "<H1>Invalid Password</H1>";
print "You must enter a valid password to enter the Database";
} else {
&Display_Database(%data);
}
}
|
The Authorize subroutine checks the value of the password that the
administrator entered, which has been placed in the %data associative
array, and compares it to the value stored in the $password variable. The
code showing where the %data associative array and the $password variable
are assigned values is in Listing 7.13 later in this chapter. For now,
just note that the variable $password is assigned a value outside the
Authorize subroutine, and that the user-supplied data will be sent to the
Authorize subroutine as a parameter. The Authorize subroutine uses the
if...else conditional to check the value the administrator typed as the
password. If this password is not equal to the contents of the $password
variable, the Invalid Password error is sent to the user's Web browser. If
the password matches the contents of the $password variable, the current
contents of the database are displayed by calling the Display_Database
subroutine, which is developed in the next section.
Viewing the
Contents of the Database
Once the administrator
has entered the correct administration password for the database, the
administration script displays the current contents of the database.
Because the administrator must select records that need to be modified and
deleted, the administration script displays all of the database records in
a scrollable list. From this list, the user administering the database can
select records to delete or modify. There is also a push button for going
to the add page to add a record to the database.
As with the other
examples in this book, you will place most of the HTML tags for the
display database page in a template file. When the administration script
needs to display the contents of the database, this template file will be
read into an array, the contents of the database will be added in the
appropriate location, and the results will be sent to the user's Web
browser. To display the database, you need a template file that has the
scrollable list where the database records will be placed. Listing 7.3
contains the HTML code for the database.tmpl file. (If your system limits
you to a three-character extension, name the file database.tml.)
Listing 7.3: The database.tmpl File
<HTML>
<HEAD>
<TITLE>On-line Car Ads - Database Page</TITLE>
</HEAD>
<BODY>
<H1>On-line Car Ads - Database Page</H1>
Select a record and press the Delete or Modify push button, or press
the Add New Entry push button to add a new entry.
<FORM METHOD=POST ACTION="/cgi-bin/admin.pl">
<SELECT NAME="entry" SIZE=1Ø>
YYYY
</SELECT>
<P><INPUT TYPE=submit NAME="submit" VALUE="Delete"> <INPUT TYPE=submit
NAME="submit" VALUE="Modify"> <INPUT TYPE=submit NAME="submit"
VALUE="Add New Entry">
</FORM>
</BODY>
</HTML>
|
This template file contains a form with a scrollable list and three push
buttons: Delete, Modify, and Add New Entry. Notice that the contents of
the scrollable list is just the letters YYYY. This is a placeholder where
the administration script will substitute the actual records from the
database. This substitution is done with the Display_Database subroutine
in the administration script.
The Display_Database
subroutine first needs to open the database and read in all of the
records. This is done with the following three lines of Perl code:
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open database!";
@database = <DATABASE>;
close(DATABASE);
The first line opens the database file for input. The path and file name
of the database file are stored in the $database variable, which is set
outside of the Display_Database subroutine. The die statement in the first
line of Perl code causes the program to terminate and output the contents
of the string
Content-type: text/html\n\nCannot open database!
The || operator between the open and die statements is the logical or
operator. When you place this operator between the two statements, the
Perl interpreter first tries to execute the open statement. If the open is
successful, the Perl interpreter moves on to the next line of code.
However, if the file cannot be opened, the Perl interpreter executes the
die statement. This is a common way to verify whether a file is
successfully opened and to terminate the Perl program if it is not.
The second line of code
reads in the contents of the database file from the input stream
<DATABASE> and places each line in an element of the array @database.
After the records have been read in from the database file, you can close
the input stream <DATABASE> by using the close command, as in the third
line.
Besides reading in the
records from the database, you need to read in the contents of the
database.tmpl file. You do this with similar lines of Perl code:
open(TEMPLATE, "$tmpl_files{'database'}") || die "Content-type: text/html\n\nCannot open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
The only changes from the previous open statement are the name of the
file, $tmpl_files{'database'}, the name of the input stream, <TEMPLATE>,
and the name of the array to hold the contents of the file, @template.
Again, the code to assign the path and file name to the $tmpl_files{'database'}
array element is outside of the Display_Database subroutine and will be
shown in Listing 7.13, which contains the entire code for the
administration script.
With the contents of
both files read in, all you need to do is to format the records from the
database for display within the scrollable list. You can do this with the
following lines of Perl code:
foreach (@database) {
chop;
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/);
$data_string .= "<OPTION>$year | $make | $model | $contact | $id";
}
This foreach loop takes each element in the @database array and executes
the body of the loop for that element. Because each record in the database
will be in a separate element in the @database array, this code executes
the body of the loop for each record in the database. The first statement
in the body of the foreach loop is chop;. This statement removes the last
character from the current element of the @database array. Because the
records are stored in a text file database with each record on a separate
line, each line contains a new line character, \n. The chop; statement
just removes this new line character from the end of the record. For more
information on the format of records in the database, see the section
"Adding Records to the Database."
The next line in the
foreach loop is
($id, $year, $make, $model, $description, $price, $contact) = split(/::/);
This line splits the current record into separate variables for each field
of the record. When records are added to the database, each record is one
long string with two colons separating the individual fields. For example,
the record
831153195::1987::Toyota::Camry::White, Automatic, AM-FM/Cassette, moon roof,
Power Windows and Doors, Excellent Condition::5ØØØ::Sam Matlend 8Ø5-324-5343
has seven fields, a unique ID (as explained under "Adding Records to the
Database"), the year of the car, the make, the model, the description, the
asking price, and the contact information. Each field is separated by two
colons. The preceding line of Perl separates the fields into separate
variables.
Once the record has
been divided into separate fields, some of the fields are inserted into a
new string, the $data_string variable, which contains the HTML formatting
necessary to include the string in the HTML page. The line of Perl code
$data_string .= "<OPTION>$year | $make | $model | $contact | $id";
does this formatting by placing the <OPTION> HTML tag before some of the
fields of the database record. Because this same line of code is executed
for each iteration of the foreach loop, the .= operator is used instead of
the = operator. This .= operator appends the values on the right side to
the variable on the left side, formatting all of the records from the
database into one long string. For example, suppose the database contained
these two records:
831153195::1987::Toyota::Camry::White, Automatic, AM-FM/Cassette, moon roof,
Power Windows and Doors, Excellent Condition::5ØØØ::Sam Matlend 8Ø5-324-5343
831153288::1985::Honda::Accord::2dr Hatchback, Power Windows and Locks, new
paint, tires.::35ØØ::Debbie Welch 212-323-3223
After the first iteration of the loop, the $data_string variable would be
<OPTION>1987 | Toyota | Camry | Sam Matlend 8Ø5-324-5343 | 831153195
and after the second iteration of the loop, the $data_string variable
would be
<OPTION>1987 | Toyota | Camry | Sam Matlend 8Ø5-324-5343 | 831153195<OPTION>1985
| Honda | Accord | Debbie Welch 212-323-3223 | 831153288
After the records from the database have been formatted and placed in the
$data_string variable, simply substitute the placeholder YYYY in the
@template array with the contents of the $data_string variable, and send
the modified contents of the @template array back to the Web browser. You
can do this with the following lines of Perl code:
$template[9] =~ s/YYYY/$data_string/e;
print "Content-type: text/html\n\n";
print @template;
In this example, the placeholder for the contents of the database, YYYY,
is on the tenth line of database.tmpl file. When the file is read into the
@template array, the tenth line is placed in the tenth element of the
@template array. Because the index of Perl arrays begins with 0, the tenth
element is at index 9. So, the statement
$template[9] =~ s/YYYY/$data_string/e;
takes the tenth element of the @template array and replaces the
placeholder with the actual records from the database. Then the next two
lines of Perl code print the required parsed header and the contents of
the @template array, which sends the contents of the @template array back
to the Web browser.
Listing 7.4 contains
all the Perl code for the Display_Database subroutine. Besides the sub
Display_Database line, which declares the subroutine, the only lines of
code that have been added are the local statements at the beginning and
if...else conditional toward the end. The local statements
local (%data) = @_;
local (@database, @template, $data_string, $year, $make, $model,
$description, $price, $contact, $id);
local (@template);
declare the arrays and variables as local to the Display_Database
subroutine. A local variable exists only within a portion of your Perl
code, usually within a subroutine. If a variable with the same name exists
outside the subroutine, Perl considers it a different variable than the
one within the subroutine. Declaring your subroutine's variables as local
helps to keep your subroutines from overwriting values of global
variables. A global variable is one that is accessible throughout the
entire Perl program, including any subroutines in the same Perl file. In
Listing 7.4, the variable $database is a global variable.
Listing 7.4: The Display_Database Subroutine
sub Display_Database {
local (%data) = @_;
local (@database, @template, $data_string, $year, $make, $model,
$description, $price, $contact, $id);
local (@template);
open(DATABASE, "$database") || die "Content-type:
text/html\n\nCannot open database!";
@database = <DATABASE>;
close(DATABASE);
open(TEMPLATE, "$tmpl_files{'database'}") || die "Content-type:
text/html\n\nCannot open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
if (@database == Ø) {
$template[9] =~ s/YYYY//;
} else {
foreach (@database) {
chop;
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/);
$data_string .= "<OPTION>$year | $make | $model | $contact |
$id";
}
$template[9] =~ s/YYYY/$data_string/e;
}
print "Content-type: text/html\n\n";
print @template;
}
|
The if...else conditional added toward the end of Listing 7.4 places the
foreach loop and the placeholder substitution you developed earlier within
the else portion. The if statement
if (@database == Ø) {
checks whether the length of the @database array is 0, which means there
are no records in the database. If so, the placeholder for the database
records in the @template array can simply be deleted. The following line
deletes the YYYY string from the tenth element of the @template array:
$template[9] =~ s/YYYY//;
Adding
Records to the Database
One of the tasks of
administering the car database is to add new entries to the database. To
do so, the administrator needs a form in which he or she can enter values
for the various fields. For the car database, you want fields for the
year, make, and model of the car, a description, the asking price, and the
contact information of the seller. These pieces of information can all be
captured with text input fields. Listing 7.5 contains the HTML code for
add.tmpl, the template file the administration script uses to create the
form for adding records to the database.
Listing 7.5: The add.tmpl File
<HTML>
<HEAD>
<TITLE>On-line Car Ads - Adding Records Page</TITLE>
</HEAD>
<BODY>
<H1>On-line Car Ads - Adding Records Page</H1>
<FORM METHOD=POST ACTION="/cgi-bin/admin.pl">
Year: <INPUT NAME="year" SIZE=4> Make: <INPUT NAME="make" SIZE=1Ø>
Model: <INPUT NAME="model" SIZE=2Ø>
<P>
Description: <INPUT NAME="description" SIZE=43>
<P>
Price: <INPUT NAME="price" SIZE=1Ø> Contact: <INPUT NAME="contact"
SIZE=3Ø>
<P><INPUT TYPE=submit NAME="submit" VALUE="Submit"> <INPUT TYPE=reset
VALUE="Reset"> <INPUT TYPE=submit NAME="submit" VALUE="Back to
Database">
</FORM>
</BODY>
</HTML>
|
Like database.tmpl, add.tmpl is a template file. (If your system limits
you to a three-character extension, call the file add.tml.) When displayed
in a Web browser, this page has text input fields to capture all of the
necessary information as well as three push buttons, Submit, Reset, and
Back to Database. add.tmpl is a template file instead of a regular HTML
file because of the password protection. You must generate this page from
the administration script so the administrator only has to enter the
password once. Otherwise, you would need to add a password field to the
page to keep other people from adding records to the database.
To display the page for
adding records, the administration script needs a subroutine to open the
add.tmpl template and output the contents to the user's Web browser. This
subroutine, shown in Listing 7.6, is called when the administrator presses
the Add New Entry push button on the page developed in the previous
section. The Display_Add subroutine should look familiar to you because
each line of code is similar to a line of code in the Display_Database
subroutine.
Listing 7.6: The Display_Add Subroutine
sub Display_Add {
local (%data) = @_;
local (@template);
open(TEMPLATE, "$tmpl_files{'add'}") || die "Content-type:
text/html\n\nCannot open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
print "Content-type: text/html\n\n";
print @template;
}
|
You also need a
subroutine to add a new entry to the database when the administrator
presses the Submit push button on the Adding Records page. Because new
entries are added by an authorized administrator, the Add_Entry subroutine
does not need to check for values in every field. For simplicity, the
Add_Entry subroutine only checks for a value in the year field. It does
this with the if statement
if ($data{'year'} ne "") {
If you want to place checks for values in the other fields, you can check
whether the other fields are blank as well. To do so, add lines such as
$data{'make'} ne ""
If the year field is not blank, a unique identifier is assigned to the new
record. A unique identifier is used to prevent identical records from
being placed in the database. For this example, the current time is used
as the unique identifier. The line
$data{'time'} = time;
assigns the current value of time to the time element of the %data
associative array.
After the unique
identifier is assigned, you only need to open the database file and append
the new record. You can do this with these three lines of Perl code:
open(DATABASE, ">>$database") || die "Content-type: text/html\n\nCannot open
database!";
print DATABASE "$data{'time'}::$data{'year'}::$data{'make'}::$data{'model'}::$data{'description'}
::$data{'price'}::$data{'contact'}\n";
close(DATABASE);
The first line opens the database file. The $database variable is preceded
with the >> operator, which opens the file for output and appends the
output to the current contents of the file. The next statement outputs the
new record to the database. The string
"$data{'time'}::$data{'year'}::$data{'make'}::$data{'model'}::$data{'description'}
::$data{'price'}::$data{'contact'}\n";
formats the record by separating all of the fields with two colons. This
formats records as in the two examples in the previous section. Finally,
the stream to the database file is closed with the close statement in the
third line.
Listing 7.7 contains
the entire Perl code for the Add_Entry subroutine. Notice that the line
&Display_Database(%data);
is added to the end of the subroutine. This line displays the contents of
the database after every addition.
Listing 7.7: The Add_Entry Subroutine
sub Add_Entry {
local (%data) = @_;
if ($data{'year'} ne "") {
$data{'time'} = time;
open(DATABASE, ">>$database") || die "Content-type:
text/html\n\nCannot open database!";
print DATABASE "$data{'time'}::$data{'year'}::$data{'make'}::$data{'model'}::$data{'de
scription'}::$data{'price'}::$data{'contact'}\n";
close(DATABASE);
}
&Display_Database(%data);
}
|
Modifying Records in the Database
Car database
administrators also have the task of modifying records in the database. If
you enter a record incorrectly, it is usually easier to modify what you
already entered than to delete the old record and type in a new one. To
allow administrators to modify existing database records, you will create
a Modify Entry page. This page will look very similar to the Adding
Records page. Like the Adding Records page, it is generated from the
administration script. However, unlike the Adding Records page, the Modify
Entry page will contain dynamic data that must be entered by the
administration script. Listing 7.8 contains the HTML code for the
modify.tmpl (or modify.tml) file.
Listing 7.8: The modify.tmpl File
<HTML>
<HEAD>
<TITLE>On-line Car Ads - Modify Entry Page</TITLE>
</HEAD>
<BODY>
<H1>On-line Car Ads - Modify Entry Page</H1>
<FORM METHOD=POST ACTION="/cgi-bin/admin.pl">
Year: <INPUT NAME="year" VALUE="AAAA" SIZE=4>
Make: <INPUT NAME="make" VALUE="BBBB" SIZE=1Ø>
Model: <INPUT NAME="model" VALUE="CCCC" SIZE=2Ø>
<P>
Description: <INPUT NAME="description" VALUE="DDDD" SIZE=43>
<P>
Price: <INPUT NAME="price" VALUE="EEEE" SIZE=1Ø>
Contact: <INPUT NAME="contact" VALUE="FFFF" SIZE=3Ø>
<INPUT TYPE=hidden NAME="id" VALUE="GGGG">
<P><INPUT TYPE=submit NAME="submit" VALUE="Make Changes"> <INPUT
TYPE=submit NAME="submit" VALUE="Cancel">
</FORM>
</BODY>
</HTML>
|
Recall from Chapter 4 that the VALUE attribute is used to contain a
default value when used in the text input element. In Listing 7.8, the
values assigned to the VALUE attributes are to be used as placeholders for
actual data from the database. The database administrator accesses the
Modify Entry page by selecting an entry from the scrollable list on the
Database page constructed in the section "Viewing the Contents of the
Database." The Display_Modify subroutine, shown in Listing 7.9, changes
these placeholders in the modify.tmpl file to the values of the record
that is currently selected from the Database page.
Listing 7.9: The Display_Modify Subroutine
sub Display_Modify {
local(%data) = @_;
local (@database, @template, $data_string, $year, $make, $model,
$description, $price, $contact, $id, $year2, $make2, $model2,
$contact2, $id2);
local (@template);
if ($data{'entry'} eq "") {
&Display_Database(%data);
} else {
open(DATABASE, "$database") || die "Content-type:
text/html\n\nCannot open database!";
@database = <DATABASE>;
close(DATABASE);
open(TEMPLATE, "$tmpl_files{'modify'}") || die "Content-type:
text/html\n\nCannot open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
($year2, $make2, $model2, $contact2, $id2) = split(/ \| /,
$data{'entry'});
foreach (@database) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/);
last if $id eq $id2;
}
$template[7] =~ s/AAAA/$year/e;
$template[8] =~ s/BBBB/$make/e;
$template[9] =~ s/CCCC/$model/e;
$template[11] =~ s/DDDD/$description/e;
$template[13] =~ s/EEEE/$price/e;
$template[14] =~ s/FFFF/$contact/e;
$template[15] =~ s/GGGG/$id/e;
print "Content-type: text/html\n\n";
print @template;
}
}
|
The body of the Display_Modify subroutine is one large if...else
conditional that begins with the if statement
if ($data{'entry'} eq "") {
This statement checks whether a value has been selected in the scrollable
list. If it has, the $data{'entry'} array element contains the selection.
Otherwise, the $data{'entry'} element is blank. If no entry was selected
when the administrator pressed the Modify push button, the Database page
is reloaded by calling the Display_Database subroutine
&Display_Database(%data);
In the else portion of the if...else conditional, both the database file
and the modify.tmpl file are opened and the contents read into the
@database and @template arrays. Then the selected entry, which is in the $data{'entry'}
array element, is split up into the individual field elements. Remember,
each record in the database was put in a specific format for viewing in
the scrollable list. Each field of the record that was placed in the
scrollable list was separated by a space, vertical bar, and space. So, the
line
($year2, $make2, $model2, $contact2, $id2) = split(/ \| /, $data{'entry'});
splits up this string and places the values in the respective variables.
Now that the selected
entry is split up, you can use the unique identifier to locate the record
in the database. You do this by looping over each record in the database
and comparing the value of the unique identifier with the value of the
identifier taken from the selected entry. The following foreach loop
performs this search:
foreach (@database) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/);
last if $id eq $id2;
}
The line
last if $id eq $id2;
causes the loop to exit when the matching record is found.
Once the matching
record has been found, you can display the Modify Entry page by changing
the values of the placeholders and outputting the results to the Web
browser. The lines
$template[7] =~ s/AAAA/$year/e;
$template[8] =~ s/BBBB/$make/e;
$template[9] =~ s/CCCC/$model/e;
$template[11] =~ s/DDDD/$description/e;
$template[13] =~ s/EEEE/$price/e;
$template[14] =~ s/FFFF/$contact/e;
$template[15] =~ s/GGGG/$id/e;
print "Content-type: text/html\n\n";
print @template;
perform the substitutions and output the results. Each substitution line
is similar to the line used in the Display_Database subroutine. Remember,
Perl arrays are indexed starting at 0. So, each index number for the
elements of the @template array that are going to be changed is one less
that the line number containing the placeholder in Listing 7.8.
When the database
administrator presses the Make Changes push button on the Modify Entry
page, the administration script needs to modify that record in the
database. Up to this point, only the Modify Entry page has been displayed.
The record in the database has not yet been changed. Listing 7.10 contains
the Modify_Entry subroutine, which is called when the administrator
presses the Make Changes push button. In this subroutine, the database is
opened with a typical open statement and the contents of the database are
read into the @database array. Then comes the following for loop:
for ($i=0; $i<@database; $i++) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/, $database[$i]);
if ($id eq $data{'id'}) {
$data_string = "$data{'id'}::$data{'year'}::$data{'make'}::$data{'model'}::$data{'description'}::
$data{'price'}::$data{'contact'}\n";
splice(@database, $i, 1, $data_string);
last;
}
}
This code loops over each element of the @database array until the
matching record is found. A for loop is used instead of a foreach loop, as
in the Display_Modify subroutine, because the loop needs to keep track of
the index of the current element so the new data can be placed at the
correct array index of the @database array. The first line of the for loop
divides the current database record into its respective fields. Then the
unique identifier of the current database record is compared with the
unique identifier of the record being modified. If the identifiers do not
match, the index variable $i is incremented and the next record in the
database is checked. If there is a match, the new data sent from the
Modify Entry page is formatted into the $data_string variable, which is
then substituted into the @database array for the old record with the
statement
splice(@database, $i, 1, $data_string);
This statement inserts the contents of the $data_string variable into the
@database array at index $i, which is the index where the unique
identifiers matched. The digit 1 means replace the current value in the
@database array at index $i with the contents of the $data_string. After
this substitution is made, the last command causes the loop to exit.
With the substitution
into the @database array complete, the new array can be written to the
database file with these lines of Perl code:
open(DATABASE, ">$database") || die "Content-type: text/html\n\nCannot open
database!";
print DATABASE @database;
close(DATABASE);
Notice how the operator > precedes the $database variable in the open
statement. This operator specifies to open the file for output,
overwriting the file if it already exists. Because the @database array
contains all of the current contents of the database, including the
modified record, you can just overwrite the file with the contents of the
@database array. Finally, the Display_Database subroutine is called,
displaying the modified contents of the database.
Listing 7.10: The Modify_Entry Subroutine
sub Modify_Entry {
local(%data) = @_;
local (@database, @template, $data_string, $year, $make, $model,
$description, $price, $contact, $id, $year2, $make2, $model2,
$contact2, $id2);
local (@template);
open(DATABASE, "$database") || die "Content-type:
text/html\n\nCannot open database!";
@database = <DATABASE>;
close(DATABASE);
for ($i=Ø; $i<@database; $i++) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/, $database[$i]);
if ($id eq $data{'id'}) {
$data_string = "$data{'id'}::$data{'year'}::$data{'make'}::$data{'model'}::$data{'desc
ription'}::$data{'price'}::$data{'contact'}\n";
splice(@database, $i, 1, $data_string);
last;
}
}
open(DATABASE, ">$database") || die "Content-type:
text/html\n\nCannot open database!";
print DATABASE @database;
close(DATABASE);
&Display_Database(%data);
}
|
Deleting Records in the Database
The final task for the
administration script is to allow the administrator to delete records. You
can easily do this with a Delete_Entry subroutine, which is shown in
Listing 7.11. This subroutine is very similar to the Modify_Entry
subroutine. First, you only delete a record from the database if the
administrator selects a record and presses the Delete push button. As in
the Modify_Entry subroutine, if the user presses the Delete push button
without selecting a record, the Display_Database subroutine is called. No
records are deleted. If the administrator did select an entry and press
the Delete push button, the record is deleted from the database file.
Listing 7.11: The Delete_Entry Subroutine
sub Delete_Entry {
local(%data) = @_;
local (@database, @template, $year, $make, $model,
$description, $price, $contact, $id, $year2, $make2, $model2,
$contact2, $id2);
local (@template);
if ($data{'entry'} eq "") {
&Display_Database(%data);
} else {
open(DATABASE, "$database") || die "Content-type:
text/html\n\nCannot open database!";
@database = <DATABASE>;
close(DATABASE);
($year2, $make2, $model2, $contact2, $id2) = split(/ \| /,
$data{'entry'});
for ($i=Ø; $i<@database; $i++) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/, $database[$i]);
if ($id eq $id2) {
splice(@database, $i, 1);
last;
}
}
open(DATABASE, ">$database") || die "Content-type:
text/html\n\nCannot open database!";
print DATABASE @database;
close(DATABASE);
&Display_Database(%data);
}
}
|
First the database file must be opened and the contents placed in the
@database array with the following lines of Perl code.
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
Next, the unique identifier is extracted from the entry the administrator
selected in the statement
($year2, $make2, $model2, $contact2, $id2) = split(/ \| /,
$data{'entry'});
which is identical to the split statement in the Display_Modify
subroutine. Then a for loop--similar to the for loop in the Modify_Entry
subroutine--loops over the @database array until the matching record is
found for the entry the administrator selected.
for ($i=Ø; $i<@database; $i++) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/, $database[$i]);
if ($id eq $id2) {
splice(@database, $i, 1);
last;
}
}
Once the matching record is found in the @database array, that element is
removed from the array. The preceding splice statement removes the element
at index $i from the @database array.
With the element
removed from the @database array, the array once more contains the current
contents of the database. So, as in the Modify_Entry subroutine, the
database file is opened for output and the contents of the @database array
overwrites the previous contents of the file. This is done with the three
lines
open(DATABASE, ">$database") || die "Content-type: text/html\n\nCannot open database!";
print DATABASE @database;
close(DATABASE);
Finally, the Display_Database subroutine that displays the contents of the
database file is called; it will no longer have the entry the
administrator selected for deletion. Listing 7.11 contains all the Perl
code for the Delete_Entry subroutine.
Putting Together the Administration
Script
In the previous
sections, you developed all of the subroutines for displaying the Web
pages for the database and adding, modifying, and deleting database
records. To finish the administration script, you need to set the values
of your global variables, create a subroutine that calls the appropriate
subroutine you developed previously, and place all of the code in a file
called admin.pl.
Setting the values of
the global variables is relatively easy. The code for doing so is shown is
Listing 7.13. Developing the subroutine to call the appropriate subroutine
you have already developed should be relatively easy as well. All of your
subroutines perform tasks that are the result of forms being submitted by
the administrator. Each of the forms is submitted with the POST method. So
you just need a subroutine that identifies which form is being submitted.
The following lines of Perl check the REQUEST_METHOD environment variable:
if ($ENV{'REQUEST_METHOD'} eq "POST") {
&Which_Post(%data_received);
} else {
print "Content-type: text/html\n\nYou are not using this script correctly!";
}
If the variable equals POST, the Which_Post subroutine is called.
Otherwise, the string
You are not using this script correctly!
is displayed in the user's Web browser.
In the HTML for the
Administration, Database, Adding Records, and Modify Entry pages, every
push button has a different value for the VALUE attribute. When a form is
submitted, the name/value pair submit=X is sent to your administration
script, where X is the string assigned to the VALUE attribute of the push
button that was pressed. For example, if the Delete push button is
pressed, the name/value pair is submit=Delete. So, the Which_Post
subroutine only needs to check the values of the $data{'submit'} array
element, which is where the user's data is placed, and then call the
related subroutine. Listing 7.12 contains the Perl code for the Which_Post
subroutine.
Listing 7.12: The Which_Post Subroutine
sub Which_Post {
local (%data) = @_;
&Authorize(%data) if ($data{'submit'} eq "" || $data{'submit'} eq
"Enter");
&Display_Add(%data) if $data{'submit'} eq "Add New Entry";
&Display_Database(%data) if ($data{'submit'} eq "Back to Database"
|| $data{'submit'} eq "Cancel");
&Add_Entry(%data) if $data{'submit'} eq "Submit";
&Display_Modify(%data) if $data{'submit'} eq "Modify";
&Modify_Entry(%data) if $data{'submit'} eq "Make Changes";
&Delete_Entry(%data) if $data{'submit'} eq "Delete";
}
|
Notice that the Authorize subroutine can be called if the value for $data{'submit'}
is blank or Enter. Recall that the Authorize subroutine verifies the
password being entered on the Administration Home page. Because this page
has only a single text input field and the Enter push button, the user can
simply enter a value in the text field and press the Return or Enter key
on the keyboard to submit the form. When the form is submitted in this
fashion--which only works when there is just a single text input field and
at most a single submit push button, no other input fields, scrollable or
drop-down lists, or text areas--the $data{'submit'} array element will be
empty.
Listing 7.13 contains
the complete Perl code for the admin.pl script. Be sure to change the
value for the path global variable to the correct path for your machine.
Also notice that the current password is set to the string mypassword. You
can change this string to whatever you want the current password to be. If
you will be using the script on a Windows machine, you should remove the
first line.
Listing 7.13: The admin.pl File
#!/usr/local/bin/perl
# All users should change the value of the $path
# variable to the correct value for their machine.
# Windows users need a value in the form
# $path = "c:\\robertm\\";
$path = "/users/robertm/";
%tmpl_files =
( 'add' , $path . 'add.tmpl',
'database', $path . 'database.tmpl',
'modify', $path. 'modify.tmpl', );
$database = $path . "car.dat";
# Change this string if you want to change the password.
$password = "mypassword";
%data_received = &User_Data;
&No_SSI(*data_received);
if ($ENV{'REQUEST_METHOD'} eq "POST") {
&Which_Post(%data_received);
} else {
print "Content-type: text/html\n\nYou are not using this script correctly!";
}
sub Which_Post {
local (%data) = @_;
&Authorize(%data) if ($data{'submit'} eq "" || $data{'submit'} eq "Enter");
&Display_Add(%data) if $data{'submit'} eq "Add New Entry";
&Display_Database(%data) if ($data{'submit'} eq "Back to Database" ||
$data{'submit'} eq "Cancel");
&Add_Entry(%data) if $data{'submit'} eq "Submit";
&Display_Modify(%data) if $data{'submit'} eq "Modify";
&Modify_Entry(%data) if $data{'submit'} eq "Make Changes";
&Delete_Entry(%data) if $data{'submit'} eq "Delete";
}
sub Authorize {
local (%data) = @_;
if ($data{'password'} ne $password) {
print "Content-type: text/html\n\n";
print "<H1>Invalid Password</H1>";
print "You must enter a valid password to enter the Database";
} else {
&Display_Database(%data);
}
}
sub Display_Add {
local (%data) = @_;
local (@template);
open(TEMPLATE, "$tmpl_files{'add'}") || die "Content-type: text/html\n\nCannot
open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
print "Content-type: text/html\n\n";
print @template;
}
sub Add_Entry {
local (%data) = @_;
if ($data{'year'} ne "") {
$data{'time'} = time;
open(DATABASE, ">>$database") || die "Content-type: text/html\n\nCannot open
database!";
print DATABASE
"$data{'time'}::$data{'year'}::$data{'make'}::$data{'model'}::$data{'description'}
::$data{'price'}::$data{'contact'}\n";
close(DATABASE);
}
&Display_Database(%data);
}
sub Display_Database {
local (%data) = @_;
local (@database, @template, $data_string, $year, $make, $model,
$description, $price, $contact, $id);
local (@template);
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
open(TEMPLATE, "$tmpl_files{'database'}") || die "Content-type:
text/html\n\nCannot open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
if (@database == Ø) {
$template[9] =~ s/YYYY//;
} else {
foreach (@database) {
chop;
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/);
$data_string .= "<OPTION>$year | $make | $model | $contact | $id";
}
$template[9] =~ s/YYYY/$data_string/e;
}
print "Content-type: text/html\n\n";
print @template;
}
sub Display_Modify {
local(%data) = @_;
local (@database, @template, $data_string, $year, $make, $model,
$description, $price, $contact, $id, $year2, $make2, $model2,
$contact2, $id2);
local (@template);
if ($data{'entry'} eq "") {
&Display_Database(%data);
} else {
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
open(TEMPLATE, "$tmpl_files{'modify'}") || die "Content-type:
text/html\n\nCannot open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
($year2, $make2, $model2, $contact2, $id2) = split(/ \| /, $data{'entry'});
foreach (@database) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/);
last if $id eq $id2;
}
$template[7] =~ s/AAAA/$year/e;
$template[8] =~ s/BBBB/$make/e;
$template[9] =~ s/CCCC/$model/e;
$template[11] =~ s/DDDD/$description/e;
$template[13] =~ s/EEEE/$price/e;
$template[14] =~ s/FFFF/$contact/e;
$template[15] =~ s/GGGG/$id/e;
print "Content-type: text/html\n\n";
print @template;
}
}
sub Modify_Entry {
local(%data) = @_;
local (@database, @template, $data_string, $year, $make, $model,
$description, $price, $contact, $id, $year2, $make2, $model2,
$contact2, $id2);
local (@template);
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
for ($i=Ø; $i<@database; $i++) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/, $database[$i]);
if ($id eq $data{'id'}) {
$data_string =
"$data{'id'}::$data{'year'}::$data{'make'}::$data{'model'}::$data{'description'}::
$data{'price'}::$data{'contact'}\n";
splice(@database, $i, 1, $data_string);
last;
}
}
open(DATABASE, ">$database") || die "Content-type: text/html\n\nCannot open
database!";
print DATABASE @database;
close(DATABASE);
&Display_Database(%data);
}
sub Delete_Entry {
local(%data) = @_;
local (@database, @template, $year, $make, $model,
$description, $price, $contact, $id, $year2, $make2, $model2,
$contact2, $id2);
local (@template);
if ($data{'entry'} eq "") {
&Display_Database(%data);
} else {
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
($year2, $make2, $model2, $contact2, $id2) = split(/ \| /, $data{'entry'});
for ($i=Ø; $i<@database; $i++) {
($id, $year, $make, $model, $description, $price, $contact) =
split(/::/, $database[$i]);
if ($id eq $id2) {
splice(@database, $i, 1);
last;
}
}
open(DATABASE, ">$database") || die "Content-type: text/html\n\nCannot open
database!";
print DATABASE @database;
close(DATABASE);
&Display_Database(%data);
}
}
sub No_SSI {
local (*data) = @_;
foreach $key (sort keys(%data)) {
$data{$key} =~ s/<!--(.|\n)*-->//g;
}
}
sub User_Data {
local (%user_data, $user_string, $name_value_pair,
@name_value_pairs, $name, $value);
# If the data was sent via POST, then it is available
# from standard input. Otherwise, the data is in the
# QUERY_STRING environment variable.
if ($ENV{'REQUEST_METHOD'} eq "POST") {
read(STDIN,$user_string,$ENV{'CONTENT_LENGTH'});
} else {
$user_string = $ENV{'QUERY_STRING'};
}
# This line changes the + signs to spaces.
$user_string =~ s/\+/ /g;
# This line places each name/value pair as a separate
# element in the name_value_pairs array.
@name_value_pairs = split(/&/, $user_string);
# This code loops over each element in the name_value_pairs
# array, splits it on the = sign, and places the value
# into the user_data associative array with the name as the
# key.
foreach $name_value_pair (@name_value_pairs) {
($name, $value) = split(/=/, $name_value_pair);
# These two lines decode the values from any URL
# hexadecimal encoding. The first section searches for a
# hexadecimal number and the second part converts the
# hex number to decimal and returns the character
# equivalent.
$name =~
s/%([a-fA-FØ-9][a-fA-FØ-9])/pack("C",hex($1))/ge;
$value =~
s/%([a-fA-FØ-9][a-fA-FØ-9])/pack("C",hex($1))/ge;
# If the name/value pair has already been given a value,
# as in the case of multiple items being selected, then
# separate the items with a " : ".
if (defined($user_data{$name})) {
$user_data{$name} .= " : " . $value;
} else {
$user_data{$name} = $value;
}
}
return %user_data;
}
|
Searching
through the Database
In the previous
sections, you developed the administration script that allows users with
the password to make changes to the database file. In this section, you
will develop the interface that lets any user view the records in your
database. Because this example deals with a database of car
advertisements, the best interface for other users is a search form. In
this form, users enter parameters they want in a car, and the search
script searches the database and displays any records that match the
specified criteria.
The Search
Form
Because all users have
access to the Search feature, you can create the search form in a regular
HTML file. You don't need to create a template file and have the script
display the page. Listing 7.14 contains the HTML code for the Search page.
Listing 7.14: The search.html File
<HTML>
<HEAD>
<TITLE>On-line Car Ads - Search Page</TITLE>
</HEAD>
<BODY>
<H1>On-line Car Ads - Search Page</H1>
To search for a car, enter what you are looking for below. You do not
need to enter values for all fields, but you must enter a value for
the Make field. If you enter years, please use a four digit number,
such as 1989.
<FORM METHOD=POST ACTION="/cgi-bin/search.pl">
<P><B>Make:</B> <INPUT NAME="make" SIZE=1Ø> <B>Model:</B> <INPUT
NAME="model" SIZE=2Ø>
<P><B>Years</B> <INPUT NAME="lowyear" SIZE=4> <B>to</B> <INPUT
NAME="highyear" SIZE=4>
<P><B>Priced from</B> <INPUT NAME="lowprice" SIZE=7> <B>to</B> <INPUT
NAME="highprice" SIZE=7>
<P><INPUT TYPE=submit VALUE="Search"> <INPUT TYPE=reset VALUE="Reset">
</FORM>
</BODY>
</HTML>
|
The search form
contains text input fields for the make and model of car the user is
looking for. There are two text input fields for both the year and price
so that the user can specify a range to search for. Notice that the user
must enter a value for the make field, and that year values must be
specified as four-digit numbers. When the user enters data into the form
and presses the Search push button, the form is submitted and the
search.pl script is called.
The search script loops
over every record in the database, returning any records that match the
search parameters. So, the search script first needs to open the database
file and read the contents into the @database array. It does this with the
following lines of Perl code:
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
With the contents of the database file in the @database array, the search
script can loop over all the elements of the @database array and compare
the values of the current record with the values entered by the user in
the search form. To loop over the array, you can use a foreach loop such
as
foreach (@database) {
Inside the body of the foreach loop, the current record from the @database
array can be split using the statement
($id, $year, $make, $model, $description, $price, $contact) = split(/::/);
which is similar to split statements used in the Modify_Entry subroutine
of the administration script.
Now that the record is
divided into separate fields, each field can be compared to the values
entered by the user in the search form. Because the make field is the only
value the user must enter, check this value first. Using the if statement,
if (($make =~ /\b$data{'make'}/i) || ($data{'make'} =~ /\b$make/i))
compare the value from the database, $make, with the value the user
entered for the make of the car, $data{'make'}. The Perl expression
$make =~ /\b$data{'make'}/i
evaluates to true if the beginning of the $make variable contains the
contents of the $data{'make'} variable, ignoring case. For example, if
$make were equal to Honda, the expression would be true if $data{'make'}
were Honda, Hon, Hond, or honda, but would be false if $data{'make'} were
Hondas. To make the search more flexible--so it will find the make Honda
if the user types Hondas--the preceding if statement contains the or
operator || and the expression
$data{'make'} =~ /\b$make/i
This is a Perl regular expression that evaluates to true if the beginning
of the $data{'make'} variable contains the contents of the $make variable,
ignoring case. This way, the user can enter a wide variety of strings
resembling Honda and get all Hondas in the database. The expressions also
ensure that the strings will match only if the user's entry and the field
from the current record head match and do not tail match. Head matching is
matching up the first part of the word; in contrast, tail matching matches
up the last parts of the word. For example, both Ford and Clifford tail
match (ignoring case), but do not head match, whereas toy and Toyota head
match.
The model value the
user enters for a search parameter can be compared against the model field
of the current record with similar statements. To check to see if the
model the user entered matches the model in the record, use
if (($model =~ /\b$data{'model'}/i) || ($data{'model'} =~ /\b$model/i)
|| $data{'model'} eq "")
This line adds one more or operator (||) and another conditional
expression than was in the make example. The expression
$data{'model'} eq ""
evaluates to true if the user left the model field blank, which is
allowed. So, the model will match if either of the regular expressions
returns true, or if the $data{'model'} element is blank.
The year and price
matching are different than the matching used for the make and model.
Because the values for these fields should be numbers, the comparison is
whether the user's value is greater than or less than the value from the
current record. For example, with the year value, the if statement would
be
if ( (($data{'lowyear'} <= $year) && ($data{'highyear'} >= $year)) ||
(($data{'lowyear'} eq "") && ($data{'highyear'} eq "")) )
which checks whether the price of the current record is greater than or
equal to the user's low price value and less than or equal to the user's
high price value. The statement is also true if both the low price field
and the high price field are blank. The price comparison is identical to
the earlier statement for the year comparisons.
if ( (($data{'lowprice'} <= $price) && ($data{'highprice'} >= $price))
|| (($data{'lowprice'} eq "") && ($data{'highprice'} eq "")) )
The current record in the database matches the user's search criteria if
all of these if statements are true. So, the if statements will be nested
one inside the other with the if statement for the $make being on top, as
in
if (($make =~ /\b$data{'make'}/i) || ($data{'make'} =~ /\b$make/i)) {
# make matched, check model
if (($model =~ /\b$data{'model'}/i) || ($data{'model'} =~ /\b$model/i)
|| $data{'model'} eq "") {
# model matched, check year
if ( (($data{'lowyear'} <= $year) && ($data{'highyear'} >= $year)) ||
(($data{'lowyear'} eq "") && ($data{'highyear'} eq "")) ) {
# year is in range, check price
if ( (($data{'lowprice'} <= $price) && ($data{'highprice'} >= $price))
|| (($data{'lowprice'} eq "") && ($data{'highprice'} eq "")) ) {
# price matched, add entry to user's list of matches
$matched .= "<HR>$year $make $model<BR>$description<BR>Asking
\$$price<BR>Contact: $contact\n";
$count++;
}
}
}
}
The two lines
$matched .= "<HR>$year $make $model<BR>$description<BR>Asking
\$$price<BR>Contact: $contact\n";
$count++;
are only executed if all the search parameters match the current record.
When the parameters do match the current record, the values from the
current record are appended to the $matched variable. Appending them to
the variable enables you to output the contents of the variable as the
search results, as described in the next section, "Displaying the
Results." The second line increments the $count variable, which is used to
store the total number of matching records from the database. Listing 7.15
contains the code you have so far for the Search subroutine.
Listing 7.15: The First Part of the Search Subroutine
sub Search {
local (%data) = @_;
local ($count) = Ø;
local (@database, $id, $year, $make, $model, $description, $price, $contact,
@template, $matches, $search);
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
foreach (@database) {
($id, $year, $make, $model, $description, $price, $contact) = split(/::/);
if (($make =~ /\b$data{'make'}/i) || ($data{'make'} =~ /\b$make/i)) {
# make matched, check model
if (($model =~ /\b$data{'model'}/i) || ($data{'model'} =~ /\b$model/i)
|| $data{'model'} eq "") {
# model matched, check year
if ( (($data{'lowyear'} <= $year) && ($data{'highyear'} >= $year)) ||
(($data{'lowyear'} eq "") && ($data{'highyear'} eq "")) ) {
# year is in range, check price
if ( (($data{'lowprice'} <= $price) && ($data{'highprice'} >= $price))
|| (($data{'lowprice'} eq "") && ($data{'highprice'} eq "")) ) {
# price matched, add entry to user's list of matches
$matched .= "<HR>$year $make $model<BR>$description<BR>Asking
\$$price<BR>Contact: $contact\n";
$count++;
}
}
}
}
}
}
|
Displaying the Results
With the searching
done, your search script just needs to output the results of the search to
the user's Web browser. The basic format for the Results page will be
stored in a template file results.tmpl (or results.tml). Listing 7.16
contains the HTML code for the results.tmpl file.
Listing 7.16: The results.tmpl File
<HTML>
<HEAD>
<TITLE>On-line Car Ads - Results Page</TITLE>
</HEAD>
<BODY>
<H1>On-line Car Ads - Results Page</H1>
XXXX
YYYY
<HR>
</BODY>
</HTML>
|
Notice the two placeholders in Listing 7.16, XXXX and YYYY. The first
placeholder will be changed to a string indicating how many records in the
database matched the search and the second placeholder will be replaced
with the actual results from the search.
In the previous
section, you developed the code for comparing the user's parameters with
all of the records in the database. Whenever a match occurred, the code in
Listing 7.15 appended the fields from the record to the $matched variable
and incremented the number of matches stored in the $count variable. To
output the results, your Search subroutine just needs some lines of code
to read in the template, replace the placeholders with the number of
results and the actual results, and output the @template array.
Instead of just
outputting the value of the count variable, create another variable that
specifies how many entries matched the search, such as
$search = "$count entries matched your search for <B>$data{'year'} $data{'make'}
$data{'model'}</B>.\n";
This line creates a heading that tells the user how many records matched
their search parameters. Then, open the template file and read in the
contents to the @template array with
open(TEMPLATE, "$tmpl_file") || die "Content-type: text/html\n\nCannot
open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
With the contents of the template file in the @template array, substitute
the real data for the placeholders and output the results to the user's
Web browser. The lines
$template[6] =~ s/XXXX/$search/e;
$template[7] =~ s/YYYY/$matched/e;
perform the substitutions. Keep in mind that the seventh line in Listing
7.15 will correspond to the sixth element in the @template array because
the indexing begins at 0. Also, if no records matched the search, the
$matched variable will be blank and the second substitution will just
remove the YYYY placeholder from the @template array. Finally, with the
@template array containing the correct information, the array can be
returned to the user's Web browser with the following two lines
print "Content-type: text/html\n\n";
print @template;
Listing 7.17 contains the complete Search subroutine.
Listing 7.17: The Complete Search Subroutine
sub Search {
local (%data) = @_;
local ($count) = Ø;
local (@database, $id, $year, $make, $model, $description, $price, $contact,
@template, $matches, $search);
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
foreach (@database) {
($id, $year, $make, $model, $description, $price, $contact) = split(/::/);
if (($make =~ /\b$data{'make'}/i) || ($data{'make'} =~ /\b$make/i)) {
# make matched, check model
if (($model =~ /\b$data{'model'}/i) || ($data{'model'} =~ /\b$model/i)
|| $data{'model'} eq "") {
# model matched, check year
if ( (($data{'lowyear'} <= $year) && ($data{'highyear'} >= $year)) ||
(($data{'lowyear'} eq "") && ($data{'highyear'} eq "")) ) {
# year is in range, check price
if ( (($data{'lowprice'} <= $price) && ($data{'highprice'} >= $price))
|| (($data{'lowprice'} eq "") && ($data{'highprice'} eq "")) ) {
# price matched, add entry to user's list of matches
$matched .= "<HR>$year $make $model<BR>$description<BR>Asking
\$$price<BR>Contact: $contact\n";
$count++;
}
}
}
}
}
$search = "$count entries matched your search for <B>$data{'year'}
$data{'make'} $data{'model'}</B>.\n";
open(TEMPLATE, "$tmpl_file") || die "Content-type: text/html\n\nCannot
open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
$template[6] =~ s/XXXX/$search/e;
$template[7] =~ s/YYYY/$matched/e;
print "Content-type: text/html\n\n";
print @template;
}
|
Putting
Together the Search Script
Now that you have
completed the Search subroutine, you have all the pieces for your search
script. You just need to combine the Search subroutine with the No_SSI and
User_Data subroutines in a file called search.pl. Listing 7.18 contains
all the Perl code for the search script. You need to change the value for
the $path variable to the path to the database and template files on your
machine. Also, if you will be using this script on a Windows machine, you
should remove the first line of code. Figure 7.6 shows the results of a
sample search, where the user entered the string Honda for the make to
search for.
Listing 7.18: The search.pl File
#!/usr/local/bin/perl
# All users need to change the $path
# variable to the path to the database
# and template files on their machine.
# Windows users need a path in the form
# $path = "c:\\robertm\\";
$path = "/users/robertm/";
$database = $path . "car.dat";
$tmpl_file = $path . "results.tmpl";
%data_received = &User_Data;
&No_SSI(*data_received);
if ($ENV{'REQUEST_METHOD'} eq "POST") {
&Search(%data_received);
} else {
print "Content-type: text/html\n\nYou are not using this script correctly!";
}
sub Search {
local (%data) = @_;
local ($count) = Ø;
local (@database, $id, $year, $make, $model, $description, $price, $contact,
@template, $matches, $search);
open(DATABASE, "$database") || die "Content-type: text/html\n\nCannot open
database!";
@database = <DATABASE>;
close(DATABASE);
foreach (@database) {
($id, $year, $make, $model, $description, $price, $contact) = split(/::/);
if (($make =~ /\b$data{'make'}/i) || ($data{'make'} =~ /\b$make/i)) {
# make matched, check model
if (($model =~ /\b$data{'model'}/i) || ($data{'model'} =~ /\b$model/i)
|| $data{'model'} eq "") {
# model matched, check year
if ( (($data{'lowyear'} <= $year) && ($data{'highyear'} >= $year)) ||
(($data{'lowyear'} eq "") && ($data{'highyear'} eq "")) ) {
# year is in range, check price
if ( (($data{'lowprice'} <= $price) && ($data{'highprice'} >= $price))
|| (($data{'lowprice'} eq "") && ($data{'highprice'} eq "")) ) {
# price matched, add entry to user's list of matches
$matched .= "<HR>$year $make $model<BR>$description<BR>Asking
\$$price<BR>Contact: $contact\n";
$count++;
}
}
}
}
}
$search = "$count entries matched your search for <B>$data{'year'}
$data{'make'} $data{'model'}</B>.\n";
open(TEMPLATE, "$tmpl_file") || die "Content-type: text/html\n\nCannot
open template!";
@template = <TEMPLATE>;
close(TEMPLATE);
$template[6] =~ s/XXXX/$search/e;
$template[7] =~ s/YYYY/$matched/e;
print "Content-type: text/html\n\n";
print @template;
}
sub No_SSI {
local (*data) = @_;
foreach $key (sort keys(%data)) {
$data{$key} =~ s/<!--(.|\n)*-->//g;
}
}
sub User_Data {
local (%user_data, $user_string, $name_value_pair,
@name_value_pairs, $name, $value);
# If the data was sent via POST, then it is available
# from standard input. Otherwise, the data is in the
# QUERY_STRING environment variable.
if ($ENV{'REQUEST_METHOD'} eq "POST") {
read(STDIN,$user_string,$ENV{'CONTENT_LENGTH'});
} else {
$user_string = $ENV{'QUERY_STRING'};
}
# This line changes the + signs to spaces.
$user_string =~ s/\+/ /g;
# This line places each name/value pair as a separate
# element in the name_value_pairs array.
@name_value_pairs = split(/&/, $user_string);
# This code loops over each element in the name_value_pairs
# array, splits it on the = sign, and places the value
# into the user_data associative array with the name as the
# key.
foreach $name_value_pair (@name_value_pairs) {
($name, $value) = split(/=/, $name_value_pair);
# These two lines decode the values from any URL
# hexadecimal encoding. The first section searches for a
# hexadecimal number and the second part converts the
# hex number to decimal and returns the character
# equivalent.
$name =~
s/%([a-fA-FØ-9][a-fA-FØ-9])/pack("C",hex($1))/ge;
$value =~
s/%([a-fA-FØ-9][a-fA-FØ-9])/pack("C",hex($1))/ge;
# If the name/value pair has already been given a value,
# as in the case of multiple items being selected, then
# separate the items with a " : ".
if (defined($user_data{$name})) {
$user_data{$name} .= " : " . $value;
} else {
$user_data{$name} = $value;
}
}
return %user_data;
}
|
Interacting with complex DBMS programs
So far in this chapter,
you have been interacting with a text file database. Although this example
is useful to illustrate the interaction of the Web with a database through
CGI, it's unlikely that you have text file based databases. If you have
any databases, they are probably in a highly specialized, complex DBMS
program such as Oracle, Sybase, Access, or FileMaker Pro. However, even
though these programs are more complicated than a simple text file
database, you still interface them to the Web with a CGI script.
Recall from Chapter 2
that CGI defines how data passes back and forth between your Web pages and
your CGI scripts. When interacting with a DBMS program, you want to take
the information obtained from your Web page and get it to your DBMS. You
may also have data coming out of your database that you want to display in
a Web browser. Once your CGI script obtains data from the DBMS program,
you display it the same way you have displayed other data in this book: by
formatting it with HTML and sending it via standard output to the user's
Web browser. But you may not know how to write code to interact with the
DBMS. This code would be highly specific to each type of database. For
example, the code you would write to interface with Sybase would be very
different than the code to interface with FileMaker Pro. Many of the more
popular databases, such as Oracle and Sybase, already have interfacing
routines that you can purchase or download.
Before pouring lots of
time and energy into writing an interface to your database, you should
look either for some library routines or for existing interfaces that you
can download or purchase. First check with the company that sells your
DBMS program. They may distribute an interface themselves or may know
where to get one. Next explore the search directories listed in the
"Search Engines and Directories" section of the Appendix. Unless you know
the name of an existing interface product, search for the name of your
DBMS. There are also several resources in the "Script Archives, Gateways,
and Libraries" section that contain links to existing DBMs gateways. Other
good resources are newsgroups and mailing lists, which are listed in the
"Usenet Newsgroups and Mailing Lists" section of the Appendix. If you are
using a common database package, chances are good that someone has already
interfaced with it. Hopefully, by searching and asking around, you will
find an existing interface for your database. Even if you end up paying
for a solution, it may be cheaper than spending the time to write your
own.
