Integrating SQL with CGI, Part 1

Now that we have created our “postcards” table, let's insert some dummy data directly at the MySQL prompt. Then we will write the program show-postcard.pl to display the dummy postcard. Finally, we will write a program to allow users to enter postcards via an HTML form.

We can insert data into a table by using the SQL insert command. Let's say we wish to insert a postcard with the following information:

ID: 12345
Sender name: Reuven Lerner
Sender e-mail: reuven@netvision.net.il
Recipient name: Bill Clinton
Recipient e-mail: president@whitehouse.gov
Graphic: smile.gif
Text: Hey there, Mr. President!

To insert this information, use the following SQL command:

insert into postcards
  (id_number, sender_name, sender_email,
   recipient_name, recipient_email, graphic_name,
   postcard_text)
values
  (12345, "Reuven Lerner",
   "reuven@netvision.net.il",
   "Bill Clinton",
   "president@whitehouse.gov",
   "smile.gif",
   "Hey there, Mr. President!");

Typing this command at the MySQL prompt produces this response:

Query OK, 1 rows affected (0.34 sec)

In other words, one new row was successfully added to the table. To retrieve it, use the SQL command:

select * from postcards where id_number = 12345;

which produces the result shown in Table 2.

Table 2. New Database Entry

While it might look ugly, this output actually makes sense. The problem is that most CRTs are only 80 columns wide, while the table is nearly twice that width. (And this magazine is even narrower, making it even worse.) Luckily, when our program retrieves a row, it does not have to worry about the formatting.

If you are interested only in certain columns, you can specify a subset of the entire row, as follows:

select sender_name,graphic_name,postcard_text
 from postcards where id_number = 12345;

Submitting this query produces the result shown in Table 3. The output row contains only the row corresponding to our postcard, and the columns we have requested. As an added benefit, it is easier to read than the entire row that we retrieved earlier.

Table 3. Output of select Query

Now that we have seen the sort of SQL query that is necessary to retrieve information from the database, it should be a snap to write our CGI program. You can see an initial stab in Listing 1.

This version of show-postcard.pl expects to be invoked with a single argument (the postcard's ID number) in the query string, as we mentioned earlier. If we go to the URL /cgi-bin/show-postcard.pl?12345, we should see a postcard addressed to Bill Clinton, with the message that we inserted by hand at the MySQL prompt.

The program works in a relatively straightforward way. First, it creates an instance of CGI, a Perl object (available from CPAN at http://www.perl.com/CPAN/) that makes CGI programming easier. After sending a MIME header indicating that the program will send its output in HTML-formatted text, we retrieve the value of the query string by using the param method, as follows:

my $id = $query->param("keywords");

If the query string is empty, we print out an error message and give the user another chance to enter a postcard ID number, by using the little-known <isindex> tag. Note that we check the numeric value of $id, by using the == operator, rather than simply checking to see if $id is equal (with “eq”) to the null string. This prevents problems if someone invokes show-postcard.pl with an argument that includes characters other than numbers.

Assuming that a number does arrive in the query string, we connect to the test database, and then build up our SQL command in the variable $command. We could, of course, simply insert the command string inside of the call to $dbh->query. However, building the command in a separate string makes it easier to understand. It also has the added benefit of allowing us to debug the program. We can print the literal query by uncommenting the following debugging line:

# Uncomment for debugging
# print "<P>SQL command: \"$command\
        "</P>\n";

When the above line is uncommented, we can see exactly what is being passed to MySQL and find our program's problems more easily.

Our program then sends the request to MySQL using the “query” method. The value returned is a handle into the database, which can contain one or more rows matching our query. Since we are requesting all rows matching a particular ID, we can be sure that no more than one row is returned. This is true because we set id_number to be a primary key, which makes it unique.

What happens if the user invokes show-postcard.pl with an ID number that does not correspond to any postcard? If we were to ignore this possible error, users entering nonexistent ID numbers would see the outline of a postcard, but no actual content. This isn't particularly friendly for our users, who would like to know when they make a mistake and to be given a chance to correct it. Thus, before we retrieve information from the returned row, we make sure that there was a returned row. If not, then we can safely assume that this is because the ID number submitted by the user did not match the id_number column for any row in the database.

Our code accomplishes this checking by using the “numrows” method on the statement handle ($sth)--the object which allows us to read the results of our query. If numrows equals 0, we have not received any rows from the server, and we complain to the user that he has entered an ID number which does not match any on our server.

Obviously, to be displayed, the graphic with the specified name must exist in /tmp (or whichever directory you name in the final part of the program) on the server. If the graphic's name is misspelled or if it is placed in the wrong directory on the server, the program will not be able to display it, so take care. (We will take a closer look at this problem next month, when we look at the use of multiple tables for the graphic.)

Finally, show-postcard.pl turns the row into a Web page that can be sent to the user. Indeed, since the “postcard” is really a Web page, you could argue that, while I have talked about this project as if it were useful only for sending postcards, you could easily adapt this strategy for creating personalized home pages on your system, with each user getting a different page.