Completing the Site
| only for RuBoard - do not distribute or recompile |
This section describes the two remaining applications that must be implemented to complete the PseudEcom e-commerce Web site. One (feedback.pl) enables customers to ask questions or make comments using a free text form. The other (site_rating.pl) solicits customer opinions about the site itself using a poll.
Collecting Feedback and Questions
The feedback.pl script presents a form so that visitors can express concerns or submit comments to PseudEcom. The form contains fields for the customer name and email address (both optional; we won t require them to identify themselves unless they want to), and a field for the comment itself. We ll also include a pop-up menu that the customer can use to categorize the comments, which may be helpful for routing submissions to the appropriate support personnel. The feedback table on which the application is based contains columns corresponding to these fields:
CREATE TABLE feedback ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, date DATE NOT NULL, # when comment was submitted name VARCHAR(60), # customer name email VARCHAR(60), # customer email address category ENUM('Question','Comment', # comment type 'Complaint','Accolade', 'Problem with product', 'Other'), comment TEXT # text of comment ) The table also contains an id column so each comment has a unique identifier, and a date column for recording when comments were submitted. comment is a TEXT column rather than CHAR or VARCHAR, because the application enables users to submit comments that are more than 255 characters long.
The feedback.pl script is as follows:
#! /usr/bin/perl -w # feedback.pl - Collect customer feedback use strict; use lib qw(/usr/local/apache/lib/perl); use CGI qw(:standard escapeHTML); use WebDB; use WebDB::TableInfo; use WebDB::PseudEcom; my $dbh = WebDB::connect (); my $page = h3 ("PseudEcom Customer Feedback Page"); # Dispatch to proper action based on user selection my $choice = lc (param ("choice")); # get choice, lowercased if ($choice eq "") # initial invocation { $page .= generate_form ($dbh); } elsif ($choice eq "submit") # a comment was submitted { $page .= save_comment ($dbh); } else { $page .= p (escapeHTML ("Logic error, unknown choice: $choice")); } $dbh->disconnect (); print header (), start_html (-title => "PseudEcom Corporation", -bgcolor => "white"), add_boilerplate ($page), end_html (); exit (0); # ---------------------------------------------------------------------- # Generate the comment form sub generate_form { my $dbh = shift; my ($tbl_info, @category, $page); # Generate the "type of comment" popup based on the ENUM # values that are legal for the category column $tbl_info = WebDB::TableInfo->get ($dbh, "feedback"); @category = $tbl_info->members ("category"); $page .= p ("Please use the following form to tell us who you are\n" . "(optional) and what you'd like us to know.") . p ("If you'd like a response to your comment, be sure\n" . "to include a valid email address. Thank you."); $page .= start_form (-action => url ()) . table ( Tr ( td ("Your name:" .. br () . "(optional)"), td (textfield (-name => "name", size => "60")) ), Tr ( td ("Email address:" .. br () . "(optional)"), td (textfield (-name => "email", size => "60")) ), Tr ( td ("Type of comment:"), td (popup_menu (-name => "category", -values => \@category)) ), Tr ( td ({-valign => "top"}, "Comment:"), td (textarea (-name => "comment", -rows => "5", -cols => "60")) ), ) . br () . br () . submit (-name => "choice", -value => "Submit") . end_form (); return ($page); } # Store the contents of the comment form in the database. This routine # does the bare minimum of validation -- none! sub save_comment { my $dbh = shift; my ($stmt, $val, @placeholder, $page); $stmt = "date = CURRENT_DATE"; foreach my $param_name ("name", "email", "category", "comment") { $val = WebDB::trim (param ($param_name)); next if $val eq ""; # skip empty fields $stmt .= "," if $$stmt; # put commas between assignments $stmt .= $param_name . " = ?"; # add column name, placeholder push (@placeholder, $val); # save placeholder value } $stmt = "INSERT INTO feedback SET $stmt"; # complete the statement $dbh->do ($stmt, undef, @placeholder); # save the comment $page .= p ("Thank you for your comment."); return ($page); } The save_comment() routine doesn t validate submitted comment forms at all.You might want to add a few rudimentary checks, such as making sure that the comment field is not blank and that the email address is valid if it s not blank.
As written, this application files away comments in the database, but does nothing else with them.Without any further action on your part, the comments will just sit there forever.That might help PseudEcom come to resemble large faceless corporations that appear not to respond in any way to customer concerns, but presumably if you implement an information-gathering application such as feedback.pl, you do so in order to use the information for some purpose. For example, you might set up a cron job that runs each day, looks for comments submitted the previous day, and forwards them to someone in charge of customer relations. Or, you could modify feedback.pl itself to generate an email alert to a responsible party each time it stores a record in the feedback table.
Asking Visitors To Rate the Site
Our final application presents a poll that asks customers to rate the PseudEcom Web site. It illustrates one way to use the WebDB::Poll module in an e-commerce context. (This module was developed in Chapter 6 along with the poll and poll_candidate tables that it uses. If you haven t already created these tables, check that chapter for instructions.)
First, we need to set up the poll by adding information to the proper polling tables:
INSERT INTO poll (name,title,question,begin_date) VALUES('PseudEcom rating','Rate the PseudEcom Web site', 'How well do you like our Web site?',NOW()) INSERT INTO poll_candidate (id,seq,name,label) VALUES (LAST_INSERT_ID(),1,'like-lot','Like it a lot'), (LAST_INSERT_ID(),2,'like-little','Like it a little'), (LAST_INSERT_ID(),3,'neutral','I\'m neutral'), (LAST_INSERT_ID(),4,'dislike-little','Dislike it a little'), (LAST_INSERT_ID(),5,'dislike-lot','Dislike it a lot'), (LAST_INSERT_ID(),5,'bogus','It\'s completely bogus') Then we write a short script, site_rating.pl, that presents the poll and records responses. The script is really just a slight variation on the poll.pl script from Chapter 6. It s modified to suppress the link that enables users to see the current voting results (this is an internal use only poll) and to embed the polling form within the standard PseudEcom page boilerplate:
#! /usr/bin/perl -w # site_rating.pl - Present poll for customers to rate the PseudEcom site use strict; use lib qw(/usr/local/apache/lib/perl); use CGI qw(:standard escape escapeHTML); use WebDB; use WebDB::Poll; use WebDB::PseudEcom; my $dbh = WebDB::connect (); my $poll = WebDB::Poll->new ($dbh, "PseudEcom rating"); $poll or fatal_error ("Sorry, poll is not available"); $poll->{results_link} = 0; # turn off "current results" link my $page = h3 (escapeHTML ($poll->{data}->{title})); # Dispatch to proper action based on user selection my $choice = lc (param ("choice")); # get choice, lowercased if ($choice eq "") # initial invocation { $page .= $poll->generate_form (); } elsif ($choice eq lc ($poll->{data}->{submit_title})) # a vote was submitted { # tally vote, but ignore return value (we don't report errors) $poll->tally_vote (param ("candidate")); $page .= p ("Thanks for your input."); } else { $page .= p (escapeHTML ("Logic error, unknown choice: $choice")); } $dbh->disconnect (); print header (), start_html (-title => "PseudEcom Corporation", -bgcolor => "white"), add_boilerplate ($page), end_html (); exit (0); To see the current results from the poll, run this query:
mysql> SELECT poll_candidate.label, poll_candidate.tally -> FROM poll, poll_candidate -> WHERE poll.name = 'PseudEcom Rating' and poll..id = poll_candidate.id -> ORDER BY poll_candidate.seq; +-----------------------+-------+ | label | tally | +-----------------------+-------+ | Like it a lot | 41 | | Like it a little | 112 | | I'm neutral | 90 | | Dislike it a little | 78 | | Dislike it a lot | 19 | | It's completely bogus | 493 | +-----------------------+-------+
| only for RuBoard - do not distribute or recompile |
