Creating a Web-based BBS, Part 2

Mr. Lerner continues to look at the bulletin board system, examining the code that works with individual messages.
Working with Messages

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.

Posting the Message

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.

Viewing a Thread

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";
}
______________________

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState