Recipe 19.9. Performing Searches and Presenting the Results

Problem

You want to implement a web-based search interface.

Solution

Present a form containing fields that enable the user to supply search parameters such as keywords. Use the keywords to construct a database query, and then display the query results.

Discussion

A script that implements a web-based search interface provides a convenience for people who visit your web site because they don't have to know any SQL to find information in your database. Instead, visitors supply keywords that describe what they're interested in and your script figures out the appropriate statements to run on their behalf. A common paradigm for this activity involves a form containing one or more fields for entering search parameters. The user fills in the form, submits it, and receives back a new page containing the records that match the parameters.

As the writer of such a script, you must handle these operations:

Generate the form and send it to the users.
Interpret the submitted form and construct an SQL statement based on its contents. This includes proper use of placeholders or quoting to prevent bad input from crashing or subverting your script.
Execute the statement and display its result. This can be simple if the result set is small, or more complex if it is large. In the latter case, you may want to present the matching records using a paged displaythat is, a display consisting of multiple pages, each of which shows a subset of the entire statement result. Multiple-page displays have the benefit of not overwhelming the user with huge amounts of information all at once. Section 19.10 discusses how to implement them.

This recipe demonstrates a script that implements a minimal search interface: a form with one keyword field, from which a statement is constructed that returns at most one record. The script performs a two-way search through the contents of the states table. That is, if the user enters a state name, it looks up the corresponding abbreviation. Conversely, if the user enters an abbreviation, it looks up the name. The script, search_state.pl, looks like this:

#!/usr/bin/perl # search_state.pl - simple "search for state" application # Present a form with an input field and a submit button.  User enters # a state abbreviation or a state name into the field and submits the # form.  Script finds the abbreviation and displays the full name, or # finds the name and displays the abbreviation. use strict; use warnings; use CGI qw(:standard escapeHTML); use Cookbook; my $title = "State Name or Abbreviation Lookup"; print header (), start_html (-title => $title, -bgcolor => "white"); # Extract keyword parameter.  If it's present and nonempty, # attempt to perform a lookup. my $keyword = param ("keyword"); if (defined ($keyword) && $keyword !~ /^\s*$/) {   my $dbh = Cookbook::connect ();   my $found = 0;   my $s;   # first try looking for keyword as a state abbreviation;   # if that fails, try looking for it as a name   $s = $dbh->selectrow_array ("SELECT name FROM states WHERE abbrev = ?",                               undef, $keyword);   if ($s)   {     ++$found;     print p ("You entered the abbreviation: " . escapeHTML ($keyword));     print p ("The corresponding state name is : " . escapeHTML ($s));   }   $s = $dbh->selectrow_array ("SELECT abbrev FROM states WHERE name = ?",                               undef, $keyword);   if ($s)   {     ++$found;     print p ("You entered the state name: " . escapeHTML ($keyword));     print p ("The corresponding abbreviation is : " . escapeHTML ($s));   }   if (!$found)   {     print p ("You entered the keyword: " . escapeHTML ($keyword));     print p ("No match was found.");   }   $dbh->disconnect (); } print p (qq{ Enter a state name into the form and select Search, and I will show you the corresponding abbreviation. Or enter an abbreviation and I will show you the full name. }); print start_form (-action => url ()),       "State: ",       textfield (-name => "keyword", -size => 20),       br (),       submit (-name => "choice", -value => "Search"),       end_form (); print end_html ();

The script first checks to see whether a keyword parameter is present. If so, it runs the statements that look for a match to the parameter value in the states table and displays the results. Then it presents the form so that the user can enter a new search.

When you try the script, you'll notice that the value of the keyword field carries over from one invocation to the next. That's due to CGI.pm's behavior of initializing form fields with values from the script environment. If you don't like this behavior, defeat it and make the field come up blank each time by supplying an empty value explicitly and an override parameter in the textfield⁠(⁠ ⁠ ⁠) call:

print textfield (-name => "keyword",                  -value => "",                  -override => 1,                  -size => 20);

Alternatively, clear the parameter's value in the environment before generating the field:

param (-name => "keyword", -value => ""); print textfield (-name => "keyword", -size => 20);