Electronic Greeting CardsSend a Friend a Greeting

only for RuBoard - do not distribute or recompile

Electronic Greeting Cards Send a Friend a Greeting

Our next application enables users to construct electronic greeting cards and send them to friends. You use your browser to create a card, and the recipient uses a browser to view it. This is a more ambitious (and complex) undertaking than any of our previous applications because it has to do a whole bunch of stuff:

  • When you first visit the application, you see a card information form into which you enter the name and email address for the recipient and for yourself, as well as the text of the greeting you want to send.

  • If you want to select a picture to be displayed with the card, the application switches to a page that displays a gallery of images. After you pick one, the application switches back to the original information form. If you decide to select a different picture, the process repeats.

  • After you ve finished constructing the card, the application assigns it an expiration date and generates an email message to the recipient indicating that a card is waiting and the URL to use for viewing it.

  • When the recipient issues a request for the card, the application retrieves the appropriate record from the database, generates a Web page that displays it, and updates the record to indicate that the recipient has seen the card. If you asked to be notified when the recipient views the card, the application sends you an email message to that effect.

  • The application removes old cards from the database periodically.

In effect, the application enables one person to communicate asynchronously with another by means of email and the Web, and it uses MySQL to provide the persistent storage that makes this possible. The application consists of several scripts that handle the various tasks involved:

  • make_ecard.pl manages the card-creation process. It presents the card information form and the picture gallery page.

  • When the recipient requests the card, show_ecard.pl displays it, updates the record as having been seen, and notifies the card sender.

  • We need a supply of images and a means of displaying them to be able to present pictures with cards. make_ecard.pl and show_ecard.pl rely for this on the image table and the serve_image.pl script developed in the preceding section, Storing and Retrieving Images. If you haven t yet read that section, it would be a good idea to do so.

  • expire_ecard.pl removes old cards from the database. It runs periodically as a cron job that checks card expiration dates.

Card construction can take place across the span of several page requests, so we may need to store and retrieve the card to and from the database many times before it s completed. If the user switches from the card information form to the image gallery page, for example, the contents of the form are saved to the database before presenting the gallery. After the user selects a picture, we pull the information back out of the database and use it to initialize the form before redisplaying it. Because we need to tie together multiple page requests (so that we can associate them all with the same card), we ll create a unique ID when a user begins the card-creation process, and carry that ID along at each stage until the card is finished.

The ecard table for storing card records looks like this:

 CREATE TABLE ecard  (     id              INT UNSIGNED NOT NULL AUTO_INCREMENT,   # card ID number      PRIMARY KEY (id),      recip_name      VARCHAR(255),   # recipient name and email address      recip_email     VARCHAR(255),      sender_name     VARCHAR(255),   # sender name and email address      sender_email    VARCHAR(255),      sender_notify   ENUM('N','Y'),  # notify sender when recipient views card?      message         TEXT,           # card message      picture         VARCHAR(40),    # name of picture to show with card      expiration      DATE,           # when to expire card      viewed          DATE            # when recipient first viewed card  ) 

The id column specifies the unique identification number that is used to track each card throughout the card-construction process. We ll also include it as part of the URL that is sent to the recipient so that when a recipient requests a card, we can figure out which one to display.

The recipient name and email address allow us to notify the recipient. (Strictly speaking, notification requires only the email address, but a name allows the application to generate messages that are more personal.)

The sender name and email address are needed so that we can inform the recipient who s responsible for sending the message, and also for generating a notification message if the sender wants to be told when the recipient views the card. sender_notify is a two-value column indicating whether the card sender desires this kind of notification.

The message and picture fields comprise the content of the greeting. message contains the text of the greeting, and picture indicates which image from the image table to display with the message. (A picture value of NULL indicates no picture. )

The ecard table also contains two DATE columns. expiration is NULL until the sender completes the card, and then it is set to indicate the date when the card can be deleted from the ecard table. We ll have the application refuse to modify any card that already has the expiration date set. This convention prevents accidental duplicate card submissions if the card sender clicks the browser s Back button after sending the card and then selects the Send Card button again. This same convention is also a modest security enhancement; it prevents someone else from coming along later and modifying a card that s already been completed. The viewed column indicates when the recipient asked to see the card. It s NULL until such a request is received.

Many of these columns are mandatory. Before a card can be sent, we ll enforce the constraint that the names and email addresses all must be supplied, as well as the message text. Because a card may be created in several steps, however, the point at which this information is required does not occur until the user finally indicates the card is finished. That means we must enforce the fields required rule only when the user selects the Send Card button and not before.

Card Storage and Retrieval Utility Routines

Our card-creation script make_ecard.pl needs to perform several types of operations on card records. The script itself represents card information internally as a hash with key names that are the same as columns in the ecard table. However, we ll need to exchange that information back and forth with the client s Web browser, and we ll need to store the hash into and retrieve it from the corresponding database record:

  • When the card information form is displayed, we look up any information about the card that exists in the database and use it to initialize the form that the user sees.

  • When the user switches to the image gallery page or indicates that the card is complete, we extract the contents of the information form using CGI.pm s param() function and save the card to the database.

To do all this, we ll use three functions: extract_card_params() to get card values from the script environment, lookup_card_record() to retrieve a card from the database, and update_card_record() to store a card in the database. All three functions require the card s id value so they can tell which card to operate on. As we ll see shortly, the main logic of the application makes sure this value is known before any of these utility routines are called.

extract_card_params() looks in the script parameter environment for values that the user can specify. This does not include the expiration or viewed dates because the user has no control over them, and they don t come into play until the card has been completed, anyway. The function extracts the relevant card values from the parameter space, constructs a hash from them, and returns a reference to the hash. We create a slot for each parameter to make sure each one is defined and trim the values to eliminate any extraneous spaces:

 sub extract_card_params  { my $card_ref = {};      $card_ref->{id} = param ("id");      foreach my $param ("recip_name", "recip_email", "sender_name",                          "sender_email", "sender_notify", "message", "picture")      {         $card_ref->{$param} = WebDB::trim (param ($param));      }      return ($card_ref);  } 

lookup_card_record() fetches a card from the database given the card ID number and returns it as a hash reference:

 sub lookup_card_record  { my ($dbh, $id) = @_;  my ($sth, $ref);      $sth = $dbh->prepare ("SELECT * FROM ecard WHERE id = ?");      $sth->execute ($id);      $ref = $sth->fetchrow_hashref ();      $sth->finish ();      return ($ref);  # undef if card doesn't exist  } 

To update a card, we shove it back into the database by converting the card hash to an UPDATE statement. Again, the ID number identifies the proper card:

 sub update_card_record  { my ($dbh, $card_ref) = @_;  my ($stmt, @placeholder);      # don't store an empty value in this column      $card_ref->{sender_notify} = "N" if $$card_ref->{sender_notify} ne "Y";      # Construct the SET clause listing the column values to be updated.      # Skip the id element here (it's used in the WHERE clause, not the      # SET clause).      foreach my $key (keys (%{$card_ref}))      {         next if $key eq "id";          $stmt .= "," if $$stmt;             # separate assignments by commas          $stmt .= "$key = ?";                # construct placeholder reference          push (@placeholder, $card_ref->{$key}); # save placeholder value      }      return unless @placeholder; # do nothing if there's nothing to update      # complete the statement, then execute it      $stmt = "UPDATE ecard SET $stmt WHERE id = ?";      push (@placeholder, $card_ref->{id});      $dbh->do ($stmt, undef, @placeholder);  } 

Now that we have these support routines in place, we can see how they fit into the overall architecture of the card construction process.

Card Construction Main Logic

When you invoke make_ecard.pl, it needs to know whether you re beginning a new card or are already in the middle of creating one. This is accomplished using the card ID number, which exists during the card-making process, but not before. The first part of the script therefore checks for an id parameter prior to executing the dispatch code:

 my $card_ref;                   # card information hashref  my $dbh = WebDB::connect ();    # connect to database  # Determine whether to begin a new card or continue working  # on an existing one by checking for a card ID value.  my $id = param ("id");  if (!defined ($id) || $id !~ /^\d+$/)  {     # ID is missing (or is not an integer, and is therefore malformed).      # Create new ecard record; this generates a new ID number      $dbh->do ("INSERT INTO ecard SET id = NULL");      $id = $dbh->{mysql_insertid};           # retrieve ID number      param (-name => "id", -value => $id);   # place ID in parameter space      $card_ref = extract_card_params ($id);  # construct standard card hash  }  else  {     # ID was found, so the card should already exist in the database.      # Make sure it does and that the expiration date hasn't been set.      # (If that date has been set, the card has already been sent!)      $card_ref = lookup_card_record ($dbh, $id);      if (!$card_ref || $card_ref->{expiration})      {         # error - disconnect and close the page; we need proceed no further          $dbh->disconnect ();          print p ("No card with ID $id exists, or card has already been sent");          print end_html ();          exit (0);      }  } 

If make_ecard.pl finds no ID number, it begins a new card by creating a new record in the ecard table. The INSERT statement causes MySQL to create a new ID number; id is an AUTO_INCREMENT column and setting it to NULL generates the next number in the sequence. This number is available as the value of the mysql_insertid database handle attribute after executing the INSERT. (We could also determine the value by issuing a SELECT LAST_INSERT_ID() query, but mysql_insertid provides the same information without the overhead of a second query.) Then we put the id value into the parameter space and construct a standard hash structure. At this point, all elements of the hash except id are empty.

If make_ecard.pl does find an id value, that means the user is working on an existing card, so there should already be a record for it in the database. We look up the record to make sure it really exists, and then check the expiration date to verify that it hasn t already been set. (The expiration value remains NULL until the user sends the card; if it s set, that indicates the user is probably accidentally sending the card again or that someone else is attempting to modify it. Either way, we refuse to continue any further.)

After the preceding initial code executes, we know that we have a database record representing the current contents of the card. We may also have new information in the script s parameter space, if the user has just submitted the card information form or chosen an image from the picture gallery. The dispatch code determines what to do, based as usual on the value of the choice parameter. This code is structured around the choices the user can make on the form page and the gallery page:

  • The information form has two buttons Select Picture (or Change Picture if an image has already been chosen) and Send Card.

  • The gallery page shows a set of images, any of which many be selected. There is also a Continue button if the user decides not to select any picture.

If the value of choice is empty or continue, we populate the card form with whatever information has already been specified and display it. (The value will be continue if the user was just viewing the gallery page but decided not to choose a picture.) If choice is select picture or change picture, we switch to the image gallery page. If choice is add_picture, the user just chose a picture. Finally, if the value of choice is send card, the user has completed the card and we can save it for good and notify the recipient.

 my $choice = lc (param ("choice")); # get choice, lowercased  if ($choice eq "" || $choice eq "continue")  {     # New card or user declined to choose a picture from the      # gallery page.  Just redisplay the card information form.      display_entry_form ($card_ref);  }  elsif ($choice eq "select picture" || $choice eq "change picture")  {     # display image gallery (but save form info first)      $card_ref = extract_card_params ($id);      update_card_record ($dbh, $card_ref);      display_gallery ($dbh, $id);  }  elsif ($choice eq "add_picture")  {     # User chose a picture from the gallery page. Extract the picture      # name and add it to the card hash, then redisplay card form.      $card_ref->{picture} = param ("picture") if param ("picture");      update_card_record ($dbh, $card_ref);      display_entry_form ($card_ref);  }  elsif ($choice eq "send card")  # all done; send the card  {     $card_ref = extract_card_params ($id);      send_card ($dbh, $card_ref);  }  else  {     print p (escapeHTML ("Logic error, unknown choice: $choice"));  } 

Displaying the Card Information Form

The code for displaying the form is a bit different from most of those we ve written so far. Each field-generating call is passed a value parameter and override is turned on so that the value becomes the field s default value. Normally we might rely on CGI.pm s sticky behavior to initialize a form with any previous values. That doesn t work for make_ecard.pl, because sometimes the values come from the database rather than the parameter space. (This is the case if the user was just viewing the gallery page, for example.) At any rate, $card_ref always points to the card s current contents, whether they come from the database or the parameter space, so we can use it to provide the form s default values.

 sub display_entry_form  { my $card_ref = shift;   # reference to card hash      if ($card_ref->{picture} ne "') # If the card has a picture, display it      {         my $img_url = sprintf ("serve_image.pl?name=%s",                                  escape ($card_ref->{picture}));          print img ({-src => $img_url,                      -alt => escapeHTML ($card_ref->{picture})});      }      print start_form (-action => url ()),          hidden (-name => "id",                  -value => $card_ref->{id},                  -override => 1),          hidden (-name => "picture",                  -value => $card_ref->{picture},                  -override => 1),          p ("Use this form to send an electronic greeting card to a friend."),          "Person to whom you're sending the card:",          table (             Tr (                 td ("Name:") ,                  td (textfield (-name => "recip_name",                                  -value => $card_ref->{recip_name},                                  -override => 1, -size => 60))              ),              Tr (                 td ("Email address:"),                  td (textfield (-name => "recip_email",                                  -value => $card_ref->{recip_email},                                  -override => 1, -size => 60)),              )          ),          br (), br (), "Message to send to recipient:",          br (),          textarea (-name => "message",                     -value => $card_ref->{message},                     -override => 1,                     -rows => 3,                     -cols => 60,                     -wrap => "virtual"),          br (), br (),          "Please identify yourself (the person from whom the card is sent):",          table (             Tr (                 td ("Name:") ,                  td (textfield (-name => "sender_name",                                  -value => $card_ref->{sender_name},                                  -override => 1, -size => 60))              ),              Tr (                 td ("Email address:"),                  td (textfield (-name => "sender_email",                                  -value => $card_ref->{sender_email},                                  -override => 1, -size => 60)),              )          ),          br (),          p ("Would you like to be notified when the recipient views the card?"),          # Note: if $card_ref->{sender_notify} is empty, the default          # becomes the first radio button ("N"), which is what we want.          radio_group (-name => "sender_notify",                      -values => [ "N", "Y" ],                      -labels => { "N" => "No", "Y" => "Yes" },                      -default => $card_ref->{sender_notify},                      -override => 1),          br (), br (),          submit (-name => "choice",                  -value => ($card_ref->{picture} ne "" ?                              "Change" : "Select") . " Picture"),          " ",          submit (-name => "choice", -value => "Send Card"),          end_form ();  } 

The form display code adapts to the presence or absence of a picture selection in two ways. First, if the card has a picture, we display it above the form so that the user can see it. (The code generates an <img> tag that invokes our serve_image.pl script to obtain the image.) Second, the picture selection button title is Select Picture if no picture has been chosen, and Change Picture otherwise.

The form also includes a couple of hidden fields. We need the id value to identify the card. But we also carry along the picture value. That s not something the user can specify in the form, but we don t want to lose the value by not including it here. (Otherwise param("picture") will be empty when the user submits the form and we update the card record in the database using the information in that form.)

Presenting the Picture Gallery

When the user selects the Select Picture/Change Picture button, make_ecard.pl calls display_gallery() to present a gallery page that shows thumbnails of the images in the image table. This is preferable to displaying the full-size images because thumbnails require less bandwidth to transfer, load more quickly, and result in a more compact display. (Full-size image display is better limited to showing the card itself, which involves only one picture.)

We ll display each thumbnail with its name and make both of them hyperlinks so that the user can click either one to select an image for the card. To reduce the length of the gallery page, we ll arrange the images into a table and present several images per row. Six images per row significantly reduces the amount of vertical scrolling the user must do to see the entire gallery, without making the table so wide that the user likely would need to scroll horizontally to see all the images in a given row. (This is a fairly arbitrary choice, tied to my decision to use 64 64 for the thumbnail size when we built the image table. If you wanted to get fancier, you could modify the image table to store the dimensions of the thumbnails, and then attempt to determine from those values what a reasonable column count would be for the gallery table.)

What if the user takes a look at the gallery and decides not to select any of them? We could provide instructions to click the Back button, but another way to handle this issue and provide the user a sense of continuing to move forward through the card-creation process is to put a Continue button on the page along with a caption select Continue to choose no image. Thus, display_gallery() presents a page that consists of a table of images followed by a short form containing only the Continue button.

 sub display_gallery  { my ($dbh, $id) = @_;  my ($image_list_ref, $nimages, $nrows, $ncols);  my @table_row;      print start_form (),          # include card ID so next page knows which card to use          hidden (-name => "id", -value => $id, -override => 1);      # Select the names of all images available in the gallery      $image_list_ref = $dbh->selectcol_arrayref (                     "SELECT name FROM image ORDER BY name");      if (!$image_list_ref || @{$image_list_ref} == 0)      {         print p ("Sorry, there are no pictures available at this time"),              submit (-name => "choice", -value => "Continue"),              end_form ();          return;      }      print p ("To make a picture selection, click on the picture or\n"              . "on its name. To continue without choosing a picture,\n"              . "select the Continue button at the end of the page.\n");      # Determine the number of images available. Then, given the      # number of columns to display in the table, figure out how      # many rows there will be.      $nimages = @{$image_list_ref};      $ncols = 6;      $nrows = int (($nimages + $ncols - 1) / $ncols);      for my $row (0 .. $nrows - 1)      {         # construct a string containing the cells in the row          my @cell;          for my $col (0 .. $ncols - 1)          {             if (($row * $ncols) + $col < $nimages) # display image in cell              {                 my $name = $image_list_ref->[$row * $ncols + $col];                  # URL for displaying the image thumbnail                  my $img_url = sprintf ("serve_image.pl?name=%s;thumbnail=1",                                                          escape ($name));                  # URL for selecting this picture and adding it to the card                  my $select_url = url ()                          . sprintf ("?choice=add_picture;id=%d;picture=%s",                                      $id, escape ($name));                  # display image name and thumbnail; make each one a hyperlink                  # that adds the picture to the card                  push (@cell,                          a ({-href => $select_url}, escapeHTML ($name))                          . br ()                          . a ({-href => $select_url},                              img ({-src => $img_url,                                      -alt => escapeHTML ($name)}))                          );              }              else                                    # display empty cell              {                 # this happens on last row when there aren't                  # enough images to fill the entire row                  push (@cell, "&nbsp;"); # put non-breaking space in cell              }          }          push (@table_row,                  Tr (td ({-valign => "top", -align => "center"}, \@cell)));      }      print table ({-border => 1}, @table_row),          p ("Select Continue to return to main card form\n"             . "without making a picture selection.\n"),          submit (-name => "choice", -value => "Continue"),          end_form ();  } 

If the user selects the Continue button, display_gallery() posts a form containing a choice value of Continue and an id parameter that identifies the card. (The id value is contained in the form as a hidden value to make sure it gets communicated back to make_ecard.pl.) The script processes the Continue button by just redisplaying the form to show the current contents of the card.

On the other hand, if the user selects a picture name or thumbnail, each of those is linked to a URL that contains the appropriate parameters for adding the picture to the card. Each URL contains a choice value indicating that a picture was chosen, the ID number of the card, and a picture parameter indicating which picture to add:

 make_ecard.pl?choice=add_picture;id=n;picture=name 

The dispatch code for the add_picture choice sets the picture attribute of the card hash using the value of the picture parameter from the URL. (If there was already a picture value in the card hash, it will be replaced by the picture named in the URL. This way we don t lock the user into a given picture.) Then we store the modified card information in the database and redisplay the card information form:

 elsif ($choice eq "add_picture")  {     # User chose a picture from the gallery page. Extract the picture      # name and add it to the card hash, then redisplay card form.      $card_ref->{picture} = param ("picture") if param ("picture");      update_card_record ($dbh, $card_ref);      display_entry_form ($card_ref);  } 

We re making progress, but we still need to take care of the code to store the final card and send the notification email.

Sending the Card

When the user selects the Send Card button to complete the card, make_ecard.pl must take the following steps:

  • Extract the card information from the form and make sure all the required fields are present.

  • Assign an expiration date. (As a side-effect, this marks the card as done, a convention we use to prevent double submissions or attempts to tamper with the card.)

  • Store the card in the database.

  • Send email to the recipient containing instructions for viewing the card.

  • Send email to the user who s sending the card noting that it s been sent and also containing instructions for viewing it.

  • Display a confirmation page to the user.

Extracting the card from the form is just a matter of calling extract_card_params() to construct the card hash. This is done in the main dispatch logic. The other steps are handled by the send_card() function:

 sub send_card  { my ($dbh, $card_ref) = @_;  my @errors;  my %req_field_map =  (     "recip_name" => "Recipient's name",      "recip_email" => "Recipient's email address",      "message" => "The message to send to the recipient",      "sender_name" => "Your name",      "sender_email" => "Your email address"  );  my $card_life = 30;         # how long to retain the card, in days  my ($iso_date, $desc_date);  my ($url, $recip_url, $sender_url);  my %mail;      # Make sure required fields are filled in      foreach my $key (keys (%req_field_map))      {         # if field is required but missing, it's an error          if (defined ($req_field_map{$key}) && $card_ref->{$key} eq "")          {             push (@errors, $req_field_map{$key} . " must be filled in");          }      }      # Perform additional constraint checking: email fields must look      # like addresses.      if ($card_ref->{recip_email} ne ""          && !WebDB::looks_like_email ($card_ref->{recip_email}))      {         push (@errors,              "Recipient email address is not in user\@host.name format");      }      if ($card_ref->{sender_email} ne ""          && !WebDB::looks_like_email ($card_ref->{sender_email}))      {         push (@errors,              "Your email address is not in user\@host.name format");      }      if (@errors)      {         print p ("The following problems were found in the card form:");          print ul (li (\@errors));           # print error messages          display_entry_form ($card_ref);     # redisplay form          return;      }      # Get expiration date in ISO and descriptive formats      ($iso_date, $desc_date) = get_card_expiration ($card_life);      # Assign expiration date and store final card in database      $card_ref->{expiration} = $iso_date;      update_card_record ($dbh, $card_ref);      # Get full URL of current script and convert last component to name      # of card-display script. Then add card ID and viewer role parameters.      $url = url ();      $url =~ s/[^\/]+$/show_ecard.pl/;      $recip_url = $url . sprintf ("?id=%d;recip=%s",                          $card_ref->{id}, escape ($card_ref->{recip_email}));      $sender_url = $url . sprintf ("?id=%d;sender=%s",                          $card_ref->{id}, escape ($card_ref->{sender_email}));       # Send email to card recipient      $mail{To} = $card_ref->{recip_email};      $mail{From} = $card_ref->{sender_email};      $mail{Subject} = "An electronic greeting card for you!";      $mail{Message} = "  Hello, $card_ref->{sender_name} ($card_ref->{sender_email}) has sent you  an electronic greeting card.  You can view the card with your Web browser at the following address:  $recip_url  The card will be available for $card_life days (until $desc_date).  ";      sendmail (%mail);      # Send email to card sender      $mail{To} = $card_ref->{sender_email};      $mail{From} = $card_ref->{sender_email};      $mail{Subject} = "Your card to $card_ref->{recip_name}";      $mail{Message} = "  This message is for your records. You sent an electronic greeting card to:  $card_ref->{recip_name} ($card_ref->{recip_email})  You can view the card with your Web browser at the following address:  $sender_url  The card will be available for $card_life days (until $desc_date).  ";      sendmail (%mail);      # display confirmation page      print p ("Your card has been sent. Thank you for using this service.");  } 

The send_card() function first validates the form by checking for required fields and making sure the values in the email fields actually look like email addresses. (The test for required fields cannot be done earlier in the card-construction process because we enable the user to leave any of the fields blank up to the point when the card is to be sent.) It s unnecessary to trim whitespace here like we did in the validation procedure for most of the applications developed earlier in the chapter; that already has been done by the extract_card_params() function. looks_like_email() is one of the utility routines in the WebDB module. It runs a pattern test on a string to verify that it looks like a legal email address.

If any errors are found, we show the error messages and redisplay the form so that the user can correct the problems. Otherwise, the card checks out okay, so we assign it an expiration date and update the record in the database:

 # Get expiration date in ISO and descriptive formats  ($iso_date, $desc_date) = get_card_expiration ($card_life);  # Assign expiration date and store final card in database  $card_ref->{expiration} = $iso_date;  update_card_record ($dbh, $card_ref); 

get_card_expiration() calculates the expiration date, given the number of days the card should live. The date can be obtained from either MySQL or Perl, but however we get it, we ll need it in two formats. The date must be in ISO 8601 format (CCYY-MM-DD) for storage into MySQL. For display in the email messages, we ll use a more descriptive format for example, January 23, 2001 rather than 2001-01-23.

To get the expiration date from MySQL, we can use CURRENT_DATE to get today s date and DATE_ADD() to perform date arithmetic. That returns the expiration date in ISO format. To get the descriptive format, we do the same thing but pass the result to DATE_FORMAT(). Here s a query to retrieve the expiration date in both formats, where $card_life represents how many days to retain the card in the database:

 ($iso_date, $desc_date) = $dbh->selectrow_array (     "SELECT          DATE_ADD(CURRENT_DATE,INTERVAL $card_life DAY),          DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL $card_life DAY),'%M %e, %Y')"); 

Alternatively, we can get the expiration date from Perl using time() and localtime(). time() returns the current time in seconds. We can add to that value the number of seconds in 30 days, and then pass the result to localtime() to convert it to an eight-element array containing the various parts of a date. (Month, day, and year are contained in elements 3 through 5 of the array.) The year value represents the number of years relative to 1900, so we add 1900 to get the absolute year. The month is a numeric value in the range 0 to 11; we can use it as is to index into an array of month names to get the month name, or add one to get the actual month number. The code looks like this:

 @monthname = (     "January", "February", "March", "April", "May", "June",      "July", "August", "September", "October", "November", "December"  );  ($day, $month, $year) = (localtime (time () + (60*60*24*$card_life)))[3..5];  $year += 1900;      # convert year to 4-digit form  $iso_date = sprintf ("%04d-%02d-%02d", $year, $month+1, $day);  $desc_date = "$monthname[$month] $day, $year"; 

Which method of calculating the expiration date is preferable? Personally, I prefer the SELECT version because we can get both date values using a single selectrow_array() call. But that method incurs the overhead of a round trip over the network to the MySQL server. Getting the date directly from Perl does not, so it s more efficient.

After calculating the expiration date and using it to update the card record in the database, the only thing left to do is send email to the card recipient and the sender. These notification messages can be simple or fancy. On the fancy side, you ll notice when you create a card at one of the big e-card sites is that they include lots of extra stuff in their email, much of it related to advertising. That s the kind of thing you ll have to customize for yourself. For our purposes here, we ll confine the message content to the essentials related only to the card content. The message sent to the recipient will look like this:

To: recipient

From: sender

Subject: An electronic greeting card for you!

Hello, sender has sent you an electronic greeting card.

You can view the card with your Web browser at the following address:

URL

The card will be available for n days

(until expiration date).

The message for the sender looks like this:

To: sender

From: sender

Subject: Your card for recipient

This message is for your records. You sent an electronic greeting card to:

recipient

You can view the card with your Web browser at the following address:

URL

The card will be available for n days

(until expiration date).

The sender and recipient names and email addresses are contained in the card hash, and $desc_date indicates the expiration date in descriptive form. But what address should we use in the From: header, and what should the URL for requesting the card look like?

The From: address should be some valid address in case the person receiving the message attempts to reply to it. One choice is to have the message appear to come from the card sender, which is what make_ecard.pl does. (An alternative would be to have messages appear to come from your card-sending service, using an address such as e-cards@snake.net. If you use a special address, you ll have to set up an account to receive mail sent to it, or alias it to someone else.)

The more difficult thing is figuring out what kind of URL to include in the messages so that the card sender and recipient can view the card. We ll use another script, show_ecard.pl, for retrieving and displaying the card, so the initial part of the URL has to name that script. If we assume that make_ecard.pl and show_ecard.pl are both installed in the same directory on your Web server, make_ecard.pl can get its own URL and use it to figure out the URL for show_ecard.pl by replacing the last component (everything after the last slash) with show_ecard.pl :

 $url = url ();  $url =~ s/[^\/]+$/show_ecard.pl/; 

We ll also need to add some information to the URL that identifies the card. The ID number does this, but we really need something more than just the ID by itself. Remember that we want to be able to tell when the recipient requests the card for the first time so that we can mark the card record in the database as having been viewed. We also may need to notify the sender when that happens. That means we need to know who is requesting the card. Also, we want to discourage casual card browsing by people other than the sender or recipient. (That is, we don t want other people sending requests for arbitrary card ID numbers, to see what cards people are sending. We re not guaranteeing that cards are private, but the intent isn t really to provide each card as a completely public resource, either.)

One simple way to tell whether the sender or the recipient is requesting a card and to discourage browsing by outsiders is to use card-retrieval URLs that identify the role and address of the requester. If card 407 was sent by the U.S. President to the First Lady, the URLs that would be provided to each of them for viewing the card would look like this:

 .../show_ecard.pl?id=407;sender=president@whitehouse.gov  .../show_ecard.pl?id=407;recip=first.lady@whitehouse.gov 

These URLs are constructed from the base URL by appending the appropriate parameter values:

 $recip_url = $url . sprintf ("?id=%d;recip=%s",                      $card_ref->{id}, escape ($card_ref->{recip_email}));  $sender_url = $url . sprintf ("?id=%d;sender=%s",                      $card_ref->{id}, escape ($card_ref->{sender_email})); 

With the URLs in hand, we have all the information we need to generate the email messages and send them. make_ecard.pl does so using the sendmail() function from the Mail::Sendmail module discussed earlier in the chapter.

Retrieving Cards for Display

Requests to see cards are handled by the show_ecard.pl script. For purposes of reading a card from the database, the script needs only the card ID number:

 SELECT * FROM ecard WHERE id = n 

Given that we want only the card sender and recipient to view the card, however, we ll also require that the email address in the URL match the appropriate email address in the card record. If the recipient requests the card, for example, the query looks like this:

 SELECT * FROM ecard WHERE id = n AND recip_email = 'address' 

For the sender, the query checks the sender_email column rather than the recip_email column. Given this mechanism, show_ecard.pl acts as follows:

  • Look in the URL for the id parameter that identifies the card, and either a sender or recip parameter that identifies the role and email address of the person who wants to see it.

  • Look up the record that matches the id value and the email address from either the sender_email or recip_email column (depending on which one was specified in the URL). This way casual attempts to view cards will fail. (To hack in, you would have to know not only a card s ID number, but also who sent it or to whom it was sent.)

  • Display the card. show_ecard.pl generates a page containing the text of the card and, if there is a picture, an <img> tag that is handled by serve_image.pl.

  • If the card s viewed column is NULL and the requester is the recipient, this is the first time the recipient has asked to see the card. Set the viewed value to the current date and, if the sender has requested notification, send an email message confirming that the recipient has taken a look at the card.

The first part of show_ecard.pl checks the URL parameters, determines whether they re valid, and displays the card if so:

 print header (),      start_html (-title => "View Your Electronic Greeting Card",                  -bgcolor => "white");  my $id = param ("id");  my $sender = param ("sender");  my $recip = param ("recip");  my $valid = 0;  if (defined ($id))          # got the card ID, look for sender or recipient  {     if (defined ($sender))      {         $valid = 1;          show_ecard ($id, $sender, "sender_email");      }      elsif (defined ($recip))      {         $valid = 1;          show_ecard ($id, $recip, "recip_email");      }  }  if (!$valid)  {     print p ("Missing or invalid e-card parameters specified.\n"              . "Please check the URL that was sent to you.");  }  print end_html (); 

Assuming the card ID value and an email address are present in the URL, show_ecard() looks up the card from the database and displays it:

 sub show_ecard  { my ($id, $email, $col_name) = @_;  my $dbh = WebDB::connect ();  my ($sth, $ref);      $sth = $dbh->prepare (                 "SELECT * FROM ecard                  WHERE id = ? AND $col_name = ?");      $sth->execute ($id, $email);      $ref = $sth->fetchrow_hashref ();      $sth->finish ();      if (!$ref)      {         $dbh->disconnect ();          print p ("Sorry, card was not found; perhaps it has expired.");          return;      }      #  Print recipient name and email.  If a picture was selected,      #  generate an <img> tag for it.  Then display the message text and      #  sender information.      print p (escapeHTML ("To: $ref->{recip_name} ($ref->{recip_email})"));      if ($ref->{picture} ne "")      {     print img ({-src => "serve_image.pl?name=" . escape ($ref->{picture}),                  -alt => escapeHTML ($ref->{picture})});      }      print p (escapeHTML ($ref->{message}));      print p (escapeHTML ("This message was sent to you by: "                      . "$ref->{sender_name} ($ref->{sender_email})"));      # If this is a request by the recipient, set the "viewed" date if      # it hasn't yet been set; notify the sender that the recipient has      # viewed the card if the sender requested notification. Also,      # display some links for replying to to sender by email or for      # generating a reply card.      if ($col_name eq "recip_email")      {         my $mail_url = sprintf ("mailto:%s?subject=%s",                                      escape ($ref->{sender_email}),                                      escape ("Thanks for the e-card"));          print hr ();          print a ({ -href => $mail_url }, "Send mail to sender") , br ();          print a ({ -href => "make_ecard.pl" }, "Create your own e-card");          if (!$ref->{viewed})          {             $dbh->do ("UPDATE ecard SET viewed=CURRENT_DATE WHERE id = ?",                      undef, $ref->{id});              notify_sender ($ref) if $ref->{sender_notify} eq "Y";          }      }      $dbh->disconnect ();  } 

The notify_sender() function generates email to let the sender know the recipient has looked at the card:

 sub notify_sender  { my $ref = shift;        # card record  my %mail;      $mail{To} = $mail{From} = $ref->{sender_email};      $mail{Subject} = "Your e-card for $ref->{recip_name}";      $mail{Message} = "  Your card to $ref->{recip_name} ($ref->{recip_email})  has been viewed by the recipient.";      sendmail (%mail);  } 

Expiring Old Cards

The expiration column is present in the ecard table to allow old cards to be removed; there s no need to keep them around forever. Cards won t delete themselves, however, so we need to set up a mechanism to handle that task. This can be done by setting up a cron job to identify and delete cards whose expiration date has passed. If I want to expire cards at 1:15 a.m. each morning using a script expire_ecard.pl installed in my bin directory, for example, I d add a line like this to my crontab file:

 15 1 * * * /u/paul/bin/expire_ecard.pl 

The expiration script itself can be written different ways. The following version deletes the expired records, but also prints a message indicating how many records were deleted. (As usual, I m assuming that cron will mail to me the output of any programs it runs on my behalf.)

 #! /usr/bin/perl -w  # expire_ecard.pl - remove greeting cards that have expired  use strict;  use lib qw(/usr/local/apache/lib/perl);  use WebDB;  my $dbh = WebDB::connect ();    # connect to database  my $count = $dbh->do ("DELETE FROM ecard WHERE expiration < CURRENT_DATE");  $count += 0;    # convert string to number, in case it's "0E0"  print "$count e-cards have expired and were deleted\n";  $dbh->disconnect ();  exit (0); 

Suggested Modifications

Our card-sending application is the most complex of the chapter. Nevertheless, it s relatively unsophisticated, compared to some of the big sites devoted to electronic greeting cards, and there are a lot of things you could add to it. A couple of obvious additions would be to allow delivery to multiple recipients, or to allow the delivery date to be set. As written, the card is sent to a single recipient, and it s sent as soon as the user selects the Send Card button. You could also implement card categories such as get well, sympathy, good luck , birthday, or wedding. If you had such categories, you could add a SET column to the image table that would indicate which card categories each image applies to, enabling you to present category-specific image galleries. Other modifications could focus on enhancing the existing features in various ways. If you have lots of images, for example, you d probably want to present the picture gallery using multiple pages instead of displaying all pictures on the same page.

When a visitor first begins to create a card, make_ecard.pl generates a new ID number so the card can be tracked through each part of the process. At the end of the process, we assign an expiration date. If the user never completes the card, however, the expiration date remains NULL. That s a problem: The expiration mechanism is based on the value of the expiration date, so uncompleted cards never get expired. How would you address this problem, making sure not to remove records for cards that visitors currently are working on?

After a visitor completes the card construction process, it s not possible for someone else to come along and modify the card. (make_ecard.pl will notice that the expiration date has been set, which indicates that the card is finished.) However, card hijacking is possible while the card is being built, between the time that the card ID generated and the time the expiration date is assigned. How might you deal with this?

Modify expire_ecard.pl to provide information that indicates how many of the expired cards actually were viewed by the recipient.

In this chapter, you ve seen how to build several interactive applications that run from your browser. For some of these applications, we attempted to reduce the amount of work involved in generating and processing the form by storing information about it in a data structure. In the product-registration script at the beginning of the chapter, for example, we used an array to list the names, labels, and sizes of the text-input fields, as well as whether each field had to have a non-empty value at form-submission time. In Chapter 6, we ll further explore the potential for deriving information about forms in a way that can be used automatically. For applications that are tied to tables in your database, one good source of information that can be used in relation to form processing is the knowledge that MySQL itself has about the structure of the tables in your database. As we ll see, this information can be used in several ways to make your Web programming tasks easier.

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