Automating Poll Processing

only for RuBoard - do not distribute or recompile

In the preceding chapter, we developed a script to conduct an online poll ( vote for your favorite groundhog ). After writing that script, we discussed several of its shortcomings, primarily having to do with the amount of information about the poll that was hardwired into the script: the number of choices, the labels to display for those choices, the internal names used in the vote tallying table, the poll title, and so forth. Putting all these things into the polling script makes it efficient, but also very special purpose. You might expect an application to be highly specialized and tailored to your particular requirements if it s something like a customized order-processing system, but online polls are fairly stereotyped. Specialization in a polling application is a limitation, because it forces you to write a different script for each poll.

If we remove some of this specialization by moving it out of the poll-processing code and into the database, we ll make the code more generalized and reusable for handling multiple polls. We can do quite a bit to parameterize poll form generation and processing. For example, why not store the candidate names and descriptive labels in the database? If we do that, we can dissociate the form-generation code from knowledge about the content of any specific poll. The code need not care what the poll is about, it need only understand the structure of the poll table. Then we should be able to create the form automatically and use the same code to conduct any number of polls.

To carry out this more general approach to poll administration, we need several kinds of information:

  • As before, we need to store the poll candidates in a vote tally table so that we can associate a counter with each candidate.

  • We also need to store some descriptive text for each candidate. This becomes the label displayed for that choice in the poll form. For the groundhog poll you conducted before, the labels for jimmy and phil would be Jimmy the Groundhog (Sun Prairie, Wisconsin) and Punxsutawney Phil (Punxsutawney, Pennsylvania).

  • The groundhog table that was used to store groundhog vote totals contained two rows, with each row storing one groundhog s name and vote tally. As such, it s useful only for one specific poll. To store the results of multiple polls, we ll need a more general table design. For example, we ll need to give each poll a name or number so we can tag each candidate with the appropriate poll identifier. Otherwise, we won t be able to tell which candidates are part of each poll.

  • The groundhog polling program knew the order in which to display the choices. A general-purpose polling routine won t have any such knowledge. We might use a heuristic like display choices in alphabetic order, but that won t always work. For example, polls that ask for ratings often list items using an order such as very good, good, average, poor, and very poor, which is not alphabetic. Similarly, polls that ask the question what political party are you affiliated with? typically list the major parties first and the minor parties last, which may not be alphabetic, either. To deal with this difficulty, we can assign sequence numbers to poll choices to determine the display order.

  • A poll contains other information that needs to be parameterized, such as the poll caption (that is, the question that appears in the form preceding the choices). Also, we might want to specify the text of the submission button, and perhaps the thank-you message to present to users after they ve voted. If we want to present a view past polls link, then we ll probably want to display them by date, so it ll be useful to include the date when the poll begins. In fact, an end date would also be useful so that we can specify a point beyond which votes will no longer be accepted or that we can use to expire old polls if we want.

It appears that we ll need to store some pieces of information that apply to the poll as a whole (the caption, submission button text, thank-you message, and dates), as well as some that apply only to individual poll candidates (the name, descriptive label, and vote tally). This can be handled by using two tables, which we can tie together using a poll ID number. The first table, poll, maintains the general information about each poll:

 CREATE TABLE poll  (     id           INT UNSIGNED NOT NULL AUTO_INCREMENT,   /* poll ID (unique) */      PRIMARY KEY (id),      name         VARCHAR(40) NOT NULL,        /* name for poll (also unique) */      UNIQUE (name),      title        VARCHAR(255) NOT NULL,       /* descriptive title */      question     VARCHAR(255) NOT NULL,       /* question posed by poll */      submit_title VARCHAR(20) DEFAULT NULL,    /* submit button title */      thank_you    VARCHAR(255) DEFAULT NULL,   /* thank-you message */      begin_date   DATETIME,                    /* begin/end dates of poll */      end_date     DATETIME DEFAULT NULL  ) 

The id value is an AUTO_INCREMENT column. MySQL assigns a unique number to this column whenever we create a new poll record. id values must be unique so we can refer unambiguously to specific polls. To make it easier to refer to polls by more meaningful labels, however, the poll table also includes a name column that should be set to a string value that provides a name for the poll. The name values also must be unique because we don t want two polls to have the same name.

The title and question columns provide the title to display for the poll and the question that potential voters are asked. submit_title is the label to use for the vote-submission button and thank_you is the message to display after a user submits a vote. Either of these can be NULL, in which case, we ll supply appropriate defaults ( Submit and Thank you for voting ).

The begin_date and end_date column values indicate when voting for the poll begins and ends. The begin_date column must be given a value, but end_date value can be NULL to indicate that the poll has an open end date. These two columns have type DATETIME. DATE might be good enough under some conditions, but if you have very short polls that last less than a day, DATE won t have sufficient granularity and DATETIME is better. (An example would be a poll conducted during a television show and that lasts only as long as the show, so that the results can be announced at the end of the broadcast.)

The poll_candidate table is a companion to the poll table; it contains information about each choice presented to voters:

 CREATE TABLE poll_candidate  (     id      INT NOT NULL,                       /* poll ID */      seq     TINYINT DEFAULT 0,                  /* display sequence number */      name    VARCHAR(30) NOT NULL,               /* internal candidate name */      label   VARCHAR(255) DEFAULT NULL,          /* label displayed to users */      tally   BIGINT UNSIGNED NOT NULL DEFAULT 0, /* current vote tally */      UNIQUE (id, name)  ) 

The id column identifies the poll table record with which each poll_ candidate record is associated. For any given id value, there will be one poll record and multiple poll_ candidate records.

name is the internal name used to identify each candidate (it becomes associated with a particular radio button) and label is the descriptive text displayed for the candidate. In the groundhog poll, for example, we could use jimmy as a name value and Jimmy the groundhog (Sun Prairie, Wisconsin) as a label value. If a label is the same as the name, we ll allow the label value to be NULL in the table to save space.

The seq column is used to solve the problem, in what order should poll items be displayed? For the candidates in a given poll, you should set the values of this column to appropriate sequential values to order them the way you want. You ll recognize this use of a sequence number column from the earlier section in this chapter on lookup tables. For the polling application, however, we ll add a small twist by retrieving records sorted by both sequence number and label, to allow easy display of candidates in alphabetic order. That way, you can set all the seq values for a set of candidates to the same value (such as zero); in effect, this causes the poll choices to be sorted by the labels.

The tally column records the vote count for a given poll candidate. It s a BIGINT to allow for polls where you expect a very large number of votes.

The poll_candidate table index is a unique multiple-part key on id and name. The index serves two purposes, one for inserting records and one for retrieving them. When we insert records, the index prevents creation of duplicate records that have the same name for a given poll, to ensure that each candidate name is unique. For retrieval, the index allows all records for a poll with a given id value to be retrieved quickly, even when we don t specify a name value. That s because id is a leftmost prefix of the index (see the following sidebar).

Leftmost Prefixes of Indexes

When you create a multiple-column index (or key), MySQL can use the index in a query if you specify values for all the index columns. However, the index may also be used if you specify values only for some of its columns, as long as they form some leading part of the key. This is called a leftmost prefix of the key. If you manufacture a medical product that is packaged in individually numbered vials that are grouped for sale into boxes, cases, and lots, for example, you might have a multiple-column index that comprises columns named lot, case, box, and vial. You gain the benefits of the index if you specify values for all four columns in a query. But the index is useful even if you specify just lot, case, and box, to find all vials in a box. In this case, you ve specified the leftmost three columns of the index. Other leftmost prefixes are lot and case, or just lot. However, the combination of lot and box does not form a leftmost prefix, because it skips the case column.

Under our revised layout for storing poll data, we can load the information needed for the groundhog poll using the following statements:

 INSERT INTO poll (name,title,question,begin_date)      VALUES('groundhog','Groundhog Poll','Who is your favorite groundhog? ,              NOW())  INSERT INTO poll_candidate (id,seq,name,label)      VALUES          (LAST_INSERT_ID(),1,'jimmy',              'Jimmy the groundhog (Sun Prairie, Wisconsin)'),          (LAST_INSERT_ID(),2,'phil',              'Punxsutawney Phil (Punxsutawney, Pennsylvania)') 

While we re at it, let s go ahead and load another poll, too. This one asks users to vote for their favorite composer:

 INSERT INTO poll (name,title,question,begin_date)      VALUES('composer','Composer Poll','Which composer do you like best?',              NOW())  INSERT INTO poll_candidate (id,seq,name,label)      VALUES          (LAST_INSERT_ID(),1,'jsbach','Johann Sebastian Bach'),          (LAST_INSERT_ID(),2,'beethoven','Ludwig Van Beethoven'),          (LAST_INSERT_ID(),3,'mozart','Wolfgang Amadeus Mozart'),          (LAST_INSERT_ID(),4,'wagner','Richard Wagner') 

For both of these polls, the poll table record has the begin_date column set to the current date and time using the NOW() function. The end_date column is not specified, so MySQL sets it to the default value of NULL, meaning the poll is still open. We ve also left the id column unspecified because we want MySQL to assign an ID automatically: id is an AUTO_INCREMENT column, so MySQL generates new unique ID values for us. Each time MySQL creates a new AUTO_INCREMENT value, it can be accessed by calling the LAST_INSERT_ID() function. Thus, by inserting each poll s poll_candidate records immediately after creating the corresponding poll record, we can get the proper id column value without even knowing what that value is. (This technique would not work if you inserted the two poll table records first and then the six poll_ candidate table records. In that case, all six poll_candidate records would be assigned the ID number of the second poll record.)

Here s a third poll that asks people to describe their head shape. It is set up a little differently than the other two polls because it relies on some of the conventions that we ll use. First, the candidate names will also be used as the labels displayed in the poll form, so we can leave the labels NULL. Second, the candidate seq values are unspecified, so MySQL will assign them the default value (zero); the result is that candidates will be displayed alphabetically in the poll form.

 INSERT INTO poll (name,title,question,begin_date)      VALUES('head','Head Shape Poll','What shape best describes your head?',              NOW())  INSERT INTO poll_candidate (id,name)      VALUES          (LAST_INSERT_ID(),'round'),          (LAST_INSERT_ID(),'egg'),          (LAST_INSERT_ID(),'block'),          (LAST_INSERT_ID(),'without form and void') 

Now that we have our poll tables set up, we need to write some code to use them. Let s put the code to manage poll processing into a separate library module and provide an object-oriented interface to it. Then we can create a poll as an object that we manipulate by calling object methods. The module needs to perform several tasks for us:

  • New poll object creation

  • Poll form generation

  • Vote tabulation

  • Poll result display

If we put this code into its own module, WebDB::Pol l, we can write a general-purpose polling script, poll.pl, that looks like this:

 #! /usr/bin/perl -w  # poll.pl - present a poll using the generalized tables.  use strict;  use lib qw(/usr/local/apache/lib/perl);  use CGI qw(:standard escapeHTML);  use WebDB;  use WebDB::Poll;  # Get poll name or ID number and set up poll object my $poll_name = param ("poll"); if (!defined ($poll_name)) {    print header(), p ("No poll name was specified");    exit (0); } my $dbh = WebDB::connect (); my $poll = WebDB::Poll->new ($dbh, $poll_name); if (!$poll) {    $dbh->disconnect ();    print header(),        p (escapeHTML ("No information for poll $poll_name was found"));    exit (0); } print header (),    start_html (-title => escapeHTML ($poll->{data}->{title}),                -bgcolor => "white"); # Dispatch to proper action based on user selection my $choice = lc (param ("choice")); # get choice, lowercased if ($choice eq "")                                      # initial invocation {    print $poll->generate_form ();  # display the voting form } elsif ($choice eq lc ($poll->{data}->{submit_title}))   # a vote was submitted {    print $poll->tally_vote (param ("candidate")); # tally vote    print $poll->text_result ();                   # show current results } elsif ($choice eq "results")                           # just show results {    print $poll->text_result (); } else {    print p (escapeHTML ("Logic error, unknown choice: $choice")); } $dbh->disconnect (); print end_html (); exit (0); 

The poll.pl script determines which poll to present based on the value of the poll parameter, which means that we can invoke it as follows to present each of the polls in our poll tables:

 http://www.snake.net/cgi-perl/poll.pl?poll=groundhog  http://www.snake.net/cgi-perl/poll.pl?poll=composer  http://www.snake.net/cgi-perl/poll.pl?poll=head 

Note that we can t print the page until we look up the poll information. (For example, we need to defer printing the page title until after we fetch poll data from the tables, because the title is stored in one of the tables.) Because of this, we ll write methods that generate HTML so they return results as strings. That allows the HTML to be printed immediately or saved for later, depending on an application s requirements.

If you compare poll.pl to our earlier groundhog.pl script that presented only the groundhog poll, you ll see a big difference in that poll.pl contains no information specific to any given poll. The poll name is obtained as a parameter from the script environment, and the information about that poll comes from the database tables.

The poll.pl script is based on the assumptions that the poll parameter identifies the poll to administer and that the choice parameter value indicates what to do. The script interprets choice values as follows:

  • If choice has no value, the script is being invoked for the first time, so we just display the voting form.

  • If choice has a value equal to the submit_title column value, the user just submitted a vote. We process the vote and display the current results.

  • If choice is results, the user wants to see the current results without voting, so the script just displays the results.

The file Poll.pm that contains the polling module code is constructed using guidelines like those used earlier for writing WebDB: : TableInfo earlier in the chapter. It should begin with an appropriate package statement that specifies the module name and use statements that reference the modules it needs, and it must be installed as WebDB/Poll. pm under some directory in Perl s search path.

The general outline of Poll.pm looks like this:

 package WebDB::Poll;  use strict;  use CGI qw(:standard escapeHTML);  use DBI;  sub new ...                # definition for new() method  sub generate_form ...      # definition for generate_form() method  # ... definitions for other methods ...  1;                         # return true from module file 

The new() method creates a new poll object, given a database handle and a poll name:

 $poll = WebDB::Poll->new ($dbh, $poll_name); 

It reads the information for the named poll from the database, creates a data structure to store the information, and returns a reference to the structure. If the poll cannot be found, new() returns undef. The implementation looks like this:

 sub new  { my ($class, $dbh, $poll_name) = @_;  my $self;  my ($col_name, $sth, $data);      # If $poll_name is an integer, look for a match in the id      # column. Otherwise look in the name column.      $col_name = ($poll_name =~ /^\d+$/ ? "id" : "name");      # not the poll still is accepting values      # select record value, and active value indicating whether or      $sth = $dbh->prepare (                 "SELECT *, IF(begin_date <= NOW()                              AND (end_date >= NOW() OR end_date IS NULL),1,0)                              AS active                  FROM poll                  WHERE $col_name = ?");      $sth->execute ($poll_name);      $data = $sth->fetchrow_hashref ();  # no loop; only one record is expected      $sth->finish ();      return (undef) unless $data;    # no record found      # supply defaults for missing fields      $data->{submit_title} = "Submit" unless $$data->{submit_title};      $data->{thank_you} = "Thank you for voting." unless $$data->{thank_you};      # Look up poll's candidate records using poll ID value. Retrieve them      # using the seq and label values to put them in the proper display order.      # Fetch each one as a reference to a hash, storing them in an array.      # If label is blank or NULL, use the name as the label.      $sth = $dbh->prepare (                 "SELECT                      name,                      IF(label IS NULL || label = '',name,label) AS label,                      tally                  FROM poll_candidate WHERE id = ?                  ORDER BY seq, label");      $sth->execute ($data->{id});      $data->{candidate} = [];      while (my $ref = $sth->fetchrow_hashref ())      {         push (@{$data->{candidate}}, $ref);      }      $sth->finish ();      # Paranoia check: if there are no poll_candidate records, the      # poll information is incomplete.      return (undef) unless @{$data->{candidate}};      # We have all the information. Create a new poll object, save the      # poll data and the database handle into it for later, bless the      # object into the appropriate class, and return it.      $self = {};      $self->{results_link} = 1;      $self->{data} = $data;      $self->{dbh} = $dbh;      return (bless ($self, $class));  } 

The new() method is written to handle a $poll_name value that is either an integer poll ID or a poll name. If the value is an integer, we look for it in the id column. Otherwise, the value cannot match any id value, so we assume that it s a poll name and look for it in the name column.

After determining the column to check for a match, we construct a query to retrieve all columns from the matching record, as well as one additional column, active, that indicates whether the poll is currently active. The active value is determined using the following expression in the query selection list:

 IF(begin_date <= NOW() AND (end_date >= NOW() OR end_date IS NULL),1,0) 

The point of this IF() in the SELECT query is to determine whether the value of NOW() lies within the date range specified by begin_date and end_date in the appropriate poll table record. The test against end_date has to account for the case where the value may be NULL to indicate an open-ended poll.

It s up to individual applications to determine what to do with the active value. The WebDB::Poll module itself doesn t enforce any requirement in the new() method that a poll must be active. If it did, we wouldn t be able to retrieve poll information for closed polls, which would prevent us from finding out their final results! On the other hand, putting the burden of checking active on application scripts has its own drawbacks. If you look back to poll.pl, for example, you ll see that it has an apparent bug. It doesn t check whether a poll is active, so it would accept votes even for closed polls. We ll address this problem later in the section Closing a Poll.

After constructing the appropriate SELECT query, we execute it and fetch the result. Because there are unique indexes on both the id and name columns, we should get back at most one row, regardless of whether we re looking for a poll ID number or a name. If the SELECT statement doesn t return any rows, the poll doesn t exist and new() returns undef. Otherwise, we check a couple of the column values and supply defaults if they re missing, and then go on to retrieve the associated poll_candidate records using the id column value to identify them. This second SELECT has two notable features. One is the column selection list, which uses IF() to implement the convention that if a label is missing (Null or blank), we ll use the name for the label:

 IF(label IS NULL || label ='',name,label) AS label, 

The other is the ORDER BY clause, which implements our convention for allowing candidates to be displayed in alphabetic order. Sorting is done on two columns, seq and label. If the seq values differ, they control the sort order, and therefore the order in which candidates display in the poll form. If seq values are identical, the label values control the sorting, so that candidates display in alphabetic order.

Internally, poll objects are represented as hashes. The database handle passed to new() becomes one hash element named dbh. (It's remembered for further operations on the poll which means you shouldn t close the connection until you re done with the poll object.) The information pulled from the database becomes another element, data, which is itself a hash. The data hash contains one element for each column in the record from the poll table (plus the active element that indicates whether the poll is still open). It also contains a candidate element containing candidate information as an array. This array has an element for each row from the poll_candidate table. Here are some examples showing how to access various types of poll information:

 $poll->{dbh}                              poll database handle  $poll->{data}->{title}                    title column from poll table  $poll->{data}->{active}                   whether poll is active  $poll->{data}->{candidate}                poll candidate records  $poll->{data}->{candidate}->[0]->{tally}  vote tally for first candidate 

new() also defines a results_link hash element. By default, this is true, meaning that the poll form should include a link allowing the user to go directly to the poll results page without voting. An application can set this attribute to 0 (false) to disable this link.

Assuming that new() returns a poll object and not undef, we can use the object to perform various operations. The first thing a script is likely to do with a poll object is to invoke its generate_form() method, which returns a string containing the HTML for the form. The way you use this will depend on how you re presenting the poll. If the poll is the main thing on the page, you ll want to print the rest of the supporting page structure around the poll:

 print header (),      start_html (-title => escapeHTML ($poll->{data}->{title}),                  -bgcolor => "white");  print $poll->generate_form ();  print end_html (); 

If the poll is a small part of a more complex page, you d print the poll at the appropriate point in page generation, and the supporting structure would be different.

The code for the generate_form() method looks like this:

 sub generate_form  { my $self = shift;  my $data = $self->{data};  my $str;        # string to hold HTML for form  my @name;       # candidate names  my %labelmap;   # candidate name -> label map      $str = start_form (-action => url ());      # identify poll      $str .= hidden (-name => "poll", -value => $data->{id}, -override => 1);      # ask the question      $str .= p (escapeHTML ($data->{question}));      # generate candidate name list and name -> label map for radio buttons      foreach my $candidate (@{$data->{candidate}})      {         push (@name, $candidate->{name});          $labelmap{$candidate->{name}} = $candidate->{label};      }      $str .= radio_group (-name => "candidate",                          -values => \@name,                          -labels => \%labelmap,                          # we assume no candidate has this value...                          -default => "[NO DEFAULT]",                          -override => 1,                          -linebreak => 1);      # add the submission button      $str .= br ()              . submit (-name => "choice", -value => $data->{submit_title});      $str .= end_form ();      # add link allowing user to see current results without      # voting, unless that has been suppressed      if ($self->{results_link})      {         $str .= hr ();          $str .= a ({-href => url () . "?choice=results;poll=$data->{id}"},                      "Show current results");      return ($str);      } 

The form is fairly typical. The form action parameter is the current script s URL, to cause it to be self-referencing. For identification purposes, we write the poll id value into the form as a hidden poll parameter so that the next time the script is invoked we ll be able to tell which poll to work with. Then we present the text of the poll question (the question the user answers by casting a vote), the list of candidates, and the submission button. The last thing added is a link that enables the user to see the results without voting, unless it has been suppressed.

To generate the radio buttons that show each of the candidates, we need to get the candidate names and labels. These are available as the name and label attributes in each of the candidate records stored in the candidate array, so we walk through that array to construct an array of names and a hash that maps names to descriptive labels. The resulting structures contain the information needed for the values and labels parameters to the radio_group() function.

The last element of the form is a submission button named choice and labeled using the value stored in the submit_title attribute of the poll data. (This also becomes the value of the button when it is selected. If you look back at the dispatch logic of poll.pl, you ll see that it determines whether a vote needs to be processed by comparing the choice parameter to this string rather than to submit.)

When the form submission button is selected, a vote may have been cast. The tally_vote() method should be invoked to process it and add the vote to the chosen candidate s tally:

 sub tally_vote  { my ($self, $name) = @_;  my $dbh = $self->{dbh};  my $data = $self->{data};  # default return value  my $message = "No vote was submitted; did you make a choice?";      if (!$data->{active})      {         $message = "Sorry, the poll has closed."      }      elsif ($name)      {         # make sure the vote matches one of the legal candidate names          for my $i (0 .. @{$data->{candidate}}-1)          {             if ($name eq $data->{candidate}->[$i]->{name})              {                 # update tally in database table                  $dbh->do ("UPDATE poll_candidate SET tally = tally + 1                              WHERE id = ? AND name = ?",                          undef, $data->{id}, $name);                  # update tally in the in-memory structure                  ++$data->{candidate}->[$i]->{tally};                  # return value is the thank-you message                  $message = $data->{thank_you};              }          }      }      return ($message);  } 

tally_vote() processes the vote and returns a string that can be displayed to the user to indicate what action was taken. The string can take three forms. First, if the poll is not active, no action is taken and we return a string indicating that the poll is closed.

Second, if the name parameter is empty, no vote actually was cast. (This happens when the user selects the submission button without choosing any candidate s radio button.) In this case, we take no action and return a message saying no vote was found.The same message also is returned if the name parameter is not empty but doesn t match any of the legal candidate names. (Presumably a result of a formhacking attempt.)

Third, if a vote is cast for a legal candidate, we update that candidate s vote tally and return the poll s thank-you message to indicate success. To count a vote, we update the counter for the appropriate row in the poll_candidate table as well as the in-memory poll information. The in-memory structure is updated on the assumption that after processing the vote you ll likely want to display the current results. By updating this structure, it becomes possible to present the results without having to query the database again to get the proper tally values.

The simplest way to display poll results is in text form. The text_result() method is used for this. It generates an HTML table and returns it as a string. The code is much like that from our original poll implementation in the groundhog.pl script:

 sub text_result  { my $self = shift;  my $data = $self->{data};  my ($str, $sum, @table_row);      $str = p (" The current results are:");      # compute sum of vote tallies      $sum = 0;      map { $sum += $_->{tally} } @{$data->{candidate}};      if ($sum == 0)  # no results!      {         $str .= p ("No votes have been cast yet");          return ($str);      }      # Construct table of results, header line first, then contents.      # For each candidate, show votes as a tally and as a percentage      # of the total number of votes.  Right-justify numeric values.      push (@table_row, Tr (th (""), th ("Votes"), th ("Percent")));      for my $i (0 .. @{$data->{candidate}}-1)      {         my $candidate = $data->{candidate}->[$i];          my $percent = sprintf ("%d%%", (100 * $candidate->{tally}) / $sum);          push (@table_row, Tr (                 td (escapeHTML ($candidate->{label})),                  td ({-align => "right"}, $candidate->{tally}),                  td ({-align => "right"}, $percent)              ));      }      $str .= table (@table_row);      return ($str);  } 

Closing a Poll

To close a poll, all you need to do is assign a nonNULL value to the end_date column. To close the head-shape poll as of right now or as of a specific date, for example, the queries look like this:

 UPDATE poll SET end_date = NOW() WHERE name = 'head'  UPDATE poll SET end_date = '2001-04-01' WHERE name == 'head' 

To make this a little easier, we can write a short script, close_poll.pl, that takes a poll name (or number) as an argument and sets the end date to NOW(). It also accepts a second argument if you want to specify the end date explicitly:

 #! /usr/bin/perl -w  # close_poll.pl - set the end_date for a poll  # The date defaults to "NOW()" if not given; if given it must be in  # ISO format.  use strict;  use lib qw(/usr/local/apache/lib/perl);  use WebDB;  @ARGV or die "Usage: close_poll.pl poll_name_or_id [date]\n";  my $poll_name = shift (@ARGV);  my $date = (@ARGV? shift (@ARGV) : undef);  # If $poll_name is an integer, look for a match in the id  # column. Otherwise look in the name column.  my $col_name = ($poll_name =~ /^\d+$/ ? "id" : "name");  my $dbh = WebDB::connect ();  my $rows = $dbh->do (                 "UPDATE poll SET end_date = IFNULL(?,NOW())                  WHERE $col_name = ?",                      undef, $date, $poll_name);  warn "Warning: no poll $poll_name was found\n" if $$rows == 0;  $dbh->disconnect ();  exit (0); 

The IFNULL() function returns its first argument if that argument is not NULL and returns the second argument otherwise. It s used in the UPDATE query because we can t pass the string NOW() using a placeholder (it would end up as a literal string, not as a function call). By using IFNULL(), we can pass an explicit date to the query if one was given and have it be used as a literal string. Otherwise, we pass undef and the current value of NOW() is used.

Earlier, I mentioned that poll.pl had a bug in that it would accept votes for a poll that is closed. In a sense that s not strictly true, because the tally_vote() method in the WebDB::Poll module does check whether a poll is active before it adds a vote. However, that means the user will find out that a poll is closed only after casting a vote. It d be better for poll.pl to check a poll s status and let the user know earlier that the poll is closed. To effect this change, modify the clause in the dispatch logic that presents the polling form from this:

 if ($choice eq "") # initial invocation  {     print $poll->generate_form ();  # display the voting form  } 

to this:

 if ($choice eq "")                  # initial invocation  {     if ($poll->{data}->{active})      {         print $poll->generate_form ();  # display the voting form      }      else      {         print p ("Sorry, the poll has closed.");          # add link allowing user to see current results without voting          print hr (),              a ({-href => url () . "?choice=results;poll=$poll->{data}->{id}"},                      "Show current results");      }  } 

The modification displays the form only if the poll is active; otherwise, it tells the user that the poll is closed, but also presents a link for checking the current results.

Displaying Results of Multiple Polls

I ve noticed that polls on Web pages often have a link you can follow to view results of past polls. If you want to do that, too, it s fairly easy. The procedure goes like this:

  1. Run a query to pull information for each of the polls

  2. Display a page containing links to each poll. You could either present links to the respective results pages, or, if some polls are still open, present links to the voting pages as well.

The following simple script, show_polls.pl, lists all available polls and the relevant links for each:

 #! /usr/bin/perl -w  # show_polls.pl - present a list of polls  use strict;  use lib qw(/usr/local/apache/lib/perl);  use CGI qw(:standard escapeHTML);  use WebDB;  print header (),      start_html (-title => "Polls Available", -bgcolor => "white");  my $dbh = WebDB::connect ();  my $sth = $dbh->prepare (                 "SELECT *, IF(begin_date <= NOW()                              AND (end_date >= NOW() OR end_date IS NULL),1,0)                              AS active                  FROM poll                  ORDER BY id");  $sth->execute ();  while (my $ref = $sth->fetchrow_hashref ())  {     # display only date part of begin_date (strip the time)      print substr ($ref->{begin_date}, 0, 10), br (), "\n";      print escapeHTML ($ref->{title} . ": " . $ref->{question}), br (), "\n";      print a ({-href => "poll.pl?poll=$ref->{id}"}, "[vote]"), " "                                                      if $ref->{active};      print a ({-href => "poll.pl?choice=results;poll=$ref->{id}"},                  "[results]"),              br (), '\n";      print br ();  }  $sth->finish ();  $dbh->disconnect ();  print end_html ();  exit (0); 

To list only open polls, use this query instead:

 SELECT * FROM poll  WHERE begin_date <= NOW() AND (end_date >= NOW() OR end_date IS NULL) 

If you want to use the script to keep tabs on your site s polling activity, you d likely find it helpful to modify it to present other information such as the date range for each poll, the total number of votes cast, and so forth. If you want to know the number of votes cast, you must perform a more complex query that runs a join between the poll and poll_candidate tables. For example, you can retrieve poll table columns along with total votes cast in each poll like this:

 SELECT poll.*, SUM(poll_candidate.tally) AS "total votes"  FROM poll, poll_candidate  WHERE poll.id = poll_candidate.id  GROUP BY id  ORDER BY begin_date DESC, id DESC 

Suggested Modifications

Write a graphic_results() method that is like text_results() but that displays poll results in graphical form.

Modify the WebDB:Poll module so the methods take additional parameters for controlling their behavior. For example, you could modify generate_form() to display radio buttons with or without linebreaks, or to enable you to generate a pop-up menu or scrolling list rather than a set of radio buttons.

As an alternative to modifying the existing methods to accept additional parameters, write methods for setting options. Such methods could store information in the poll object itself, and that information could be consulted later by generate_form().

Add some fraud detection to prevent ballot-box stuffing.

We don t log each vote, we just count them. Modify the application to keep a record of each vote and when it occurred. You could use this to track day-by-day activity, for example.

In this chapter, we ve examined several ways you can use information in your database to help you create forms, as well as to validate form contents and to generate the queries for storing the contents. These techniques can help you write reusable code for your data-entry applications. They re useful in any context involving forms, however; one important application of forms is to provide front ends to search engines so that users can specify search parameters. The next chapter deals with issues involved in performing searches; as you might expect, some of the techniques discussed in this chapter will make an appearance in that chapter as well.

only for RuBoard - do not distribute or recompile


MySQL and Perl for the Web
MySQL and Perl for the Web
ISBN: 0735710546
EAN: 2147483647
Year: 2005
Pages: 77
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net