Consumer Rankings

How to use CGI programs to allow list subscribers to enter and rank their favorite products or services.
Creating the Ranking Form

Once one or more categories have been added to RankCategories, users can begin to rank individual items. This is accomplished with Listing 2, (see Resources), which produces an HTML form so that users can rank an item. Users have the option of ranking something already in the database, or adding a new item inside an existing category.

The form must be created by a program, rather than written as a static document, because it displays pop-up menus of catalogs and items from the database tables. When it is first invoked, retrieves the items and categories from RankItems and RankCategories, respectively, putting them into hashes for easy retrieval.

DBI makes it relatively easy to retrieve a table into a hash with its fetchrow_hashref method. However, the documentation explicitly states that fetchrow_hashref is not very efficient, so we use fetchrow_arrayref, retrieving each row as its own array reference and storing the information in a hash:

$sql  = "SELECT category_id, category_name ";
$sql .= "FROM RankCategories ";
$sth = $dbh->prepare($sql)
   || die "Cannot prepare: $DBI::errstr";
$result = $sth->execute
   || die "Cannot execute: $DBI::errstr";
my %categories = ();
while (my $row = $sth->fetchrow_arrayref)
   my ($id, $name) = @$row;
   $categories{$id} = $name;

The “existing item” and “new item” sections of the form both use the popup_menu function provided by for the creation of <select> lists. A pop-up menu has a name and a set of potential values, each of which can be optionally associated with descriptive text. For example:

<select name="number">
   <option value="1">one
   <option value="2" selected> two
   <option value="3">three
The above HTML creates a three-element pop-up menu named “number”. This menu can pass one of three options (1, 2 or 3) to a CGI program. However, the user never sees the values; instead, the menu is labeled with English words corresponding to the values. While the user can select and submit any of the menu's values, the two element is selected by default.

Given that the existing items are placed in the %items hash (just as the above code places existing categories in the %categories hash), we can create a pop-up menu with the following code:

popup_menu(-name => "existing_item_id",
   -values => [0, (sort {
   $items{$a} cmp $items{$b}} keys %items)],
      -labels => {0 => "Choose one",
      map {($_, $items{$_})} keys %items},
   -default => $existing_item_id,
   -override => 1),

popup_menu returns a text string, appropriate for passing to print or saving to disk (if the program is creating a file of HTML-formatted text). The name parameter sets the name attribute, and the default parameter indicates which attribute will be selected by default.

The values parameter takes an array reference as an argument. In this case, the values should be the keys of %items (i.e., the primary keys from RankItems), but sorted in the order of each key's item_name value. The solution is to sort keys %items by value, rather than by key. Adding a 0-value element to the front of the values parameter means the first option will always have a value of 0. MySQL does not use 0 in AUTO_INCREMENT columns, and Perl sees 0 as false—so setting the default value to 0 will never collide with an actual value of item_id, and can easily be identified when passed to our program via POST.

By default, popup_menu will set the descriptive text for each <option> to the value itself. However, the labels parameter makes it possible to assign custom labels to some or all values. The labels parameter takes a hash reference as input. Since a hash in list context turns into a comma-separated list of keys and values, we can stick %items into the hash reference, preceded by a mapping from 0 to the text “Choose one”.

Unfortunately, HTML does not support hierarchical pop-up menus. It would be easiest and best for the user if we could use popup_menu to provide a menu of categories that would lead to a list of associated items. Given the choice between requiring a second program invocation (as we did with, described below) and producing a flat list of items, I chose the latter. Another approach is to use the method pioneered by Yahoo! and used by Epinions, in which each category hyperlink is followed by links to the most popular individual items. Implementing such an approach would require some changes to (and presumably, but not to the underlying database.