Integrating SQL with CGI, Part 1

Last month, we began our exploration of integrating relational databases into our CGI programs. CGI programs often have to save and retrieve information. They typically do this using text files on the server's file system. By using a relational database, however, we can make our programs flexible, powerful and robust, while at the same time reducing the amount of code that we have to write.

Like the Web, relational databases use the client-server model, dividing the world into database clients (which make requests) and servers (which respond to those requests). Requests are typically written in SQL (Structured Query Language), which can be embedded within programs.

This month, we look at a simple project that uses a relational database to allow users to send electronic postcards to each other. Next month, we will spend more time on this project, improving on our original database design and making the program even more useful.

Over the last few years, “postcard” web sites have become increasingly common. These sites, which often appear just before a major event or holiday, allow people to send electronic postcards to their friends and family.

One way to accomplish this task is to e-mail the postcard, perhaps as a MIME attachment. This is relatively easy to do and makes the system relatively simple. However, such a system requires us to send the entire postcard, an operation which can use up a great deal of bandwidth if our site gets a large number of visitors.

In addition, many users still have to pay for their Internet connection and might not wish to be sent an unsolicited 100 kilobyte mail message, even if it does contain a beautiful picture and warm wishes. This can be a particular problem in the modern age of spamming, when people send each other large quantities of mail without regard for the fact that the recipient might end up having to pay for telephone and networking charges.

Add to that the fact that not everyone uses a MIME-compliant mail reader, and it becomes clear that at least for the time being, sending large, sophisticated e-mail messages is not a good way to make friends.

Thus, we will use a different approach. The postcards will be housed in a database on our server and will be accessible via a CGI program. When a postcard is created, a short e-mail message will be sent to the recipient, indicating the URL from which she can retrieve the postcard, using the web browser to display any graphics.

We will write two CGI programs: one to create the postcard and send the notification via e-mail, another to allow the recipient to retrieve the postcard.

Before we can write our programs, we need to create a table in our relational database. For the purposes of demonstration, I'm using MySQL 6.3, a SQL database that runs nicely on my Red Hat Linux system. You can get more information about MySQL at http://www.tcx.se/.

In order to create the database tables, we need to decide what information we wish to store about each of the postcards. And in order to do that, we need to know how we wish the postcards to look.

Let's assume that the postcards are web pages constructed on the fly by a visitor to our site. A specific postcard is created when a CGI program receives a URL containing a unique identifier following a question mark, known in Web lingo as the “query string”. Thus, one postcard would be available via the URL http://www.oursitename.com/cgi-bin/show-postcard.pl?12345, while another postcard would be available via the URL http://www.oursitename.com/cgi-bin/show-postcard.pl?67890.

The CGI program show-postcard.pl takes the value of the query string (which, in the above examples, is either 12345 or 67890) and uses it as an index in our database table. That table contains a short message from the sender, as well as a picture of the sender's choice.

In order to make this work, we need a table with seven columns:

To avoid dealing with MySQL's security system, we put all of our table information in the database named “test”. We can enter the MySQL command-line interface by entering the following on the command line:

mysql test

On typing that command on my system, I was greeted with the following message:

Welcome to the mysql monitor. Commands ends with ; or \g.
Type 'help' for help.
mysql>

Just as in C and other programming languages with typed variables, columns in an SQL table must have a data type associated with them. The main data types we will use are mediumint (a medium-size integer), varchar (a string of variable length) and blob (an untyped storage element that accepts large amounts of data).

First, we'll create a table with seven columns, one for each of the pieces of data we wish to track, using the following SQL query:

create table postcards (
        id_number mediumint not null primary key,
        sender_name varchar(60) not null,
        sender_email varchar(50) not null,
        recipient_name varchar(60) not null,
        recipient_email varchar(50) not null,
        graphic_name varchar(100) not null,
        postcard_text blob);

When I enter this at the MySQL prompt, I get the following output:

mysql>     create table postcards (
   ->   id_number mediumint not null primary key,
   ->   sender_name varchar(60) not null,
   ->   sender_email varchar(50) not null,
   ->   recipient_name varchar(60) not null,
   ->   recipient_email varchar(50) not null,
   ->   graphic_name varchar(100) not null,
   ->   postcard_text blob);
Query OK, 0 rows affected (0.02 sec)

The first column, id_number, will be used to identify a particular postcard. In order to ensure that only one postcard has this particular ID number, we create the id_number column with the keywords “primary key”, which is another way of saying that its value must be unique across all rows. By making id_number a primary key, we can retrieve postcards by asking for all rows with a particular value of id_number—the result will be either a single row (with a matching value of id_number) or no rows at all (indicating that no postcard exists with that ID number).

The second through sixth columns are of type varchar, which simply means that their lengths vary as necessary, up to the maximum number of characters indicated in the parentheses. I chose these numbers somewhat arbitrarily; if you suspect that e-mail addresses will always be fewer than 50 characters, then you might wish to shorten the appropriate fields. By the same token, if you suspect that users might have names longer than 60 characters, you should extend the lengths of these fields.

The sixth column, graphic_name, contains the pathname of the graphic to be inserted in the postcard.

The final column, defined as type “blob”, contains the text that the sender wishes to send to the recipient. Because this message is optional, we have indicated that this column can contain a null value. MySQL allows us to enter a value in postcard_text, but if we fail to provide such a value, the column will simply contain a null value.

For a summary of our table, we can use the MySQL describe command:

mysql> describe postcards;

The output of this command is shown in Table 1.

Table 1. Output of describe Command