Consumer Rankings

How to use CGI programs to allow list subscribers to enter and rank their favorite products or services.
Inserting a New Ranking

When is invoked with the POST method, it looks for a new item that it will need to insert into RankItems. The actual insertion is rather straightforward, using DBI's prepare and execute methods, as we have seen before:

$sql  = "INSERT INTO RankItems ";
$sql .= "(category_id, item_name, item_description) ";
$sql .= "VALUES (?,?,?) ";
$sth = $dbh->prepare($sql)
   || die "Cannot prepare: $DBI::errstr";
$success =
   $sth->execute($item_category, $item_name,

If the execution is unsuccessful, $success will be set to a false value. We can trap errors this way, producing an error message like the following:

unless ($success)
 print h1("Error inserting new item");
 print p(
 "There was an error inserting the item:".
 print p(
 "Perhaps this item already exists in the".
We can now be sure the item to be ranked is in RankItems. If the item was already in RankItems, then we knew its primary key from the <select> list in the HTML form. However, how can we retrieve the primary key of the new item just inserted? The DBI driver for MySQL provides an attribute, called mysql_insertid, which returns the primary key of the most recently inserted row. We can retrieve this value and store it in $item_id, which otherwise would get its value from the pop-up menu:
$item_id = $dbh->{"mysql_insertid"};
As we saw earlier, each ranking consists of an integer between 0 and 10 (from worst to best), as well as user comments about the item. In order to avoid potential formatting problems, I decided to remove all HTML tags from the comments. Using Perl's non-greedy regular expressions, this is an easy task:
$comments =~ s|<.*?>||g;
The above substitutes all occurrences of <, followed by zero or more characters, followed by >, with the empty string. The |g modifier at the end of the s||| operator performs this operation globally.

Next, we handle paragraph separators. We want people to be able to enter more than one paragraph, but cannot let them use <P> and </P> tags to do so. The solution is to treat every occurrence of more than one \r (carriage return) or \n (newline) character as a paragraph separator. UNIX machines rarely produce \r characters in text input, but DOS/Windows systems end lines with a combination of \r\n, and Macintoshes use a single \r. The following turns any two (or more) of these into a <br> tag, followed by two newlines:

$comments =~ s|[\r\n]{2,}|<br>\n\n|g;

Finally, we insert the new ranking into the Rankings table:

$sql  = "INSERT INTO Rankings ";
$sql .= "(item_id, ranker_name, email, comments, rank) ";
$sql .= "VALUES (?,?,?,?,?) ";
$sth = $dbh->prepare($sql)
   || die "Cannot prepare: $DBI::errstr";
$success =
   $sth->execute($item_id, $ranker_name, $email,
   $comments, $rank);
Once again, we use question marks (?) as place holders, potentially speeding up multiple queries and removing the need for us to explicitly quote the individual items.

If the INSERT is successful, the user is given a short message:

print p("Your ranking was successfully entered.");

Following this message, the user is given the chance to rank another item in the database or view the current rankings database by clicking on a hyperlink.

Retrieving Data

Finally, once data has been entered into Rankings, we can write Listing 3, (see Resources), a program that lets us look through the rankings and read them. There are many ways to present the information, and I took the easy way in this version of the program, forcing users to go through two menus (one of categories and a second of items in that category) before seeing the list of rankings for a particular item. Once again, we use the GET/POST trick to write a program that both creates a form and accepts its data.

If is invoked with GET, it creates a simple HTML form with the categories in a pop-up menu:

print $query->start_html(-title =>
   "Choose a category");
print h1("Choose a category");
print startform(-method => "POST",
   -action => $query->url);
print p("Select a category to view:",
   popup_menu(-name => 'category_id',
   -values =>
   [sort {$categories{$a} cmp $categories{$b}}
          keys %categories],
   -override => 1,
   -labels => \%categories));
print submit(-value =>
   'View items in this category');
print endform;

If is invoked with POST, it checks to see whether category_id was set. If so, then it assumes the invoking form was the above, and displays a list of items within that category:

$sql  = "SELECT C.category_name, I.item_name, ";
$sql .= "       I.item_description, AVG(R.rank)";
$sql .= "FROM RankItems I, RankCategories C, ";
$sql .= "     Rankings R ";
$sql .= "WHERE I.category_id = C.category_id ";
$sql .= "AND   I.item_id = $item_id ";
$sql .= "AND   I.item_id = R.item_id ";
$sql .= "GROUP BY I.item_id = R.item_id ";
$sth = $dbh->prepare($sql)
   || die "Cannot prepare: $DBI::errstr";
$result = $sth->execute
   || die "Cannot execute: $DBI::errstr";
This seemingly complex SQL query retrieves information from all three tables, getting the category name from RankCategories and information on this item from RankItems. The AVG function returns the average value from all returned rows, making it possible to get a sense of where the item should truly be ranked. Of course, there is no way to stop a malicious user (or the owner of a ranked business) from trying to skew the scales in a particular direction, so it is just as important for users to read the comments and the individual rankings as the average score.

Once we have retrieved general information on the item, we perform a second SELECT, requesting all rows for this item in chronological order:

$sql  = "SELECT ranker_name, email, comments, rank ";
$sql .= "FROM Rankings ";
$sql .= "WHERE item_id = $item_id ";
$sql .= "ORDER BY entry_datetime ";

The results of this query are then printed for the user:

while (my $row_ref = $sth->fetchrow_arrayref)
  my ($name, $email, $comments, $rank) =
print p(dt(a({href => "mailto:$email"},
  $name), "*" x $rank, " ($rank)"),
HTML's <dt> and <dd> tags are perfect for formatting these sorts of comments, handling the indentation automatically. Perl's x operator, which multiplies a text string, makes it simple to produce the correct number of stars associated with a particular review.