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. Figure 7.1 shows how Netscape displays this HTML.
Listing 7.1: The admin.html File
Figure 7.1: The administration home page 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
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//;
Figure 7.2 shows how Netscape displays the contents of the database with some example records.
Figure 7.2: Example contents of the database
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
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. Figure 7.3 shows how Netscape displays the Adding Records page.
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; }
Figure 7.3: The Adding Records page 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
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. Figure 7.4 shows how the Modify Entry page appears with an example database record.
Figure 7.4: The Modify Entry page 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, and Figure 7.5 shows how Netscape displays the form.
Listing 7.14: The search.html File
Figure 7.5: The Search page 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
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.
Figure 7.6: Results of a search for the make Honda 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.