Last month, I demonstrated how to build a small bulletin-board system (BBS) on the Web using Perl and a relational database. Such a bulletin board is another useful tool for bringing people together on a web site. This month, I will show you how to write several different programs, including ones that create and list the current messages.

Before continuing, let's review the two tables that contain the information in our BBS. I used SQL to define the tables, which means that while I wrote the database using MySQL, most of these definitions should work with other relational databases as well. The code to create these two tables is shown in Listing 1.

Listing 1. Table Creation Code

These two tables will enable a number of tricks to be performed with threads (i.e., message groups) and messages. Each message belongs to one thread. Each message (and thread) is contained in a single database row includes the author's name, her e-mail address, a subject heading and the text of the message.

Pointers to additional information about relational databases in general or MySQL in particular can be found in “Resources”, which also includes information about Perl's vendor-independent database interface (DBI).

Last month I wrote a program, list-threads.pl, to list the threads (discussion topics) currently defined, and one to create a new thread, add-thread.pl. However, I didn't provide an HTML form for use with add-thread.pl, because that form must be produced within a CGI program. The program that performs this function is add-thread-form.pl in Listing 2 in the archive file. Listings 2 through 5 are not printed here due to space considerations, but are available by anonymous download in the file ftp.linuxjournal.com/pub/lj/listings/issue58/3252.tgz.

Why use a CGI program rather than a static form? To be honest, the only reason is to provide a bit of functionality I particularly like. I order many items on the Web, often returning to the same vendor multiple times. I dislike having to enter my name and e-mail address every time I fill out an HTML form on the Web. I decided to make life a bit easier for people using our bulletin board system by automatically filling in the “name” and “email” fields with information the user had posted in a previous transaction.

If I were to use a templating system such as Embperl or ePerl, I could use a file that looks closer to standard HTML without burying the HTML inside of a CGI program. For a variety of reasons, including the fact that my web-space provider had not made mod_perl available as of this writing, I decided to use CGI programs rather than templates.

Regardless of whether CGI programs or templates are used, inserting a value from a previous form submission requires keeping track of state across HTTP transactions. HTTP is a stateless protocol; that is, each connection occurs without any memory from previous ones. How, then, can data be retrieved from a previous form submission?

The answer is HTTP cookies, a clever hack that has become a cornerstone of commerce and transaction on the Web. A cookie is a name,value pair, somewhat like a variable or an entry in a hash table. The cookie is stored by the user's browser, however, meaning that it is available across multiple transactions.

A site can set a cookie as part of an HTTP response, with a “Set-cookie” header. Whenever the user visits a site that previously set a cookie, it includes a “Cookie” header in its HTTP request. Thus, the cookie's value can be used to automatically fill in the “value” attribute of the “name” and “email” fields in the HTML form. When the user submits the form to create a new thread, the program sends headers that set the “name” and “email” cookies on the user's browser. The next time the user visits the site, those values are sent as part of the HTTP headers and can be retrieved and used within our program.

Perl's CGI.pm module allows us to easily work with cookies, using the “cookie” method. The following code is put in the form-creation program:

my $email = $query->cookie(-name => "email") ||
     "";
print "<TR>\n";
print "<TD>Your e-mail address</TD>\n";
print "<TD><input type=\"text\"
size=\"50\" ";
print "value=\"$email\"
name=\"email\"></TD>\n";
print "</TR>\n\n";

which assigns $email the value of the “email” cookie or the empty string. The empty string could be ignored, since Perl automatically assigns the empty string to a variable the first time its value is retrieved. However, this would produce a warning message, since the program would be using the value of an undefined variable. It is safest to assign the empty string when possible.

The value of the text field is set to the current value of the cookie or the empty string; that is, either the user's e-mail address or nothing at all. A similar method is used for the user's name, so that she doesn't have to enter her name multiple times.

The form is submitted to add-thread.pl, which we examined last month. That program uses the elements of the submitted HTML form to create an SQL query that inserts an appropriate row into the ATFThreads table. Because we have defined the ID column of ATFThreads with the AUTO_INCREMENT attribute, we can be sure every thread will have its own automatically generated ID number that we can reference in our programs.

When the form is submitted, our CGI program creates two new cookies, one named “email” and another named “name”. We can then retrieve the values with CGI.pm's “cookie” method, as demonstrated above. Creating the cookies is almost as easy as retrieving them:

my $namecookie = $query->cookie(-name => "name",
  -value => $query->param("name"),
  -expires => "+1y");
my $emailcookie = $query->cookie(-name => "email",
  -value => $query->param("email"),
  -expires => "+1y");

Once we create $namecookie and $emailcookie, we can send them to the user's browser, thus setting the cookie values, by incorporating them into the HTTP header:

print $query->header(-type => "text/html",
 -cookie => [$namecookie, $emailcookie]);

Since both cookies are set to expire one year (+1y) after they are created, the user's browser should continue to send the name and e-mail address whenever visiting the site in the future.

Now that we have seen how the underlying database system will work for threads, we need to begin working with the actual messages. Because this is a simple system, we'll look only at posting a new message to a thread and viewing the contents of a thread.

In many ways, posting a new message to a thread is similar to creating a new thread. In both cases, the user's name and e-mail address are requested. In both cases, the date and time at which the thread was created is recorded, and the user can enter a title and a message body.

The major difference between messages and threads is that each message must be associated with a thread. This association is used to create the illusion that the messages are stored separately, when in fact they are all stored in the same table (ATFMessages). Users, however, will be able to see only a single “thread-wise slice” at a time.

Just as I used a program to create the thread-adding form, I will use a CGI program to create the message-posting form, called post-comment-form.pl (Listing 3 in the archive file). This form will submit its contents to post-comment.pl (Listing 4 in the archive file).

I will ensure that each message is associated with a thread by putting a selection list inside of the form. Each option in the selection list will be identified internally with the ID code for the thread in question and will display the subject line.

In order for this list to reflect the current status of the database, a database query is done and the results are displayed in the form. The query is set by:

my $sql =
"SELECT id,subject FROM ATFThreads ORDER BY subject";

and then executed, iterating through each id,subject pair. Each pair is inserted into an <option> tag, as we can see:

while (my @row = $sth->fetchrow)
{
print "<option value=\"$row[0]\" ";
print " selected " if ($thread_id == $row[0]);
print ">$row[1]\n";
}

The standard DBI $sth->fetchrow method is used to return the next row from the SELECT query. When no more rows remain to be retrieved, $sth->fetchrow returns false, which ends the while loop.

Also notice how a particular thread's subject can be selected by comparing its $thread_id with $row[0]. $thread_id is set to the value of the query string, which can be loosely defined as “anything following the question mark in a URL”. The line:

my $thread_id = $query->param("keywords") || 0;

causes CGI.pm to automatically assign the parameter keywords to the value of the query string. If the user invokes the program with http://www.lerner.co.il/cgi-bin/post-comment-form.pl?5, then $thread_id will be assigned the value 5. If the query string is not assigned, the value is left at 0, in which case no default thread is selected.

When the HTML form is submitted to post-message.pl (Listing 4 in the archive file), the form elements are used to insert a new row into ATFMessages. As I indicated above, post-message.pl is not very different from add-thread.pl, except that it stores a thread ID number along with all the other information:

my $sql = "INSERT INTO ATFMessages ";
$sql .= "(thread,date,author,email,subject,text)";
$sql .= "VALUES ";
$sql .= "($thread_id,NOW(),$name,$email,$subject,$text)";

The variable values can be inserted without surrounding them by quotes, because the standard $dbh->quote method was used. I discovered this method only recently and continue to be amazed that I was ever able to survive without it. Simultaneously, the form elements are retrieved and quoted appropriately in the following lines of code:

my $name = $dbh->quote($query->param("name"));
my $email = $dbh->quote($query->param("email"));
my $thread_id = $dbh->quote($query->param
("thread"));
my $subject = $dbh->quote($query->param
("subject"));
my $text = $dbh->quote($query->param("text"));

Once this is done, the above SQL query will INSERT a new row. We tell the user that the new message has been added and produce a menu bar with a number of options.

Believe it or not, these two short programs are all that is needed to insert a message into the database and thus into our BBS.

At this point, the functionality is close to complete. All that remains to be done is to create view-thread.pl (Listing 5 in the archive file), which allows us to look at the current contents of a thread.

For this program to work, a single argument must be passed in the query string to identify the thread. To retrieve this value, use the keywords HTML form element that CGI.pm creates:

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

Once $thread_id is assigned, I can retrieve the appropriate information from the tables about that thread. Indeed, two separate queries are done: one from ATFThreads and a second from ATFMessages. (I could have combined the queries into a single large SELECT statement, but I chose to keep them separate.)

Early on, I decided to print the date and time of the user's posting along with the text of the posting. Given the DATETIME data type, how can we retrieve the date and time in an intelligent way? MySQL provides a DATE_FORMAT function which takes the value from a column and writes the contents using a specified format.

To make life easier, I actually retrieve the same “date” column twice, once for the date and again for the time. This allows literal characters to be inserted between the date and time without having to worry about possible misinterpretation:

$sql = "SELECT id, DATE_FORMAT(date,
   \"%W, %d %b %Y\"), ";
$sql .= "DATE_FORMAT(date, \"%h:%i %p\"), ";
$sql .= "author, email, subject, text FROM ATFMessages ";
$sql .= "WHERE thread = $thread_id ORDER BY date desc";

DATE_FORMAT takes two arguments: the name of the column to retrieve and a set of codes (in the style of C's printf statement) indicating the values to use.

Once this query is executed, the code iterates through the results, printing the messages as they come—from newest to oldest. They will come in that order because of the ORDER BY clause in the SELECT statement. Allowing the database to do our dirty work for us means we can print all of the messages in a thread with just the following short loop:

while (my @row = $sth->fetchrow)
{
my ($id, $date, $time, $author, $email, $subject,
   $text) = @row;
print "<a name=\"$id\"><B>$subject</B>, ";
print "by <a href=\"mailto:$email\">$author</a> ";
print "on $date at $time</P*gt;\n";
print "<blockquote>$text</blockquote>\n\n";
}

The basic BBS software is now finished. It can create threads, add a message to a thread and view the messages within a thread. If nothing else, this project shows how powerful a set of database tables can be when paired with some CGI programs.

Perhaps this system is too basic; it is lacking some functions that any good bulletin board (or any good web application) should handle. For instance, it would be nice to include the ability to search through the posted messages for a text string or regular expression to find messages relevant to a particular topic.

It would also be useful to provide some administrative functions, unavailable to the public at large, for handling things at a relatively high level. For example, we might want to reserve the ability to delete messages that are offensive or unrelated to the topic. We might even want to give this ability to certain other users, giving them “deputy moderator” status.

Next month, we will see how to provide these functions by adding just one or two new CGI programs. Meanwhile, you can see these programs in action at http://www.lerner.co.il/atf/, where the “At the Forge” BBS is already in use.

Resources