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 rank-something.pl 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,
   $item_description);

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:".
 $DBI::errstr");
 print p(
 "Perhaps this item already exists in the".
 database?");
 exit;
 }
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, view-ranking.pl (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 view-ranking.pl 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 view-ranking.pl 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) =
      @$row_ref;
print p(dt(a({href => "mailto:$email"},
  $name), "*" x $rank, " ($rank)"),
dd($comments));
}
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.

______________________

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