Using DBI in Web Applications

The DBI scripts that we've written thus far have been designed for use from the shell in a command-line environment, but DBI is useful in other contexts as well, such as in the development of Web-based applications. When you write DBI scripts that can be invoked from your Web browser, you open up new and interesting possibilities for interacting with your databases.

For example, if you display data in tabular form, you can easily turn each column heading into a link that you can select to re-sort the data on that column. This allows you to view your data in a different way with a single click, without entering any queries. Or you can provide a form into which a user can enter criteria for a database search, then display a page containing the results of the search. Simple capabilities like this can dramatically alter the level of interactivity you provide for accessing the contents of your databases. In addition, Web browser display capabilities typically are better than what you get with a terminal window, so the output often looks nicer as well.

In this section, we'll create the following Web-based scripts:

• A general browser for the tables in the samp_db database. This isn't related to any specific task we want to accomplish with the database, but it illustrates several Web programming concepts and provides a convenient means of seeing what information the tables contain.

• A score browser allowing us to see the scores for any given quiz or test. It's handy as a quick means of reviewing grade event results, and it's useful when we need to establish the grading curve for a test so we can mark papers with letter grades.

• A script to find Historical League members who share a common interest. This is done by allowing the user to enter a search phrase, then searching the interests field of the member table for that phrase. We've already written a command-line script to do this, but a Web-based version provides an instructive point of reference, allowing comparison of two approaches to the same task.

We'll write these scripts using the CGI.pm Perl module, which is the easiest way to link DBI scripts to the Web. (For instructions on obtaining the CGI.pm module, see Appendix A.) CGI.pm is so called because it helps you write scripts that use the Common Gateway Protocol defining how a Web server communicates with other programs. CGI.pm handles the details involved in a number of common housekeeping tasks, such as collecting the values of parameters passed as input to your script by the Web server. CGI.pm also provides convenient methods for generating HTML output, which reduces the chance of writing out malformed HTML compared to writing raw HTML tags yourself.

You'll learn enough about CGI.pm in this chapter to write your own Web applications, but of course not all of its capabilities are covered. To learn more about this module, see Official Guide to Programming with CGI.pm, by Lincoln Stein (John Wiley, 1998), or check the online documentation at:

http://stein.cshl.org/WWW/software/CGI/

Setting up Apache for CGI Scripts

In addition to DBI and CGI.pm, there's one more component we need for writing Web-based scripts: a Web server. The instructions here are geared toward using scripts with the Apache server, but you can probably use a different server if you like by adapting the instructions a bit.

The various parts of an Apache installation commonly are located under /usr/ local/apache. For our purposes, the most important subdirectories of this directory are htdocs (for the HTML document tree), cgi-bin (for executable scripts and programs to be invoked by the Web server), and conf (for configuration files). These directories may be located somewhere else on your system. If so, make the appropriate adjustments to the following notes.

You should verify that the cgi-bin directory is not within the Apache document tree so that the scripts within it cannot be requested as plain text. This is a safety precaution. You don't want malicious clients to be able to examine your scripts for security holes by siphoning off the text of the scripts and studying them.

To install a CGI script for use with Apache, put it in the cgi-bin directory, then change the script's ownership to the user that Apache runs as and change its mode to be executable and readable only to that user. For example, if Apache runs as a user named www, use the following commands:

% chown wwwscript_name
% chmod 500script_name
						

You'll probably need to run these commands as www or as root. If you don't have permission to install scripts in the cgi-bin directory, you can ask your system administrator to do so on your behalf.

After the script has been installed, you can request it from your browser by sending the proper URL to the Web server. Typically the URL is something like this:

							http://your.host.name/cgi-bin/script_name
						

Requesting the script from your Web browser causes it to be executed by the Web server. The script's output is sent back to you, and the result appears as a Web page in your browser.

If you want to use CGI scripts with mod_perl for better performance, do the following:

Alias /cgi-perl/ /usr/local/apache/cgi-perl

<Location /cgi-perl>
       SetHandler perl-script
       PerlHandler Apache::Registry
       Options ExecCGI
</Location>

If you are using a current version of Apache that uses a single configuration file, put all these directives in httpd.conf. If your version of Apache uses the older three-file approach to configuration information, put the Alias directive in srm.conf and the Location lines in access.conf.

Do not enable mod_perl, PerlSendHeader, or PerlSetupEnv directives for the cgi-perl directory. These are handled automatically by CGI.pm, and enabling them may introduce processing conflicts.

The URL for a mod_perl script is similar to that of a standard CGI script. The only difference is that you specify cgi-perl instead of cgi-bin:

http://your.host.name/cgi_perl/script_name
						

For more information, visit the mod_perl area of the Apache Web site at the following location:

http://perl.apache.org/

A Brief Introduction to CGI.pm

To write a Perl script that uses the CGI.pm module, put a use line near the beginning of the script, then create a CGI object that gives you access to CGI.pm methods and variables:

use CGI;
my ($cgi) = new CGI;

Our CGI scripts use CGI.pm's capabilities by invoking methods using the $cgi variable. For example, to generate a level 1 heading, we'll use the h1() method like this:

print $cgi->h1 ("My Heading");

CGI.pm also supports a style of use that allows you to call its methods as functions, without the leading '$cgi->'. I don't use that syntax here because the '$cgi->' notation is more similar to the way DBI is used, and because it prevents CGI.pm function names from conflicting with the names of any functions you may define.

Checking for Input Parameters and Writing Output

One of the things CGI.pm does for you is to take care of all the ugly details involved in collecting input information provided by the Web server to your script. All you need to do to get that information is invoke the param() method. You can get the names of all available parameters like this:

my (@param) = $cgi->param ();

To retrieve the value of a particular parameter, just name the one in which you're interested:

if (!$cgi->param ("my_param"))
{
    print "my_param is not set
";
}
else
{
    printf "my_param value: %s
", $cgi->param ("my_param");
}

CGI.pm also provides methods for generating output to be sent to the client browser. Consider the following HTML document:

<HTML>
<HEAD>
<TITLE>My Page Title</TITLE>
</HEAD>
<BODY>
<H1>My Level-1 Heading</H1>
<P>Paragraph 1.
<P>Paragraph 2.
</BODY>
</HTML>

This code uses $cgi to produce the equivalent document:

print $cgi->header ();
print $cgi->start_html (-title => "My Page Title");
print $cgi->h1 ("My Page Heading");
print $cgi->p ();
print "Paragraph 1.
";
print $cgi->p ();
print "Paragraph 2.
";
print $cgi->end_html ();

Some of the advantages of using CGI.pm to generate output instead of writing raw HTML yourself are that you can think in logical units rather than in terms of individual markup tags, and your HTML is less likely to contain errors. (The reason I say "less likely" is that CGI.pm won't prevent you from doing bizarre things, such as including a list inside of a heading.) In addition, for non-tag text that you write, CGI.pm provides automatic escaping of characters such as '<' and '>' that are special in HTML.

Use of CGI.pm output-generating methods doesn't preclude you from writing out raw HTML yourself if you want. You can mix the two approaches, combining calls to CGI.pm methods with print statements that generate literal tags.

Escaping HTML and URL Text

If you write non-tag text via CGI.pm methods such as start_html() or h1(), special characters in the text are escaped for you automatically. For example, if you generate a title using the following statement, the '&' character in the title text will be converted to '&' for you by CGI.pm:

print $cgi->start_html (-title => "A, B &C");

If you write non-tag text without using a CGI.pm output-generating method, you should probably pass it through escapeHTML() first to make sure any special characters are escaped properly. This is also true when you construct URLs that may contain special characters, although in that case you should use the escape() method instead. It's important to use the appropriate encoding method because each method treats different sets of characters as special and encodes special characters using formats that differ from one another. Consider the following short Perl script:

#! /usr/bin/perl
use CGI;
$cgi = new CGI;

$s = "x<=y, right?";
print $cgi->escapeHTML ($s) . "
"; # encode for use as HTML text
print $cgi->escape ($s) . "
";     # encode for use in a URL

If you run this script, it produces the following output, from which you can see that encoding for HTML text is not the same as encoding for URLs:

x<=y, right
x%3C%3Dy%2C%20right%3F
								
								
								
								
								
								
								
								
							

Writing Multiple-Purpose Pages

One of the primary reasons to write Web-based scripts that generate HTML instead of writing static HTML documents is that a script can produce different kinds of pages depending on the way it's invoked. All of the CGI scripts we're going to write have that property. Each one operates as follows:

The primary problem here is that you want the selection that you make from the first page to determine the contents of the second page, but Web pages normally are independent of one another unless you make some sort of special arrangements. The trick is to have the script generate pages that set a parameter to a value that tells the next invocation of the script what you want. When you first invoke the script, the parameter has no value; this tells the script to present its initial page. When you indicate what information you'd like to see, the page invokes the script again, but with the parameter set to a value that instructs the script what to do.

There are different ways of passing instructions from a page back to a script. One way is to provide a form that the user fills in. When the user submits the form, its contents are submitted to the Web server. The server passes the information along to the script, which can find out what was submitted by invoking the param() method. This is what we'll do for our third CGI script (the one that allows the user to enter a keyword for searching the Historical League directory).

Another way of specifying instructions to a script is to pass information as part of the URL that you send to the Web server when you request the script. This is what we'll do for our samp_db table browser and score browser scripts. The way this works is that the script generates a page containing hyperlinks. Selecting a link invokes the script again, but specifies a parameter value that instructs the script what to do. In effect, the script invokes itself in different ways to provide different kinds of results, depending on which link the user selects.

A script can allow itself to be invoked by sending to the browser a page containing a hyperlink to its own URL. For instance, a script my_script can write out a page containing this link:

<A HREF="/cgi-bin/my_script">Click Me!</A>

When the user clicks on the text " Click Me! ", the user's browser sends a request for my_script back to the Web server. Of course, all that will do is cause the script to send out the same page again because no other information is supplied. However, if you attach a parameter to the URL, that parameter is sent back to the Web server when the user selects the link. The server invokes the script, and the script can call param() to detect that the parameter was set and take action according to its value.

To attach a parameter to the end of the URL, add a '?' character followed by a name/value pair. To attach multiple parameters, separate them by '&' characters. For example:

/cgi-bin/my_script?name=value
/cgi-bin/my_script?name=value&name2=value2
							

To construct a self-referencing URL with attached parameters, a CGI script should begin by calling the script_name() method to obtain its own URL, then append parameters to it like this:

$url = $cgi->script_name ();    # get URL for script
$url .= "?name=value";          # add first parameter
$url .= "&1name2=value2";        # add second parameter

After the URL is constructed, you can generate a hyperlink <A> tag containing it by using CGI.pm's a() method:

print $cgi->a ({-href => $url}, "Click Me!");

It's easier to see how this works by examining a short CGI script. When first invoked, the following script, flip_flop, presents a page called Page A that contains a single hyperlink. Selecting the link invokes the script again, but with the page parameter set to tell it to display Page B. Page B also contains a link to the script, but with no value for the page parameter. Therefore, selecting the link in Page B causes the original page to be redisplayed. Subsequent invocations of the script flip the page back and forth between Page A and Page B:

use CGI;

my ($cgi) = new CGI;
my ($url) = $cgi->script_name ();   # this script's own URL

print $cgi->header ();
if ($cgi->param ("page") ne "b")    # display page A
{
    print $cgi->start_html (-title => "Flip-Flop: Page A");
    print "This is Page A.<BR>To select Page B, ";
    $url .= "?page=b";      # attach parameter to select page B
    print $cgi->a ({-href => $url}, "click here");
}
else                                # display page B
{
    print $cgi->start_html (-title => "Flip-F1lop1: Page B");
    print "This is Page B.<BR>To select Page A, ";
    print $cgi->a ({-href => $url}, "click here");
}
print $cgi->end_html ();

If another client comes along and requests flip_flop, the initial page is presented because different clients' browsers don't interfere with each other.

The value of $url actually was set in pretty cavalier fashion by the preceding examples. It's preferable to use the escape() method to encode your parameter names and values when you append them to a URL in case they contain special characters. Here's a better way to construct a URL with parameters attached to it:

$url = $cgi->script_name ();    # get URL for script
$url .= sprintf ("?%s=%s",      77191#1 1add first parameter
            $cgi->escape ("name"), $cgi->escape ("value"));
$url .= sprintf ("&%s=%s",      # add second parameter
            $cgi->escape ("name2"), $cgi->escape ("value2"));
								
								
								
								
								
								
								
							

Connecting to the MySQL Server from Web Scripts

The command-line scripts we developed in the previous section, "Putting DBI To Work," shared a common preamble for establishing a connection to the MySQL server. Our CGI scripts share some code, too, but it's a little different:

use DBI;
use CGI;
use strict;

# default connection  parameters - all missing
my ($host_name, $user_name, $password) = (undef, undef, undef);
my ($db_name) = "samp_db";

# construct data source
my ($dsn) = "DBI:mysql:$db_name";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=/usr/local/apache/conf/samp_db.cnf";

# connect to server
my (%attr) = ( RaiseError => 1 );
my ($dbh) = DBI->connect ($dsn, $user_name, $password, \%attr);

This preamble differs from the one we used for command-line scripts in the following respects:

• The first section now contains a use CGI statement.

• We no longer parse arguments from the command line.

• The code still looks for connection parameters in an option file, but doesn't use the .my.cnf file in the home directory of the user running the script (that is, the home directory of the Web server user). The Web server may run scripts for accessing other databases, and there's no reason to assume all those scripts will use the same connection parameters. Instead, we look for the option file stored in a different location (/usr/local/apache/conf/samp_db.cnf). You should change the option file pathname if you want to use a different file.

Scripts invoked through a Web server run as the Web server user, not as you. This raises some security issues because you're no longer in control after the Web server takes over. You should set the ownership of the option file to the user that the Web server runs as (perhaps www or nobody or something similar) and set the mode to 400 or 600 so that no other user can read it. Unfortunately, the file can still be read by anyone who can install a script for the Web server to execute. All they have to do is write a script that explicitly opens the option file and displays its contents in a Web page. Because their script runs as the Web server user, it will have full permission to read the file.

For this reason, you may find it prudent to create a MySQL user that has read-only (SELECT) privileges on the samp_db database, then list that user's name and password in the samp_db.cnf file, rather than your own name and password. That way you don't risk allowing scripts to connect to your database as a user that has permission to modify its tables. Chapter 11, "General MySQL Administration," discusses how to create a MySQL user account with restricted privileges.

Alternatively, you can arrange to execute scripts under Apache's suEXEC mechanism. This allows you to execute a script as a specific trusted user, and then write the script to get the connection parameters from an option file that is readable only to that user. You might do this for scripts that need write access to the database, for example.

Still another approach is to write a script to solicit a username and password from the client user, and use those values to establish a connection to the MySQL server. This is more suitable for scripts that you create for administrative purposes than for scripts that you provide for general use. In any case, you should be aware that some methods of name and password solicitation are subject to attack by anyone who can put a sniffer on the network between you and the server.

As you may gather from the preceding paragraphs, Web script security can be a tricky thing. It's definitely a topic about which you should read more for yourself, because it's a big subject to which I cannot really do justice here. A good place to start is with the security material in the Apache manual. You may also find the WWW security FAQ instructive; it's available at the following location:

http://www.w3.org/Security/Faq/

A samp_db Database Browser

For our first Web-based application, we'll develop a simple script—samp_browse—that allows you to see what tables exist in the samp_db database and to examine the contents of any of these tables interactively from your Web browser. samp_browse works like this:

• When you first request samp_browse from your browser, it connects to the MySQL server, retrieves a list of tables in the samp_db database, and sends your browser a page in which each table is presented as a link that you can select. When you select a table name from this page, your browser sends a request to the Web server asking samp_browse to display the contents of that table.

• If samp_browse receives a table name from the Web server when it's invoked, it retrieves the contents of the table and presents the information to your Web browser. The heading for each column of data is the name of the column in the table. Headings are presented as links; if you select one of them, your browser sends a request to the Web server to display the same table, but is sorted by the column you selected.

A note of warning here: The tables in the samp_db table are relatively small, so it's no big deal to send the entire contents of a table to your browser. If you edit samp_db to display tables from a different database containing large tables, you should think about adding a LIMIT clause to the row retrieval statements.

In the main body of the samp_browse script, we create the CGI object and put out the initial part of the Web page. Then we check whether or not we're supposed to display some particular table based on the value of the tbl_name parameter:

my ($cgi) = new CGI;

# put out initial part of page
							
							
							
							
							
my ($title) = "$db_name Database Browser";
print $cgi->header ();
print $cgi->start_html (-title => $title);
print $cgi->h1 ($title);

# parameters to look for in URL
my ($tbl_name) = $cgi->param ("tbl_name");
my ($sort_column) = $cgi->param ("sort_column");

# if $tbl_name has no value, display a clickable list of tables.
# Otherwise, display contents of the given table.  $sort_column, if
# set, indicates which column to sort by.
if (!$tbl_name)
{
    display_table_list ()
}
else
{
    display_table ($tbl_name, $sort_column);
}

print $cgi->end_html ();

It's easy to find out what value a parameter has because CGI.pm does all the work of finding out what information the Web server passes to the script. We need only call param() with the name of the parameter in which we're interested. In the main body of samp_browse, that parameter is tbl_name. If it's not defined or is empty, this is the initial invocation of the script and we display the table list. Otherwise, we display the contents of the table named by the tbl_name parameter, sorted by the values in the column named by the sort_column parameter. After displaying the appropriate information, we call end_html() to put out the closing HTML tags.

The display_table_list() function generates the initial page. display_table_list() retrieves the table list and writes out a single-column HTML table containing the name of one database table in each cell:

sub display_table_list
{
my ($ary_ref, $url);

    print "Select a table by clicking on its name:<BR><BR>
";

    # retrieve reference to single-column array of table names
    $ary_ref =
        $dbh->selectcol_arrayref (qq{ SHOW TABLES FROM $db_name });

    # display table with a border
    print "<TABLE BORDER>
";
    print "<TR>
";
    display_cell ("TH", "Table Name", 1);
    print "</TR>
";
    foreach my $tbl_name (@{$ary_ref})
    {
        $url = $cgi->script_name ();
        $url .= sprintf ("?tbl_name=%s", $cgi->escape ($tbl_name));
        print "<TR>
";
        display_cell ("TD", $cgi->a ({-href => $url}, $tbl_name), 0);
        print "</TR>
";
    }
    print "</TABLE>
";
}

The page generated by display_table_list() contains links that look like this:

/cgi-bin/samp_browse?tbl_name=absence
/cgi-bin/samp_browse?tbl_name=event
/cgi-bin/samp_browse?tbl_name=member
…

If the tbl_name parameter has a value when samp_browse is invoked, the script passes the value to display_table(), along with the name of the column to sort the results by. If no column is named, we sort on the first column (we can refer to columns by position, so this is easily accomplished using an ORDER BY 1 clause):

sub display_table
{
my ($tbl_name, $sort_column) = @_;
my ($sth, $url);

    # if sort column not specified, use first column
    $sort_column = "1" unless $sort_column;

    # present a link that returns user to table list page
    print $cgi->a ({-href => $cgi->script_name ()}, "Show Table List");
    print "<BR><BR>
";

    $sth = $dbh->prepare (qq{
                SELECT * FROM $tbl_name ORDER BY $sort_column
            });
    $sth->execute ();

    print "<B>Contents of $tbl_name table:</B><BR>
";

    # display table with a border
    print "<TABLE BORDER>
";
    # use column names for table headings; make each heading a link
    # that sorts output on the corresponding column
    print "<TR>
";
    foreach my $col_name (@{$sth->{NAME}})
    {
        $url = $cgi->script_name ();
        $url .= sprintf ("?tbl_name=%s", $cgi->escape ($tbl_name));
        $url .= sprintf ("&sort_column=%s", $cgi->escape ($col_name));
        display_cell ("TH", $cgi->a ({-href => $url}, $col_name), 0);
    }
    print "</TR>
";

    # display table rows
    while (my @ary = $sth->fetchrow_array ())
    {
							
							
							
							
							
        print "<TR>
";
        foreach my $val (@ary)
        {
            display_cell ("TD", $val, 1);
        }
        print "</TR>
";
    }

    $sth->finish ();
    print "</TABLE>
";
}

A table display page associates column headings with links that redisplay the table; these links include a sort_column parameter that explicitly specifies the column to sort on. For example, for a page that displays the contents of the event table, the column heading links look like this:

/cgi-bin/samp_browse?tbl_name=event&sort_column=date
/cgi-bin/samp_browse?tbl_name=event&sort_column=type
/cgi-bin/samp_browse?tbl_name=event&sort_column=event_id

Both display_table_list() and display_table() use display_cell(), a utility function that displays a value as a cell in an HTML table. This function uses a little trick of turning empty values into a non-breaking space ('&nbsp;') because in a table with borders, empty cells don't display the borders properly. Putting a non-breaking space in the cell fixes that problem. display_cell() also takes a third parameter controlling whether or not to encode the cell value. This is necessary because display_cell() is called to display some cell values that have already been encoded, such as column headings that include URL information.

# display a value in a table cell; put non-breaking
# space in "empty" cells so borders show up

sub display_cell
{
my ($tag, $value, $encode) = @_;

    $value = $cgi->escapeHTML ($value) if $encode;
    $value = " " unless $value;
    print "<$tag>$value</$tag>
";
}

If you want to write a more general script, you could alter samp_browse to browse multiple databases. For example, you could have the script begin by displaying a list of databases on the server, rather than a list of tables within a particular database. Then you could pick a database to get a list of its tables and go from there.

Grade-Keeping Project Score Browser

Each time we enter the scores for a test, we need to generate an ordered list of scores so that we can determine the grading curve and assign letter grades. Note that all we will do with this list is print it so that we can determine where each letter grade cutoff lies. Then we'll mark the grades on the students' test papers before returning them. We're not going to record the letter grades in the database because grades at the end of the grading period are based on numeric scores, not letter grades. Note also that, strictly speaking, we should have a way of entering the scores before we create a way of retrieving them. I'm saving the script for entering scores until the next chapter. In the meantime, we do have several sets of scores in the database already from the early part of the grading period. We can use our script with those scores, even in the absence of a convenient score entry method.

Our score-browsing script, score_browse, has some similarities to samp_browse, but is intended for the more specific purpose of looking at scores for a given quiz or test. The initial page presents a list of the possible grade events from which to choose, and allows the user to select any of them to see the scores associated with the event. Scores for a given event are sorted by score with the highest scores first, so you can print out the result and use it to determine the grading curve.

The score_browse script needs to examine only one parameter, event_id, to see whether or not a particular event was specified. If not, score_browse displays the rows of the event table so that the user can select one. Otherwise, it displays the scores associated with the chosen event:

# parameter that tells us which event to display scores for
my ($event_id) = $cgi->param ("event_id");

# if $event_id has a value, display the event list.
							
							
							
							
							
							
# otherwise display the scores for the given event.
if (!$event_id)
{
    display_events ()
}
else
{
    display_scores ($event_id);
}

The display_events() function pulls out information from the event table and displays it in tabular form, using column names from the query for the table column headings. Within each row, the event_id value is displayed as a link that can be selected to trigger a query that retrieves the scores corresponding to the event. The URL for each event is simply the path to score_browse with a parameter attached, which specifies the event number:

/cgi-bin/score_browse?event_id=number
						

The display_events() function is written as follows:

sub display_events
{
my ($sth, $url);
    print "Select an event by clicking on its number:<BR><BR>
";

    # get list of events
    $sth = $dbh->prepare (qq{
        SELECT event_id, date, type
        FROM event
        ORDER BY event_id
    });
    $sth->execute ();

    # display table with a border
    print "<TABLE BORDER>
";
    # use column names for table column headings
    print "<TR>
";
							
							
							
							
							
							
    foreach my $col_name (@{$sth->{NAME}})
    {
        display_cell ("TH", $col_name, 1);
    }
    print "</TR>
";

    # associate each event id with a link that will show the
    # scores for the event; return rows using a hash to make
    # it easy to refer to the event_id column value by name.
    while (my $hash_ref = $sth->fetchrow_hashref ())
    {
        print "<TR>
";
        $url = $cgi->script_name ();
        $url .= sprintf ("?event_id=%s",
                        $cgi->escape ($hash_ref->{event_id}));
        display_cell ("TD",
                    $cgi->a ({-href => $url}, $hash_ref->{event_id}), 0);
        display_cell ("TD", $hash_ref->{date}, 1);
        display_cell ("TD", $hash_ref->{type}, 1);
        print "</TR>
";
    }
    $sth->finish ();
    print "</TABLE>
";
}

When the user selects an event, the browser sends a request for score_browse that has an event ID value attached. score_browse finds the event_id parameter set and calls display_scores() to list all the scores for the specified event. The page also displays the text " Show Event List " as a link back to the initial page so that the user can return to the event list page easily. The URL for this link refers to the score_browse script but does not specify any value for the event_id parameter. display_scores() is shown in the following listing:

sub display_scores
{
my ($event_id) = shift;
my ($sth);
							
							
							
							
							
							

    # a URL without any event_id parameter will
    # cause the event list to be displayed.
    print $cgi->a ({-href => $cgi->script_name ()}, "Show Event List");
    print "<BR><BR>
";

    # select scores for the given event
    $sth = $dbh->prepare (qq{
        SELECT
            student.name, event.date, score.score, event.type
        FROM
            student, score, event
        WHERE
            student.student_id = score.student_id
            AND score.event_id = event.event_id
            AND event.event_id = ?
        ORDER BY
            event.date ASC, event.type ASC, score.score DESC
    });
    $sth->execute ($event_id);  # pass event ID as placeholder value

    print "<B>Scores for event $event_id</B><BR>
";

    # display table with a border
    print "<TABLE BORDER>
";
    # use column names for table column headings
    print "<TR>
";
    foreach my $col_name (@{$sth->{NAME}})
    {
        display_cell ("TH", $col_name, 1);
    }
    print "</TR>
";

    while (my @ary = $sth->fetchrow_array ())
    {
        print "<TR>
";
        display_cell ("TD", shift (@ary), 1) while @ary;
        print "</TR>
";
    }

    $sth->finish ();
    print "</TABLE>
";
}

The query that display_scores() runs is quite similar to one that we developed way back in Chapter 1 in the section "Retrieving Information From Multiple Tables" that describes how to write joins. In that chapter, we asked for scores for a given date because dates are more meaningful than event ID values. In contrast, when we use score_browse, we know the exact event ID. That's not because we think in terms of event IDs (we don't), but because the script presents us with a list of them from which to choose. You can see that this type of interface reduces the need to know particular details. We don't need to know an event ID; we need only to be able to recognize the event we want.

Historical League Common-Interest Searching

The samp_browse and score_browse scripts allow the user to make a selection by presenting a list of choices in an initial page, where each choice is a link that re-invokes the script with a particular parameter value. Another way to allow users to make a choice is to put a form in a page containing an editable field. This is more appropriate when the range of possible choices isn't constrained to some easily determined set of values. Our next script demonstrates this method of soliciting user input.

In the section "Putting DBI To Work," we constructed a command-line script for finding Historical League members who share a particular interest. However, that script isn't something that League members have access to; the League secretary must run the script and then mail the result to the member who requested the list. It'd be nice to make this search capability more widely available so that members could use it for themselves. Writing a Web script is one way to do that.

This script, interests, puts up a little form into which the user can enter a keyword, then searches the member table for qualifying members and displays the results. The search is done by adding the '%' wildcard character to both ends of the keyword so that it can be found anywhere in the interests column value.

The keyword form is displayed on every page so that the user can enter a new search immediately, even from those pages that display search results. In addition, the search string from the previous page is displayed in the keyword form so that if the user wants to run a similar search, the string can be edited. That way, it's not necessary to do a lot of retyping:

# parameter to look for
my ($interest) = $cgi->param ("interest");

# Display a keyword entry form.  In addition, if $interest is defined,
# search for and display a list of members who have that interest.
# Note that the current $interest value is displayed as the default
# value of the interest field in the form.

print $cgi->start_form (-method => "POST");
print $cgi->textfield (-name => "interest",
                        -value => $interest,
                        -size => 40);
print $cgi->submit (-name => "button", -value => "Search");
print $cgi->end_form ();

# run a search if a keyword was specified
search_members ($interest) if $interest;

The script communicates information to itself a little differently than samp_browse or score_browse. The interest parameter is not added to the end of a URL. Instead, the information in the form is encoded by the browser and sent as part of a POST request. However, CGI.pm makes it irrelevant how the information is sent; the parameter value is still obtained by calling param().

The function for performing the search and displaying the results is shown in the following listing. The function that formats the entry, format_html_entry(), is not shown because it's the same as the one in the gen_dir script:

sub search_members
{
my ($interest) = shift;
my ($sth, $count);

    printf "Search results for keyword: %s<BR><BR>
",
                                    $cgi->escapeHTML ($interest);
    $sth = $dbh->prepare (qq{
                SELECT * FROM member WHERE interests LIKE ?
                ORDER BY last_name, first_name
            });
    # look for string anywhere in interest field
    $sth->execute ("%" . $interest . "%");
    $count = 0;
    while (my $hash_ref = $sth->fetchrow_hashref ())
    {
        format_html_entry ($hash_ref);
        ++$count;
    }
    print $cgi->p ("$count entries found");
}