by Robert Niles
Using the World Wide Web to access databases can save your organization tremendous amounts of time. The task may seem daunting at first, but any effort on your part to integrate your existing database with the Web is well worth the effort.
How you go about designing your database depends on the tools you currently have, what type of information you need to store, and what you're willing to purchase. The nice thing is that there are different methods to save and retrieve information, no matter what your budget.
The most difficult and daunting task is how to go about designing your database to store information, and to retrieve that information. What would happen if you wanted to upgrade your database, or if you needed to add on to your database?
Figure 36.1 shows you how information flows from the point in which someone on the Web requests a page that needs information from your database. When requesting information that derives from a database, quite a few steps are involved to complete that request. Your Web server receives the request from the visitor to your site, then sends that information on to your CGI script. The CGI script acts as the main gateway tying two very different systems together. The CGI script performs the actual query, receives the results from the database, formulates a proper reply, and sends it off to the Web server, which in turn sends it to the person visiting your site.
Figure 36.1 : This diagram shows the information flow between those on the Web and your database.
As you can see, there are quite a few steps involved in this process. Your goal is to tie all this together in such a way that it is totally transparent to the person visiting your site.
Most likely, your organization already has an existing database in which they have stored information about their products, their customers, and other aspects of business life. Some of this information you might want to allow your customers to see, or you might even want to make the information in the database available to your workers stationed away from the office. If so, you would have to create HTML documents that contain all this information all over again, which, if you're part of a large organization, can be a tedious task. Integrating the Web with your databases can save you tremendous amounts of time in the long run, especially when it comes to maintaining that information. As the database changes, your Web pages change.
Another good reason to use the World Wide Web to access your database is that any Web browser that supports forms can access information from the database-no matter which platform is being used.
Although the limitations in accessing databases have decreased in the last few months as database companies have scrambled to ensure that their product easily integrates with existing Web applications, there are still a few left you will want to look out for.
There is not an official standard which you can use to connect to a database. If you create a script to access one type of database, there is no guarantee that the same script will work on a different database-even if the query used was the same. (People are working on this, though.) Because of this, you will be required to learn a good deal about each database application that you come across.
Also, the browser and the server are stateless in relation to each other. The browser makes a request, the server processes the query and sends the result back to the browser, and the connection is closed. This creates a problem with databases, because a connection to a database is usually constant. Someone, through a normal method, would access the database, which keeps a connection open, locking a record if any editing is performed, and closes the connection only when the person is finished. Accessing a database doesn't work exactly the same way when doing so from the Web.
Consider the following events:
There are two ways to go about handling this. The first method involves keeping track of all entries with a timestamp. This will allow both entries to be maintained by the database, without the possibility of either person's entries being overwritten.
Another way is to provide information only from the database, and not allow someone on the Web to edit, remove, or insert information to the database. While this limits some of the possibilities for having the database on the Web, it also alleviates some of the security problems as well.
The major problem with having those on the Web accessing your database is that your CGI script is trusted by your database program. That is to say, your database has to accept commands from your CGI script, and your CGI script needs to perform queries based upon what you want to provide to those on the Web. This can lead to problems if someone with ill intentions gains access to a script that has the ability to edit your database.
Also, most databases require the use of a password. Because your CGI script stores user information to the database, as well as retrieving information from the database, your script is going to need to have to have the password to access your database. You need to ensure that your script cannot be read by others both within your organization and outside your organization.
In this chapter, you take a look at three different kinds of databases: flatfile, DBM, and SQL databases, building a phonebook for each one so that you can see the differences between the three methods to store information.
Flatfile databases are just about the easiest database you can create. Other than the necessity to have a language with which to program, there is nothing else needed to create a small ASCII-text database.
A flatfile database mainly consists of lines of text where each line is its own entry. There is no special technique to index the database. Because of this, flatfile databases usually are relatively small. The larger the database, the longer it takes to perform queries to the database.
The first thing that you need is an HTML page, which will allow someone to enter information into the database. You must first decide what you want for the visitor to enter.
Your HTML document consists of three forms. The first form will allow the visitor to enter information into the phonebook database. The second form will allow the visitor to display the contents of the database, and the third form will allow the visitor to perform a keyword search on the database.
You can expand on this later, but right now I simply want the visitor to be able to enter a first name, a last name, and a telephone number, all of which will be stored in your flatfile database.
The first form assigns the input from the visitor into three names: fname, lname, and phone. A hidden input type (see Listing 36.1), named act (for action), is created that tells your script which action it is expected to perform. Once the visitor fills out the form, they can click Add to Phonebook (see Figure 36.2 to get an idea of what this would look like).
Figure 36.2 : This form allows a visitor to enter information into a flatfile phonebook database.
Listing 36.1 Pbook.html-HTML Code that Will Allow Visitors to Query a Phonebook Database
<HTML> <HEAD><TITLE>Flatfile Phonebook</TITLE></HEAD> <BODY> <H1>Your Flatfile Phonebook</H1> <HR> <H2>Insert Information</H2> <FORM ACTION="/cgi-bin/pbook.pl" METHOD="POST"> <PRE> First Name: <INPUT TYPE="text" NAME="fname"> Last Name: <INPUT TYPE="text" NAME="lname"> Phone Number: <INPUT TYPE="text" NAME="phone"> </PRE> <INPUT TYPE="hidden" NAME="act" VALUE="add"> <INPUT TYPE="submit" value="Add to Phonebook"> </FORM> <HR><P>
The second form consists of a line in which the visitor can click Display to get a listing of everyone who has been entered into the database. Notice the use of the hidden input type. This time, the name act will have the value of display.
<H2>Display Information</H2> <FORM ACTION="/cgi-bin/pbook.pl" METHOD="POST"> <INPUT TYPE="hidden" NAME="act" VALUE="display"> Click on <INPUT TYPE="submit" value="Display"> to view all entries in the phonebook </FORM> <HR><P>
The last form on the Web page will allow the visitor to enter a keyword, which is used to search through the database. We have assigned string entered by the user to the name keyword. We have also given the value, search, to the hidden input type named act.
<H2>Search the Phonebook</H2> <FORM ACTION="/cgi-bin/pbook.pl" METHOD="POST"> Enter a keyword to search for: <INPUT TYPE="text" NAME="keyword"> <INPUT TYPE="hidden" NAME="act" VALUE="search"> <INPUT TYPE="submit" VALUE="Start Search"> </FORM> </BODY> </HTML>
Now that you have finished with the HTML document, it's time to write the script that handles the information provided by the visitor.
If you take look at Listing 36.2, the first part of your script needs to be able to read STDIN and separate the contents, assigning each value to the array contents.
Listing 36.2 Pbook.pl-The Script Reads STDIN and Separates Its Contents
#! /usr/bin/perl
if ($ENV{'REQUEST_METHOD'} eq 'POST')
{
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$contents{$name} = $value;
}
}
Next, you need to declare the content type to the server, and provide the path to the file that will store your names and phone numbers.
print "Content-type: text/html\n\n"; $phonebook = "phonebook.txt";
Now, your script checks to see what value the name act contains. Here, it checks to see whether the value of act is equal to the string, add. If so, then it will open up the database, or it will go to the subroutine no_open, if, for some reason, your script cannot open the database. If the script successfully opened the database, then it appends the information entered by the visitor, and then creates a Web page (see Figure 36.3) stating that the information entered by the visitor was added to the database. Last, use the exit; command to end the script because no other functions are possible (see Listing 36.3).
Listing 36.3 Pbook.pl-The First Form Checks to See if the Visitor Wanted to Enter Information
if ($contents{'act'} eq "add") {
open(BOOK, ">>$phonebook") || do {&no_open;};
print BOOK "$contents{'fname'}:$contents{'lname'}:$contents{'phone'}\n";
close(BOOK);
print <<"HTML";
<HTML>
<HEAD><TITLE>Information added</TITLE></HEAD>
<BODY>
<H1>Information added</H1>
The information entered has been added to the phonebook.
<HR>
<CENTER>
<A HREF="/pbook.html">[Return to the Phonebook]</A>
</CENTER>
</BODY>
</HTML>
HTML
exit;
}
If you recall from just a bit ago, you told your script to go the subroutine no_open if the script wasn't able to access the database for some reason. Listing 36.4 shows you what happens if an error does occur. A Web page is generated (see Figure 36.4) informing the visitor that the database couldn't be accessed.
Figure 36.4 : If the database could not be opened, you need to inform the visitor of this.
Listing 36.4 Pbook.pl-An Error Page Is Created Informing the Visitor of a Problem
sub no_open {
print <<"HTML";
<HTML>
<HEAD><TITLE>Error!</TITLE></HEAD>
<BODY>
<H1> Error! Could not open the database!</H1>
<CENTER>
<A HREF="/pbook.html">[Return to the Phonebook]</A>
</CENTER>
</BODY>
</HTML>
HTML
exit;
}
If the visitor clicked Display, the information from the database is retrieved and simply appears to the visitor in a table. By using a table, the contents of the phonebook could be easily formatted into something that is easy to view.
As you can see in Listing 36.5, you check to see if the value of act is equal to display; if so, a page is created, and the contents of your database appear, where each line of information is broken into its respective parts. To accomplish this, use Perl's split function. The value of $line is split and assigned to the array entry. By splitting each line, you can control how you want the information to appear to the visitor (see Figure 36.5).
Figure 36.5 : The script displays the phonebook in its entirety.
Listing 36.5 Pbook.pl-The Contents of the Database Are Read and Displayed to the User in HTML
if ($contents{'act'} eq "display") {
...
open (BOOK, $phonebook) || do {&no_open;};
until (eof(BOOK))
{
$line = <BOOK>;
@entry = split(/:/, $line);
print "<TR><TD>$entry[0] $entry[1]</TD><TD> $entry[2]</TD></TR>";
}
close(BOOK);
...
Once the information from the database is displayed, you finish the HTML document and exit the script.
Last, you need to check to see if the visitor requested to perform a keyword search (see Listing 36.6). If so, you need to open the database and check each line against the keyword entered by the visitor.
First, the database is opened and the top portion of the results page is created. Next, you have a counter, which is initially set to zero (more on this in a moment). Now, each line is read and checked against the value contained in the variable $contents{'keyword'}. If so, then the count is incremented and the result printed as part of the created Web page (see Figure 36.6). Use the same technique here as earlier by splitting each line that is to be printed into an array.
Once you exit the loop, you check the count. If the count is equal to zero, then you know that there were no entries in the database that matched the keyword search, and you inform the visitor that their search produced no results.
Listing 36.6 Pbook.pl-The Script Checks to See if Anything Matches the Keyword
if ($contents{'act'} eq "search") {
open (BOOK, "$phonebook") || do {&no_open;};
$count=0;
until (eof (BOOK))
{
$line = <BOOK>;
chop($line);
if ($line =~ /$contents{'keyword'}/gi)
{
$count++;
@entry = split(/:/, $line);
print "<TR><TD>$entry[0] $entry[1]</TD><TD> $entry[2]</TD></TR>";
}
}
if ($count==0)
{
print "No Matches";
}
close(BOOK);
Once the script has checked each line against the keyword, the database is closed and the script finishes the Web page and exits the script.
To get a better feel of how the script works, take a look at the script in its entirety, which is located on the CD-ROM.
Most UNIX systems have some sort of DBM database; in fact, I have yet to find a system that runs without one. DBM is a set of library routines that manage data files consisting of key and value pairs. The DBM routines control how users enter and retrieve information from the database. Although not the most powerful mechanism for storing information, using DBM is a faster method of retrieving information than using a flatfile. Because most UNIX sites use one of the DBM libraries, the tools that you need to store your information to a DBM database are readily available.
There are almost as many flavors of the DBM libraries as there are UNIX systems. Although most of these libraries are not compatible with one another, all basically work the same way. This section explores each of the DBM flavors to give you a good understanding of their differences. Afterward, you create an address book script, which should give you an idea of how DBM databases work.
Here's a list of some of the most popular DBM libraries available:
| ON THE WEB |
http://www.perl.com/perl/ For more information on SDBM and Perl, visit the Perl home page |
| NOTE |
GNU stands for GNU's Not UNIX. GNU is a collection of programs developed by volunteers to help provide UNIX system commands and programs free to the public. Along with GDBM, you can find quite a few additional GNU programs like GNU emacs (a powerful text editor) |
If you can't find a particular DBM database on your system, search the Web for DBM databases.
First, you create a Web page that will allow you to enter information into the database. Listing 36.7 is an example of what is needed. In this chapter's example, you ask the visitor to enter a name, e-mail address, and phone number.
Listing 36.7 Dbbook.html-The Web Page Allows Visitors to Enter Information into the Database
<HTML> <HEAD><TITLE>DB Phonebook</TITLE></HEAD> <BODY> <H1>DB Phonebook</H1> <FORM ACTION="/cgi-bin/dbbookadd.pl" METHOD="POST"> <pre> Name:<INPUT NAME="name"> Email:<INPUT NAME="email"> Phone:<INPUT NAME="phone"><P> </pre> <INPUT TYPE="SUBMIT"> </FORM> </BODY> </HTML>
The script shown in Listing 36.8 uses the Berkeley db library (mentioned in the preceding bulleted list) to store information. Again, you're constructing a phonebook which can be used via the Web. This is done so that you can see how each database works in relation to the other.
First, the information that the script receives from STDIN is split and stored in an environmental variable. Then the database name is stored in the variable file. Next, you open the database using the tie() function, which allows us to specify how the database will be opened. In this script, you specify using O_RDWR that the database is to be opened for reading and writing, or it is to be created if it doesn't exist (O_CREAT).
Next, you check to see if the name entered by the visitor has been already entered earlier. To do this, all you need to do is to code a line that reports an error if the key used, which is the name entered by the visitor, already exists in the database. If the key does exist, then the script goes to the subroutine error.
Next,
$db{$form{'name'}}=join(":",$form{'email'},$form{'phone'});
is the heart of the whole script. This line takes the information that the user entered and places that information into the database. The name entered by the visitor becomes the key, and the e-mail address and the phone number are joined together using a colon to form the value. Without all the coding, the previous line looks something like the following:
Robert Niles=rniles@selah.net:555-5555
Listing 36.8 Dbbookadd.pl-A Small Script that Adds Information to a DBM Database
...$file="addresses";
$database=tie(%db, 'DB_File', $file, O_RDWR|O_CREAT, 0660);
&error if $db{"$form{name}"};
$db{$form{'name'}}=join(":",$form{'email'},$form{'phone'});
untie(%db);
undef($database);
...
print "Location: /cgi-bin/dbbook.pl\n\n";
Last, since a script should always return something to the visitor, you redirect the visitor to another script, which displays the information in the phonebook.
To retrieve information from a database, all you have to do is create a loop that reads the contents of the database and separates the value of each key at the colon. In your script,
while (($key,$value)= each(%db)) {
starts the loop that accomplishes this task (see Listing 36.9). Within the loop, the value of each key is split and assigned to the array part. Once that is done, you can format the result in any manner you choose. In this example, I have placed the name to be printed as part of a mailto: anchor, using each entry's e-mail address if it was entered.
Listing 36.9 Dbbook.pl-This Script Reads the Information from the Database
use DB_File;
use Fcntl;
print "Content-type: text/html\n\n";
$file="addresses";
$database=tie(%db, 'DB_File', $file, O_READ, 0660) || die "can't";
...
while (($key,$value)= each(%db)) {
@part = split(/:/,$value);
if ($part[0]) {
print "<TR><TD><A HREF=\"mailto:$part[0]\">$key</A></TD>";
}
else {
print "<TR><TD>$key</TD>";
}
print "<TD>$part[0]</TD><TD>$part[1]</TD></TR>\n";
}
...
untie(%db);
undef($database);
exit;
The script produces a Web page that looks like the one in Figure 36.7.
If your database starts to get large, it's convenient when you can provide a means by which visitors to your site can search for a specific keyword. The Web page needed to perform this search needs to have the following tags included (see Listing 36.10).
Listing 36.10 Dbbooksearch.html-This Form Allows a Visitor to Enter a Keyword to Perform a Search
<FORM ACTION="/cgi-bin/dbbooksearch.pl" METHOD="POST"> Name:<INPUT NAME="name"><BR> <INPUT TYPE="SUBMIT"> </FORM>
Performing a search works much in the same manner as when you simply display the whole database. Except that this time, instead of immediately displaying each entry, you check it first to see if it matches the keyword entered by the visitor. If the keyword matches the key, then you print the line; otherwise, you simply skip ahead and check the next entry (see Listing 36.11).
Listing 36.11 Dbbooksearch.pl-By Matching Each Field Against a Query, You Can Limit which Information Is Returned to the Visitor
...$file="addresses";
$database=tie(%db, 'DB_File', $file, O_READ, 0660) || die "can't";
...while (($key,$value)= each(%db)) {
if ($key =~ /$form{'name'}/i) {
@part = split(/:/,$value);
if ($part[0]) {
print "<TR><TD><A HREF=\"mailto:$part[0]\">$key</A></TD>";
}
else {
print "<TR><TD>$key</TD>";
}
print "<TD>$part[0]</TD><TD>$part[1]</TD></TR>\n";
}
}
...
Now that you have seen how DBM databases work, you can take the same concepts from these scripts and apply them to something different. For example, a hotlinks script in which you can store information for all of your favorite Web sites. Or, maybe a proper address book that stores the names, addresses, and phone numbers of all your customers. You can also create a database that stores names and e-mail addresses, which you can use as a mailing list, providing friends and customers news about you or your organization-or your products.
Most relational database servers consist of a set of programs which manage large amounts of data, offering a rich set of query commands that help manage the power behind the database server. These programs control the storage, retrieval, and organization of the information within the database. This information within the database can be changed, updated, or removed, once the support programs or scripts are in place.
Unlike DBM databases, relational databases don't link records together physically like the DBM database does using a key/value pair. Instead, they provide a field in which information can be matched and the results of which can be sent back to the person performing the query as if the database were organized that way.
Relational databases store information in tables. Tables are similar to a smaller database that sits inside the main database. Each table usually can be linked with the information in other tables to provide a result to a query. Take a look at Figure 36.8 which shows how this information could be tied together.
Figure 36.8 depicts a query in which it requests employee information from a database. To get a complete response, information is retrieved from three different tables, each of which store only parts of the information requested. In the figure, information about the person's pay rate is retrieved, while Departmental information and Personal information is retrieved from other tables. Working together, this can produce a complete query response, producing an abundant amount of information on an individual.
Each table consists of columns and rows. The columns identify
the data by a name, while the rows store information relevant
to that name. Take a look at the following example:
Name Number Fred Flintstone 01-43 ff@bedrock.com Barney Rubble 01-44 br@bedrock.com
The column heads give a name to each item below it. Information within a table is stored in much the same way.
Now, if you add more tables to the database, you could have something
that looks like the following:
Name PayRate Fred Flintstone $34/month Barney Rubble $29/month
And you could have department information as well
Name Department Tardy Record Fred Flintstone Dino-Digger 17 Barney Rubble Pebble Pusher 3
With this information, you can perform a query to get a complete look on an individual.
Select * from personal,department,finance where Name="Fred Flintstone
This would pull up all information on Fred Flintstone, from all three records. You could even be more specific, pulling only certain parts from each table:
select Name.finance,Name.finance where Tardy > 5
With this information, I think Fred would be in serious trouble-but you should have an idea of how relational databases work.
Structured Query Language (SQL) is a language which provides a user interface to relational database management systems (RDBMS). Originally developed by IBM in the 1970s, SQL is the de facto standard for storing and retrieving information for relational databases, as well as being an ISO and ANSI standard. The current standard is SQL2 with SQL3 being planned for release in 1998.
The purpose of SQL is to provide a language, easily understandable by humans, which interfaces with relational databases.
mSQL is a SQL server that works over a TCP/IP network. Although not as powerful as some of the commercial SQL engines, mSQL has just about everything that you might need for most everyday jobs. Some of commercial SQL servers cost well over $3,000, but mSQL is free to the educational and nonprofit sectors, and costs about $170 if you use it for commercial purposes.
David Hughes wrote mSQL to fill the need for a freely available
SQL engine in which individuals can do research and experiments
with SQL databases. mSQL has all the main functions that you would
find in a SQL server and runs on most UNIX systems (with ports
being developed for OS/2, Windows NT, and the Amiga by third-party
contributors). The latest version is 1.0.16 and includes a C language
API in which you can create applications, including CGI applications,
which interface with mSQL. David Hughes is currently working on
release 2.0, which probably will be available by the time you
read this book. For more information on mSQL, visit the following
site:
| ON THE WEB |
http://hughes.com.au/product/msql/ Visit the mSQL home page for more information on mSQL, its API, as well as a lot of user-contributed software |
Visiting this site is well worthwhile, because it contains many programs for various platforms and languages (including TCL, Python, and Java) which support the mSQL database.
Written by Andreas Koenig, MsqlPerl is a Perl language interface to mSQL. The current version is 1.11, and can be found on the accompanying CD-ROM or at the mSQL ftp site which can be accessed at the previously listed URL.
Building a relational database can at times be a daunting task-especially for a large organization with hundreds of employees and dozens of departments. With enough time and forethought, relational databases can work with information like no other database system can. The goal of any database administrator is to keep things as simple as you can without duplicating information.
To insert information to a database, you first have to create one. This chapter's example database, using mSQL, consists of two tables.
The first table called employees contains personal information
about each employee.
| Column name | Column description |
| empnum | Number created automatically by your script. The empnum is the primary key for this table, which means the number is unique within the table. |
| Fname | The first name of the employee. |
| Lname | The last name of the employee. |
| home_phone | The home phone number for the employee. |
| Other_phone | A secondary phone number for the employee. |
| The e-mail address of the employee. | |
| Deptnum | A number of the department in which the employee works. |
The department number (deptnum) coincides with the department
number in your second table, departments. In this table,
you have only two fields:
| Column name | Column description |
| deptnum | The department number |
| deptname | The name of the department |
With this information, you can create the database. Included on the CD-ROMs is a file called Work.dump, which is a text file that contains information that can be loaded into the mSQL database.
To add this information to the database, you need to first create
the database using the msqladmin utility that is provided with
mSQL, which is on the accompanying CD-ROMs.
| NOTE |
For additional information on how to compile and install mSQL, see the documentation provided within the mSQL archive located on the accompanying CD-ROMs, or visit the mSQL home page at http://www.Hughes.com.au/product/msql/ |
If you have installed mSQL in its default location, /usr/local/Minerva, you can simply type:
% /usr/local/Minerva/bin/msqladmin create work
at the UNIX command line prompt. If you have installed mSQL elsewhere, change the path, specifying the proper path.
This command creates a database called "work". Once the database is created, information can be stored within the database.
Next, to add each table and the table's contents to the database type
% /usr/local/Minerva/bin/msql work <<work.dump
at the UNIX prompt.
You can confirm that the information was inserted into the database by typing
% /usr/local/Minerva/bin/relshow work
at the Unix shell prompt. The relshow command will display something like the following:
Database = work +---------------------+ | Table | +---------------------+ | employees | | departments | +---------------------+
Once this is done, you can start inserting information into the database.
First, you want to create a form which will allow someone to enter information into the database. The one I have created (see Listing 36.12) looks like the following:
Listing 36.12 Phoneadd.html-An HTML Document that Will Allow You to Add Employees to the SQL Database
<HTML>
<HEAD><TITLE>Insert into Phonebook</TITLE><HEAD>
<BODY>
<H1>Add Employee to the phonebook</H1>
<FORM ACTION="/cgi-bin/phoneadd.pl" METHOD="POST">
<pre>
First Name: <INPUT TYPE="text" NAME="fname">
Last Name: <INPUT TYPE="text" NAME="lname">
Home Phone: <INPUT TYPE="text" NAME="home_phone">
Other Phone: <INPUT TYPE="text" NAME="other_phone">
Email Address: <INPUT TYPE="text" NAME="email">
Select department: <SELECT NAME="deptnum">
<OPTION VALUE="1"> Administration
<OPTION VALUE="2"> Techies
<OPTION VALUE="3"> Web Masters
<OPTION VALUE="4"> Network
<OPTION VALUE="5"> Computer Tech
<OPTION VALUE="6"> Sales
<OPTION VALUE="7"> Apple Tech
<OPTION VALUE="8"> MIS
</SELECT>
</pre>
<P>
<INPUT TYPE="submit" VALUE="Add Employee">
</FORM>
</BODY>
</HTML>
Once filled out, the information in this form will be sent to the script Phoneadd.pl. The Phoneadd.pl script takes the information from the visitor and attempts to add that information to the database. The first thing that you might notice about the script (see Listing 36.13) is that the script first escapes those characters which the database cannot normally handle.
Listing 36.13 Phoneadd.pl-Information from STDIN Is Split and Any Special Characters Are Escaped
if ($ENV{'REQUEST_METHOD'} eq 'POST')
{
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/\\/\\\\/g;
$value =~ s/\$/\\\$/g;
$value =~ s/\(/\\(/g;
$value =~ s/\)/\\)/g;
$value =~ s/\^/\\^/g;
$value =~ s/\|/\\|/g;
$value =~ s/\'/\\'/g;
$value = ~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$contents{$name} = $value;
}
}
In Listing 36.14, the script then checks to see which employee numbers (empnum) exist and increments that number that will be assigned to the next employee being entered into the database.
Listing 36.14 The Employee Number Is Checked, and an Original Number Is Assigned to the New Entry
use Msql;$db1 = Msql->Connect("", "work") or die;$nextnum=1;
$sth = $db1->Query("select empnum from employees");
$count = $sth->numrows;
while ($rows=$sth->FetchRow)
{
$nextnum=$rows+1;
}
Then, a query is performed in which all the information is placed into the database (see Listing 36.15). After that is done, then the script creates an HTML page that is sent back to the visitor, informing him that the query was successful.
Listing 36.15 The Employee Information Is Then Inserted into the Database
$names = "empnum,fname,lname,home_phone,other_phone,email,deptnum";
$values = "$nextnum,'$contents{'fname'}','$contents{'lname'}'";
$values = $values . ",'$contents{'home_phone'}','$contents{'other_phone'}'";
$values = $values . ",'$contents{'email'}',$contents{'deptnum'}";
$sth1 = $db1->Query("insert into employees ($names) VALUES ($values)");
print "Content-type: text/html\n\n";
print $Msql::db_errstr, "\n";
...
Retrieving information from a relational database can be tricky at first but, once you get the idea of how SQL queries work, you will start to appreciate SQL.
Previously, the INSERT clause was used to add information to the database. Now, to retrieve information from a database, you will need to use the SELECT clause. The SELECT clause allows you to specify which information you want to retrieve from the database. The syntax, using mSQL is:
SELECT [column name] from [table name]
In the spirit of relational databases, information can also be retrieved from multiple tables using a query like this:
SELECT employees.empnum department.empnum from employees,department
Notice how we selected the column empnum from two tables instead of just one. Mind you-this query is quite a simple one for a relational database to handle. They can get quite complex!
In the next script, the query gets a little more complex. Your script performs a query that receives information from two tables, and then creates an HTML page that shows information about each employee. This time, instead of displaying the department number, you match the department numbers with the department names and show the names instead.
The first section of the script specifies that we need to use the MsqlPerl module. Next, we create the variable $db1, which contains the information needed to connect to the database. The examples will use the database named work.
#! /usr/bin/perl
use Msql;
$db1 = Msql->Connect("", "work") or die;
Now, referring to Listing 36.16, a query is built that will select every column from both the employees and the department tables, matching the information from each table by using the information obtained from the column empnum. That information is then displayed to the visitor, with the information sorted by the department number.
Listing 36.16 Phone.pl-A Query Is Built Specifying which Information Is eeded from the SQL Database
$sth = $db1->Query("select employees.empnum,employees.fname,employees.lname,
employees.home_phone,employees.other_phone,employees.email,
departments.deptnum,departments.deptname from employees,departments where
employees.deptnum = departments.deptnum
order by employees.empnum") or die;
Next, the information retrieved from the database, and stored in the array @row, is broken up and displayed in an HTML document to the visitor.
print "<TR><TD align=\"right\">$row[0]</TD><TD><b>
<a href=\"mailto:$row[5]\">$row[1] $row[2]</a></TD>
<TD>$row[3]</TD><TD>$row[4]</TD><TD>$row[5]</TD>
<TD>$row[7]</TD></TR>",
Last, a count is kept showing how many people matched the search criteria.
while @row=$sth->FetchRow;print $Msql::db_errstr; $count = $sth->numrows; print "<TR><TD colspan=6 align=\"center\"> \ Number of Employees = $count</TD></TR>";
One of the biggest problems when dealing with databases on the Web is trying to fix any problems that may occur when you are writing your script. First, you can have problems because of a bug in your script. Second, you can have problems because of an improper query.
The best way to see if the problem lies within your script is to create a copy of the script which includes dummy variables-variables which contain information as if a visitor actually entered something into a form. For example, using the previous scripts, you can create a set of variables that mimic information that a visitor might have entered:
$contents{'fname'} = "John";
$contents{'lname'} = "Doe";
Once you have the dummy variables set, you can execute your script via the command line:
% /usr/bin/perl phone.pl
Perl will report any problems with your script if you have programmed any code improperly.
To figure out what may be wrong with your SQL query, add the following line:
print $Msql::db_errstr, "\n";
This instructs mSQL to provide an error string if anything goes wrong with the SQL query, and report whether the query is performed from the shell, or via a Web page. Just make sure that the Content-type is specified before trying to send anything out to the server. For example, if I tried to SELECT employees.phome_home instead of employees.home_phone, the script would report:
Unknown field employees.phome_phone
Last, try to keep things simple and build up from what you know works. At first, create a script that contains the query that you need to perform, making sure that you use the Content-type, text/plain. This will allow you to check and make sure that your script is providing a proper query. If all goes well, access your database by using the same query from the command line. If the query produces the proper results, then you can move on and try to access your script through the Web server. If you are having problems here, make sure that you have specified the Content-type before anything is sent out to the server. This is probably one of the biggest problems with any script. An error occurs and your script sends information back to the Web server without specifying the Content-type.