Conducting a Poll | MySQL and Perl for the Web

only for RuBoard - do not distribute or recompile

In this section, we ll take a look at polling, a common activity on the Web:

News organizations often conduct polls on current events: Which candidate do you plan to vote for? How would you assess the president s handling of foreign policy? Is the economy in good shape?
Sports sites ask for predictions: Who will win the World Series? the Super Bowl? the World Cup? Who s the best athlete of the last decade? Do you think the designated hitter rule should be eliminated?
Polls enable you to gather feedback about your site or your organization: How easy was it to find what you needed on our Web site? How would you grade our customer service? Do you think our news coverage is objective or biased?
Ratings are a natural use for polls: How would you rate this restaurant? How much did you enjoy this movie?

Most polls have a fairly standard format: Pose a question to the user and present a set of answers from which to choose, along with a submission button for casting the vote. Poll applications also commonly show the user the current results after a vote is cast. Sometimes a link is provided on the voting form that goes directly to the results page, for users who prefer not to vote or who have voted in the past and just want to see where the vote stands now.

As a simple poll, we ll ask people to vote for their favorite Groundhog s Day celebrity using a script groundhog.pl. The two prime candidates are Jimmy the Groundhog in Sun Prairie, Wisconsin, and Punxsutawney Phil in Punxsutawney, Pennsylvania. (This is about the most basic poll you can have there are only two choices.) The poll script will handle the following operations:

When first invoked, the script displays a page containing a form that presents the candidate groundhogs using a set of radio buttons, and a button for casting the vote. The page also includes a see current results link, in case the user wants to see the vote totals without actually submitting a vote.
After the user submits a vote, the script adds it to the lucky groundhog s tally, thanks the user for voting, and displays the current results.
If the user selects the current results link rather than casting a vote, the script just displays the results.

The main logic for the groundhog.pl script is as follows:

 my %groundhog_map =     # hash that maps groundhog names to labels  (     "jimmy" => "Jimmy the groundhog (Sun Prairie, Wisconsin)",      "phil" => "Punxsutawney Phil (Punxsutawney, Pennsylvania)"  );  print header (),      start_html (-title => "Vote for Your Favorite Groundhog",                  -bgcolor => "white");  # Dispatch to proper action based on user selection  my $choice = lc (param ("choice")); # get choice, lowercased  if ($choice eq "")                  # initial script invocation  {     display_poll_form (\%groundhog_map);  }  elsif ($choice eq "submit")         # tally vote, show current results  {     process_vote (\%groundhog_map, 1, param ("name"));  }  elsif ($choice eq "results")        # just show current results  {     process_vote (\%groundhog_map, 0);  }  else  {     print p (escapeHTML ("Logic error, unknown choice: $choice"));  }  print end_html ();

In a sense, this part of the script is similar to the corresponding parts of the prod_reg.pl and giveaway.pl scripts: It encodes information about the form in a data structure that it passes to the form-generation and form-processing functions. However, the data structure is different because we re going to present a set of radio buttons, not a set of text-input fields. Accordingly, we set up a hash map that associates groundhog names with descriptive labels. We use the names as the values of our radio buttons on the polling form and in the database table used to store the votes. The descriptive labels are more meaningful for users and are used for display in the polling form and results pages.

The map gets passed to each function that needs it (display_poll_form() and process_vote()). The process_vote() routine has a dual purpose; it handles the cases when the user submits a vote or just selects the see current results link. The second argument indicates whether a vote is expected; if so, a third argument contains the selected groundhog name.

display_poll_form() presents a form containing a radio button for each groundhog and a Submit button:

 sub display_poll_form  { my $map_ref = shift;    # groundhog name/label map      print start_form (-action => url ()),          p ("Which groundhog is your favorite?"),    # pose question          radio_group (-name => "name",                      -values => [ sort (keys (%{$map_ref})) ],                      -default => "[NO DEFAULT]",                      -override => 1,                      -labels => $map_ref,                      -linebreak => 1),   # display buttons vertically

         br (),          submit (-name => "choice", -value => "Submit"),          end_form ();      # add link allowing user to see current results without voting      print hr (),          a ({-href => url () . "?choice=results"}, "See current results");  }

$map_ref refers to the hash that associates the groundhog names with their descriptive labels. This hash can be used as is to supply the labels parameter to the radio_group() function, but we also need to extract the names for the values parameter. However, values requires a reference to an array, not an array, so we can t pass the list of names directly:

 -values => sort (keys (%{$map_ref}))        # incorrect

Putting the list inside [ ] creates a reference:

 -values => [ sort ((keys (%{$map_ref})) ]   # correct

The default value for the radio buttons is chosen explicitly not to be equal to either of the groundhog names. This causes the form to be displayed with no button selected, to avoid swaying the vote. Setting the linebreak parameter to non-zero causes the buttons to display vertically.

Of course, we re jumping ahead of ourselves here a little bit. What do we do with a vote when it s submitted? We need a table in which to store the vote counts for each candidate. Here s a simple table to hold groundhog names and vote counters:

 CREATE TABLE groundhog  (     name    CHAR(10) NOT NULL,              /* groundhog name */      tally   INT UNSIGNED NOT NULL DEFAULT 0 /* number of votes */  )

Initializing the table is trivial; all we need is an INSERT statement that adds rows naming each groundhog (we need not set the tally column explicitly because its default value is zero):

 INSERT INTO groundhog (name) VALUES ('jimmy'), ('phil')

Now that we have a table, we can use it to tally votes and display results. The process_vote() function takes care of this. Its $tally_vote argument indicates whether to tally a vote or just display results. However, we need to check whether a vote actually was submitted even if $tally_vote is non-zero. (The voting form comes up with no radio button selected; if the user just selects the Submit button, the form s name parameter that contains the name of the selected groundhog will be empty.) Therefore, we update the current tally for the appropriate groundhog and thank the user for voting only if we find a legal vote. Then we display the current results:

 sub process_vote  { my ($map_ref, $tally_vote, $name) = @_;  my ($dbh, $rs_ref, $row_ref, $sum, @table_row);      $dbh = WebDB::connect ();      if ($tally_vote)      {         # make sure name was given and that it's one of the legal names          if (defined ($name) && defined ($map_ref->{$name}))          {             $dbh->do ("UPDATE groundhog SET tally = tally + 1 WHERE name = ?",                          undef, $name);              print p ("Thank you for voting!");          }          else          {             print p ("No vote was cast; did you make a choice?");          }      }      print p (" The current results are:");      # retrieve result set as a reference to a matrix of names and tallies      $rs_ref = $dbh->selectall_arrayref ("SELECT name, tally FROM groundhog");      $dbh->disconnect ();      # compute sum of vote tallies      $sum = 0;      map { $sum += $_->[1] } @{$rs_ref};      if ($sum == 0) # no results!      {         print p ("No votes have been cast yet");          return;      }      # Construct table of results: header line first, then contents.      # For each groundhog, show votes as a tally and as a percentage      # of the total number of votes.  Right-justify numeric values.      push (@table_row, Tr (th ("Groundhog"), th ("Votes"), th ("Percent")));      foreach $row_ref (@{$rs_ref})      {         my $label = $map_ref->{$row_ref->[0]};  # map name to descriptive label          my $percent = sprintf ("%d%%", (100 * $row_ref->[1]) / $sum);          push (@table_row, Tr (                 td (escapeHTML ($label)),                  td ({-align => "right"}, $row_ref->[1]),    # tally                  td ({-align => "right"}, $percent)          # % of total              ));      }      print table (@table_row);  }

To display the current vote totals, we run a query to retrieve the names and vote tallies from the groundhog table. selectall_arrayref() returns a result set as a reference to a matrix (specifically, as a reference to an array, each element of which is a reference to an array containing one row from the table).^[11] After retrieving the result set, we sum the tally values to determine the total number of votes cast. This has two purposes. First, we can tell from the total whether any votes have been cast yet. Second, the total allows us to calculate the percentage of votes each groundhog has received when we generate the rows of the vote display table. The name-to-label map gives us the descriptive label from the groundhog name, which is HTML-encoded using escapeHTML() in case it contains any special characters. (In fact, neither of the labels do, but this approach prevents surprises if we decide to change the labels at a later date.) No encoding is needed for the tally or the percentage values because we know they re numeric and therefore contain no special characters.

^[11] The selectall_arrayref() function is useful here to get the entire result set into a data structure because we need to iterate through the result set twice. If we used a row-at-a-time fetch loop, we d have to run the query twice to process the results twice.

Suggested Modifications

Our poll just counts votes. It doesn t tell you when votes were cast. Modify the application to log each vote and when it occurred so that you can perform time-based analysis of poll activity. Write some summary queries that show the number of votes cast each day (week, month, and so on) that your poll is open.

Suppose you hear about another famous groundhog that lives in the city of Bangor, Maine. Consider what you d need to do to change the set of candidates presented by the poll:

The current vote counters should be set back to zero to eliminate any head start by the existing candidates.
You d need another row in the groundhog table for the new candidate.
You d need to add another entry to the hash map that associates ground hog names and descriptive labels.

That s not actually too much work. But now suppose you want to conduct a second poll. The groundhog.pl script is adequate if groundhog voting is the only poll you ll ever run, but it has some shortcomings for multiple poll presentations. Using our present poll implementation, you d need to write a script specifically for each poll you want to carry out. You d also need a separate vote-tallying table for each one. These problems arise because the script is intimately tied to knowledge about this particular poll. It knows precisely what the choices are, what labels should be associated with the names, and the title for the poll form. In Chapter 6, we ll consider a more general polling implementation that eliminates these shortcomings. It s more work to implement, but more flexible.

only for RuBoard - do not distribute or recompile