Product Registration

only for RuBoard - do not distribute or recompile

Registration applications serve many purposes. You can use them to allow people to register products they have purchased, sign up for conferences, request a catalog, add themselves to a mailing list, and so forth. The obvious advantage over paper forms for you when you receive a registration from someone is that you needn t re-key the information to get it into your database the Web application inserts it for you. An advantage for users is instant transmission of their registration information without having to dig up a stamp or put the registration form in the mail.

In this section, we ll write a script, prod_reg.pl, that collects product registrations over the Web. Generally, this kind of registration form has, at a minimum, fields to identify the product and the customer, and our application will confine itself to gathering that kind of information. Many registration forms have additional fields for demographic information such as household income, type of employment, or how the product will be used. We ll skip that stuff; you can add it later if you like.

To process registrations, we need a database table in which to store registration records and an application that collects information from customers and inserts records into the table. The table we ll use looks like this:

 CREATE TABLE prod_reg  (     # record identification information: unique record ID and time of      # creation      id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,      t           TIMESTAMP,      # product identification information: serial number, purchase date,      # where purchased      serial      VARCHAR(30),      purch_date  VARCHAR(20),      store       VARCHAR(30),      # customer identification information: name (last, first, middle      # initial), postal address, telephone, email address      last_name   VARCHAR(30),      first_name  VARCHAR(30),      initial     VARCHAR(5),      street      VARCHAR(30),      city        VARCHAR(30),      state       CHAR(2),      zip         VARCHAR(10),      telephone   VARCHAR(20),      email       VARCHAR(60)  ) 

The prod_reg table contains columns that serve to identify the record itself as well as the product and the customer. The first two columns, id and t, provide a unique identification number for each record and indicate when records are created. We can have MySQL supply values for these columns automatically when we create new records; therefore, they need not be represented in the product-registration form.^[1] The rest of the columns describe the product (serial number, purchase date, place of purchase) or the customer (all the other columns). The user must supply these product and customer values, so the form will contain corresponding fields for all of them.

^[1] Using a TIMESTAMP enables us to have MySQL fill in the time automatically when we create new records. If you expect to edit records later, however, it wouldn t be an appropriate choice because MySQL would update the value whenever you change a record. In that case, you should pick DATE or DATETIME and set the t column to CURRENT_DATE or NOW() at record-creation time.

Our application fits into the product-registration process as follows:

• When a customer purchases one of our products, it will include a paper registration form that can be filled in and mailed the traditional way. However, the form also will have a note:

   If you prefer to register online, visit our Web site at:  http://www.snake.net/cgi-perl/prod_reg.pl 

• When the customer goes to the Web site (assuming a preference for registering online rather than completing a paper form), the application presents a form for collecting registration information that reflects the structure of our prod_reg table. (The form will contain fields corresponding to each of the table s columns except id and t.) The customer completes the registration by filling in the form, and then selects the Submit button to send the information back to the Web server.

• The application examines the form to make sure all required fields (such as the product serial number) have been provided. If not, we display some feedback to the user indicating that additional information is necessary and redisplay the form so that the customer can complete it properly. Otherwise, we use the form contents to create a new record in the prod_reg table and display a confirmation page to the customer indicating that the registration was received.

From this description, we can see that our application needs to generate a form, extract and validate the contents of the submitted form, insert registration information into the database, and display confirmation to the user. (Not coincidentally, these activities correspond to the steps I mentioned earlier in the chapter as those that are common to many form-based applications.)

Designing the Form

For the prod_reg.pl application, we ll use a form that consists entirely of plain-text input fields. That s a pretty regular field structure, so instead of writing out a lot of separate textfield() calls to generate the fields, let s try a different approach. We can specify form information as an array of field descriptions:

 my @field_list =  (     { name => "serial", label => "Serial number:", size => 30, req => 1 },      { name => "purch_date", label => "Purchase date:", size => 20, req => 1 },      { name => "store", label => "Where purchased:", size => 30 },      { name => "last_name", label => "Last name:", size => 30, req => 1 },      { name => "first_name", label => "First name:", size => 30, req => 1 },      { name => "initial", label => "Initial:", size => 5 },      { name => "street", label => "Street:", size => 30 },      { name => "city", label => "City:", size => 30 },      { name => "state", label => "State:", size => 2 },      { name => "zip", label => "Zip code:", size => 10 },      { name => "telephone", label => "Telephone:", size => 20 },      { name => "email", label => "Email address:", size => 60 }  ); 

Each item in the @field_list array is a hash that describes a single field. This array serves multiple purposes:

• It helps us generate the entry form. The name, label, and size attributes specify a field name, the label to display next to it (so the user knows what to enter into the field), and a field size. Field names correspond to the names of the underlying table columns, to make it easy to associate form values with the appropriate table columns at record-creation time.

• The array helps us validate submitted form contents when a user sends in a registration: We ll determine whether any given field is required to have a value by checking its req attribute.

Because the @field_list array applies to both generation and validation of the registration form, we ll pass it as a parameter to multiple functions in the main dispatch logic of our prod_reg.pl script:

 my $choice = lc (param ("choice")); # get choice, lowercased  if ($choice eq "")                  # initial script invocation  {     display_entry_form (\@field_list);  }  elsif ($choice eq "submit")  {     process_form (\@field_list);  }  else  {     print p (escapeHTML ("Logic error, unknown choice: $choice"));  } 

Generating the Form

The display_entry_form() function generates the registration form by iterating through the field list and using the information contained in each list element to construct a call to textfield(). Then it adds a Submit button at the end:

 sub display_entry_form  { my $field_ref = shift;      # reference to field list      print start_form (-action => url ());      print p ("Please enter your product registration information,\n"              . "then select the " . strong ("Submit") . " button.");      foreach my $f (@{$field_ref})      {         print escapeHTML ($f->{label}), " ",              textfield (-name => $f->{name}, -size => $f->{size}),              br (), "\n";      }      print submit (-name => "choice", -value => "Submit"),              end_form ();  } 

It isn t necessary to use escapeHTML() to encode values passed to textfield() because CGI.pm automatically encodes parameters to functions that generate form elements. However, the labels displayed next to the input fields are just static text, so we need to encode them in case they contain any special characters.

One problem with the preceding code is that it produces a visually distracting form. The labels have different lengths, so the input fields that appear to the right of the labels won t line up vertically. We can provide a more regular layout by arranging labels and input fields within columns of an HTML table. Here s a modified version of display_entry_form() that produces a table:

 sub display_entry_form  { my $field_ref = shift;      # reference to field list  my @row;      print start_form (-action => url ());      print p ("Please enter your product registration information,\n"              . "then select the " . strong ("Submit") . " button.");      foreach my $f (@{$field_ref})      {         push (@row, Tr (                 td (escapeHTML ($f->{label})),                  td (textfield (-name => $f->{name}, -size => $f->{size}))              ));      }      print table (@row),              submit (-name => "choice", -value => "Submit"),              end_form ();  } 

Another improvement would be to give the user a hint about which fields are required to have a value. We can use the req attribute for this, but first it s necessary to decide how to indicate this field is required to the user. There are different ways to accomplish this:

• Print the labels for the required fields differently than for non-required fields. One common technique is to use red text or bold text. Personally, I don t like red text very much, because it doesn t look much different from black text to me. (I have defective color vision.) Printing field labels in bold can be problematic if the user has selected a display font for which bold and plain text don t look much different. For example, I find that if I set the font to Arial 10-point, bold and plain text don t look any different in browser windows. Under these circumstances, making the text bold communicates no information.

• Add some kind of image next to missing fields. This is visually distinctive but doesn t work very well if the user has image loading turned off. Also, it requires more complicated logic in generating the form.

• Use an asterisk next to the label for required fields.

We ll settle on the simplest technique (putting an asterisk next to the label of each required field). It s also a good idea to modify the introductory text that precedes the form, to indicate what the asterisks signify:

 sub display_entry_form  { my $field_ref = shift;      # reference to field list  my @row;      print start_form (-action => url ());      print p ("Please enter your product registration information,\n"              . "then select the " . strong ("Submit") . " button.");      print p ("(Fields with an asterisk next to the name are required.)\n");      foreach my $f (@{$field_ref})      {         my $label = $f->{label};          $label .= "*" if $f->{req};     # add asterisk for required fields          push (@row, Tr (                 td (escapeHTML ($label)),                  td (textfield (-name => $f->{name}, -size => $f->{size}))              ));      }      print table (@row),              submit (-name => "choice", -value => "Submit"),              end_form ();  } 

Processing Form Submissions

After the user fills in and submits the form, prod_reg.pl handles the submission by calling process_form(). A minimal version of this function might just look through the field list to make sure that the user actually provided values in all the required fields. We can do this by checking the req attribute for each element in our list that describes form fields:

 sub process_form  { my $field_ref = shift;      # reference to field list      foreach my $f (@{$field_ref})      {         next unless $f->{req};          # skip test if field is not required          my $value = param ($f->{name}); # get value          if (!defined ($value) || $value eq "")          {             print p ("Hey, you didn't fill in all the required fields!");              return;          }      }      # add registration to database and display confirmation page      insert_record ($field_ref);  } 

As noted, that s a minimal approach. It doesn t tell the user which fields were missing. It also gets fooled if the user just types spaces in the required fields. We certainly can do a better job. Some of the possible improvements here are to trim extraneous whitespace from the fields before checking them, tell the user which elements are required, and redisplay the form if it s incomplete.

How should we handle the problem of providing feedback to the customer about which fields are missing? Here s one approach:

1. Loop through the fields, checking each of them until we find one that s missing.

2. Announce which field is missing and redisplay the form so that the customer can enter the value and resubmit the form.

3. Repeat until all required fields have been supplied.

Here s another:

1. Check all the fields.

2. Announce which fields are missing and redisplay the form so that the customer can correct all the problems and resubmit the form.

The first method of form validation is simpler to implement, but leads to an application that s extremely tedious to use because the user finds out about only one problem at a time. The second method provides more information. To implement it, we need to save up the error messages as we check the fields. Here is a modified version of process_form() that does this:

 sub process_form  { my $field_ref = shift;      # reference to field list  my (@errors);      foreach my $f (@{$field_ref})      {         next unless $f->{req};          # skip test if field is not required          my $value = param ($f->{name}); # get value          $value = "" unless defined ($value);    # convert undef to empty string          $value =~ s/^\s+//;             # trim leading/trailing whitespace          $value =~ s/\s+$//;          push (@errors, $f->{label}) if $value eq "";    # it's missing!      }      if (@errors)      {         print p ("Some information is missing."                  . " Please fill in the following fields:");          s/:$// foreach (@errors);   # strip colons from end of labels          print ul (li (\@errors));          display_entry_form ($field_ref);    # redisplay entry form          return;      }      # add registration to database and display confirmation page      insert_record ($field_ref);  } 

To validate the form, process_form() loops through each field and checks its req attribute. If the field is not required, no further test is necessary. Otherwise, we have a look at the field value. First, it s converted to an empty string if the value is undef.^[2] Then any whitespace at the ends of the value is removed so that we don t think a value is present if it contains only spaces.^[3] If the result is empty, a required value is missing and we add a message to the @errors array.

^[2] The purpose of converting undefined values to empty strings is just to suppress warnings that would otherwise be triggered by the presence of the -w flag on the script s initial #!/usr/bin/perl line. Otherwise, attempting to operate on such values results in messages in the error log.

^[3] I m actually a bit ambivalent about pointing out that whitespace removal helps us perform better validation. By doing so, I m giving away one of my own techniques for dealing with intrusive form-based applications that insist on requiring information that I consider private. I find that just entering a space into a field often will fool an application into thinking that I ve given it real information!

If @errors is non-empty after the loop terminates, the form is incomplete. process_form() responds by showing the user which fields must be filled in, and then redisplays the form by calling display_entry_form(). Here is an instance where CGI.pm s sticky form behavior is quite valuable. None of the form element-generating calls in display_entry_form() include an override parameter, so when that function redisplays the form, CGI.pm automatically fills in the fields with whatever values the customer just submitted. That enables us to easily display the form in the same browser window as the error messages. The customer can determine from the error messages at the top of the window which fields still need to be filled in, and can enter the required values without clicking the Back button to return to the entry form. (It s of little value to display error messages if we then require the customer to click Back; as soon as the customer does so, the error messages disappear!)

Before we move on to discuss the insert_record() function that actually adds a new record to the prod_reg table, let s make one more change. One thing process_form() does while checking field values is to trim extraneous whitespace. We d like to use these tidied-up values when inserting the new registration record, but unfortunately they are just discarded because we used a temporary variable for field checking. If we save the values, we can use them later when adding the record. One easy way to do this is based on the fact that Perl enables us to create new hash attributes on the fly: We can create a new value attribute in each field information hash and stuff the trimmed value into it. The resulting field-checking loop follows. Note that we want to save all values (not just the required ones), so the test of the req attribute must be moved from the beginning of the loop to the end:

 foreach my $f (@{$field_ref})  {     my $value = param ($f->{name}); # get field value      $value = "" unless defined ($value);    # convert undef to empty string      $value =~ s/^\s+//;             # trim leading/trailing whitespace      $value =~ s/\s+$//;      $f->{value} = $value;           # save trimmed value      push (@errors, $f->{label}) if $value eq "" &&& $f->{req};  } 

Storing the Registration Record

After a form has been received that passes the form validation process, insert_record() stores the record and displays a confirmation message to the customer. There are a number of ways to construct an INSERT statement. One source of variation is that MySQL supports different syntaxes for INSERT :

 INSERT INTO prod_reg SET col1 = val1, col2 = val2, ...  INSERT INTO prod_reg (list of columns) VALUES(list of values) 

Another source of variation is that DBI supports different ways of specifying data values for a query. We can either insert the values directly into the query string or use placeholders.

To use the first INSERT syntax just shown and insert the values into the query string, you could do something like this:

 $stmt = "";  foreach my $f (@{$field_ref})  {     next if $f->{value} eq "";      # don't bother with empty fields      $stmt .= "," if $$stmt;         # put commas between assignments      $stmt .= $f->{name} . " = " . $dbh->quote ($f->{value});  }  $stmt = "INSERT INTO prod_reg SET $stmt";   # complete the statement  $dbh->do ($stmt);                           # and execute it 

The loop ignores empty fields; we ll just let MySQL supply whatever default values the corresponding columns have. If a field is not empty, we place its value into the string to assign it to the appropriate column after processing it with quote() to perform any quoting and escaping that may be necessary, of course.

To use placeholders instead, we still walk through the fields, but this time we put ? placeholder characters into the query string and save the column values so that we can pass them to the do() method:

 $stmt = "";  @placeholder = ();  foreach my $f (@{$field_ref})  {     next if $f->{value} eq "";      # don't bother with empty fields      $stmt .= "," if $$stmt;         # put commas between assignments      $stmt .= $f->{name} . " = ?";   # add column name, placeholder      push (@placeholder, $f->{value});   # save placeholder value  }  $stmt = "INSERT INTO prod_reg SET $stmt";   # complete the statement  $dbh->do ($stmt, undef, @placeholder);      # and execute it 

Functionally, there isn t much difference between the two ways of constructing the query, and I doubt whether there is much reason to prefer one over the other in terms of performance, either. Take your pick. (If you expect to be issuing zillions of INSERT statements, however, you might want to run a few benchmarks to gather some empirical performance numbers.)

Regardless of how you construct the INSERT query, it s a good idea after executing it to present some sort of confirmation to the customer. We ll discuss confirmation pages in more detail later, but for now we ll just present a short message and thank the user:

 print p ("We have received your product registration. Thank you."); 

The application doesn t check whether the registration record actually was inserted properly. If we want to warn the customer about problems creating the registration record, we should check the return value from do(), and then display an appropriate message:

 $rows = $dbh->do ($stmt, undef, @placeholder);  if ($rows)  {     print p ("We have received your product registration. Thank you.");  }  else  {     print p ("Sorry, we were unable to process your product\n"              . "registration. Please try again later.");  } 

Another potential problem is that a serial number might be submitted multiple times. This can occur different ways. The customer might click the Back button and then select Submit again mistakenly. Or a customer might enter the serial number incorrectly, duplicating a number already entered by another customer. What then? As written, the prod_reg.pl script does nothing special. It just inserts the record as usual. The theory behind this cavalier behavior is that a customer can t really do much about these problems anyway, so why say anything? On the other hand, you might want to expend a little extra effort to provide yourself with information about possible problems. For example, you could run a query periodically to find duplicate serial numbers so that you can examine the appropriate records. Here s a query that finds serial numbers that appear multiple times in the prod_reg table:

 SELECT serial, COUNT(serial) AS count FROM prod_reg  GROUP BY serial HAVING count > 1 

Enter a registration record, and then click your browser s Back button and the form s Submit button a few times to generate some duplicate records. Then run the preceding query from the mysql program to see what kind of output it produces. If you adopt the approach of looking for duplicate values using this query, you ll find that it becomes much slower as your table accumulates more records. That s because the table doesn t have any indexes on it. Use ALTER TABLE to add an index on the serial column and the query will run much more quickly:

 ALTER TABLE prod_reg ADD INDEX (serial) 

Checking for Empty Value Lists

The insert_record() function constructs an INSERT statement, but doesn t bother to check whether the list of column assignments following the SET keyword is empty. We know it won t be, because our form has several required fields and insert_record() isn t called unless they ve been filled in. If for some reason you have a form for which no fields are required, however, you should check for an empty column assignment list. Why? Because if the form is empty and you don t check, you ll find yourself constructing and trying to execute an INSERT statement that looks like this, which is syntactically invalid:

 INSERT INTO tbl_name SET 

If you re determined to insert a new record even when there are no values, you might want to consider using an INSERT syntax, which does allow an empty value list:

 INSERT INTO tbl_name () VALUES() 

This empty INSERT syntax is understood by MySQL 3.23.3 and later versions.

Suggested Modifications

process_form() displays all the error messages together at the top of the browser window if there are problems with a form submission. Modify the application to display each message immediately below the field to which it applies, to put the messages in closer proximity to the offending fields. You can do this by creating an error attribute in @field_list elements, and changing display_entry_form() to check for and display that attribute. If you want to display a warning icon as well, what would you need to do?

If you have independent information about your products, you may be able to use it to improve the integrity of product registrations. Suppose you have a product-listing table that contains a record of each item that you ve manufactured, including its serial number. You could have the prod_reg.pl application present an initial gatekeeper page that displays a form containing only an input field for the product serial number. When the customer supplies the number, check your product listing to verify that the number is valid, and proceed to the main entry form if so. Otherwise, you know the serial number was entered incorrectly and you can ask the user to double-check the number on the product.

prod_reg.pl isn t very rigorous in its validation, other than to make sure required fields aren t missing. Modify it to perform field-specific validation. Make sure the state abbreviation is legal, for example, or verify that if the email field is filled in that the value looks like a valid address. For extra fun, check the purchase date to make sure it looks like a date. (Because dates can be specified many ways, however, you probably should display a note indicating the format you expect date values to follow.)

Using text boxes for all the input fields makes it easy to describe the form using a data structure so that we can generate it and validate it automatically. But some of the fields might be easier for the user to fill in if we had used other field types. For example, the field for entering the state could be represented by a scrolling list containing the possible state abbreviations. (This would also help make sure that users don t enter an incorrect state value.) How would you modify the @field_list array and the form-generation code to handle this? Is it worth it?

The application pretty much assumes the postal address style used in the United States. (For example, we have a field for Zip Code, which is hardly a worldwide convention.) How would you generalize the form to be less U.S.-centric?

Modify insert_record() to present a more informative confirmation page that shows the customer what information was stored. (You might be surprised how often people recognize that they ve entered one or more values incorrectly if you just reformat the information and show it to them again.) If you make this change, you should also provide information about whom the customer should contact to report any errors. An alternative to storing the record in the database before displaying it in a confirmation page is to present the information along with an Are you sure? button. If the customer is sure, store the record in the database; otherwise, allow the customer to edit the information further and resubmit it.

only for RuBoard - do not distribute or recompile