Consumer Rankings

Now that we have seen how the data will be stored, we will write several programs that allow users to enter information into the tables. The interface presented here might seem a bit primitive, but this doesn't really matter. Since the information is stored in a database, we can always write new programs to improve or change the interface.

Since each RankItem must be placed in a category, we must first write a program that adds new categories to the system. Listing 1, rank-category.pl (see Resources), is a simple example of such a program.

When invoked with the GET method, such as when a user enters a URL into a browser, rank-category.pl produces an HTML form into which details about a site can be entered. The form is submitted to rank-category.pl which handles the data submitted with POST, adding a new row into the RankCategories table. After the new row is added to the database, rank-category.pl displays the HTML form once again. This makes it relatively easy to add multiple categories.

rank-category.pl, as well as the other programs presented this month, use CGI.pm, the standard Perl module (written by Lincoln Stein) for working with CGI programs. We also import a number of subroutines into the current name space (grouped together with the :standard tag), making it easier to produce HTML output. The p function, for example, surrounds text with <P> and </P> tags. Thus the following:

print p("Hello");

is the same as

print "<P>Hello</P>";

but is more readable. In addition, CGI.pm's HTML subroutines can work on multiple strings and can be nested:

print p("This will be in", b("bold"), "type");

CGI.pm automatically inserts whitespace between arguments to these subroutines, so there is no need to put spaces at the end or beginning of quoted strings.

To set HTML tag attributes, pass a hash reference to the subroutine as the first argument. For example, the following code comes from rank-category.pl (Listing 1):

 print p("Now go ",
   a({-href=>'/cgi-bin/rank-something.pl'},
       "rank something"), "!");

The above code puts the text “rank-something” inside of a hyperlink pointing to /cgi-bin/rank-something.pl, producing the same HTML as the following, without having to worry about quotes or whitespace:

print qq{<P>Now go
<a href="/cgi-bin/rank-something.pl">
rank something</a>!</P>

By default, CGI.pm does not insert whitespace between HTML tags generated using these routines. This might be more efficient for the computers, but makes it difficult to examine and debug the HTML output. We thus use CGI::Pretty, a subclass of CGI.pm which inserts appropriate whitespace between the tags. (If your system does not support CGI::Pretty, use CGI.pm instead. The output will not be formatted as nicely, but will still work.)

Regardless of how it is invoked, rank-category.pl always produces an HTML form consisting of two text fields, new_category_name and new_category_description. These are submitted back to rank-category.pl, which inserts the information into the database.

The program's connection to the database is managed by DBI, the Perl database interface available from CPAN (the comprehensive Perl archive network, at http://www.cpan.org/). DBI provides a generic API to a relational database, in conjunction with a specific driver (DBD) for each type of database server. DBDs are available for most popular brands of database server and make it possible to port Perl programs across platforms with a minimum of difficulty.

Assuming that new_category_name and new_category_description contain valid data, we insert a new row into the RankCategories table by building up an SQL query. Here is the Perl code that performs this task:

my $sql  = "INSERT INTO RankCategories ";
 $sql .= "(category_name, category_description) ";
 $sql .= "VALUES (?,?) ";
my $sth = $dbh->prepare($sql)
   || die "Cannot prepare: $DBI::errstr";
my $success =
   $sth“>execute($new_category_name,
       $new_category_description);

Notice how $sql contains question marks (?) rather than actual data. These values are set in $sth->execute, with the first argument ($new_category_name) being assigned to the first place holder and the second argument ($new_category_description) assigned to the second.

rank-category.pl does not benefit in an obvious way from the use of place holders, which speed up multiple invocations of the same SQL query, with minor variations. However, using place holders also makes it possible to avoid problems that can arise when interpolated variable values contain ' and " characters. DBI handles and quotes them automatically, making the program more readable and eliminating the possibility of these sorts of errors.