Writing a Form-Based Search Application
| only for RuBoard - do not distribute or recompile |
In this section, we ll develop res_search.pl, the initial version of an application designed for searching residential real estate listings. The section describes how to set up the underlying tables, generate the search form, interpret parameters when users submit searches so that we can construct appropriate queries, and display the results.
The application relies heavily on functions in the WebDB and WebDB::TableInfo modules that were written in Chapter 6, Automating the Form-Handling Process, so you probably will need to take a look at that chapter if you haven t already read it. Specifically, we re going to construct a number of search form elements by asking MySQL to provide information about the structure of a residence table that contains home listings, and by reading the contents of lookup tables. This helps to provide some flexibility in the application because it enables you to store search form options in the database instead of coding them into your application. (For example, one lookup table lists the various locations in which users can look for matching records. The locations aren t wired into the script.) By using this strategy, you can change many of the options that the search form presents to users by updating your database rather than by rewriting your script.
Setting Up the Database Tables
The res_search.pl script uses a number of tables to do its work. The primary table is residence, which contains the listings of homes for sale. This is the base table that is, the table we ll be searching. Other tables are used to hold the allowable values for several of the search parameters that users can specify. These are res_location, res_price, res_bedrooms, and res_bathrooms, which list the values for location, price, and the number of bedrooms and bathrooms, respectively.
The residence table used to store home listings looks like this:
CREATE TABLE residence ( id INT UNSIGNED NOT NULL, # listing identifier location VARCHAR(25) NOT NULL, # general locality style ENUM('Condominium','Duplex', # type of house 'Ranch','Split-level') NOT NULL, price BIGINT UNSIGNED NOT NULL, # asking price bedrooms TINYINT UNSIGNED NOT NULL, # number of bedrooms bathrooms DECIMAL(4,1) NOT NULL, # number of bathrooms built SMALLINT UNSIGNED NOT NULL, # year when built features SET('heated garage','deck', # additional amenities 'pool','basement') NOT NULL, description VARCHAR(255), # descriptive blurb PRIMARY KEY (id), INDEX (location), INDEX (style), INDEX (price), INDEX (bedrooms), INDEX (bathrooms), INDEX (built), INDEX (features) ) The id is the listing identifier. This column should have unique values. However, it s not an AUTO_INCREMENT column, because I m assuming that the values come from an external source and that we cannot generate them automatically. (For example, in the area where I live, the identifiers consist of 6-digit numbers that are assigned by a central registry service used by all realtors.) location is the general geographic locality of the residence, such as Downtown. (I haven t included any fields for the specific street address, although clearly you would do so for a production application.) style is the architectural style of the home. price is the seller s asking price. bedrooms and bath rooms indicate the number of each type of room. bedrooms is an integer because they are measured in whole units. bathrooms is a DECIMAL(4,1) to allow for fractions, as in two and a half baths. built indicates the year the home was built. The residence table doesn t store age because age values change over time. The year a home was built is fixed, so it s better to store that instead. Besides, if we need age, we can compute it from the built value. (MySQL happens to have a built-in YEAR type, but its range begins with the year 1900. The residence table uses SMALLINT for the built column, to accommodate older homes.) features lists any additional features or amenities the residence includes. This column is a SET so that a record can list as few or as many features as are applicable. The description column provides for a string in which to store general descriptive information about the residence. description values should, of course, be taken with a grain of salt. For example, nice fixer-upper is realtor-speak for this place is a dump, and cozy generally means tiny and cramped beyond belief.
Each column that will be searchable by res_search.pl is indexed so that we can find values quickly. They are also declared NOT NULL, which should help if you have a version of MySQL earlier than 3.23 that does not allow NULL values in an index. (Also, columns can be processed more quickly and require less space if you declare them NOT NULL, regardless of your version of MySQL.)
Generating Sample Search DataThe webdb source distribution contains a directory search in which you ll find the programs described in this chapter. You ll also find a number of SQL scripts, which you should feed to the mysql program to create and initialize the tables you ll need. For example, cr_res_tbls.sql creates the residence-related tables and creates a number of records for you to search so that you don t have to create them yourself. Here s some illustrative sample data for the residence table, to give you an idea of the kind of records it contains: INSERT INTO residence (id,location,style,price,bedrooms,bathrooms,built, features,description) VALUES (397428,'Downtown','Condominium',174900,3,2,1998, 'deck,pool', 'Elegant living for the senior circuit, many amenities, no pets'), (386298,'Bradford Heights','Ranch',374900,4,3,1988, 'deck,heated garage,basement', 'Spacious quarters, quiet neighborhood, woods nearby'), (402942,'Bradford Heights','Ranch',224900,3,2.5,1992,'deck,basement', 'Quiet neighborhood, woods nearby'), (400129,'South Side','Duplex',95900,2,1,1982,'basement', 'nice fixer-upper; cozy!') Several columns in the residence table have a limited number of values, and as such would be good candidates for being represented as ENUM columns. These include the style, location, bedrooms, and bathrooms columns. ENUM columns are useful in that we can convert column descriptions to list elements in the search form automatically, using the techniques shown in Chapter 6. As you can see from the CREATE TABLE statement, however, I ve used an ENUM type only for the style column. In most cases, we ll use lookup tables to store the possible search form options. If you use a lookup table rather than an ENUM, you can add new options just by adding rows to the table. With an ENUM, you must use ALTER TABLE to modify the structure of the residence table. (For that reason, I d have used a lookup table for style, too, except that I needed an excuse to show how to perform searches against an ENUM column!) Each lookup table consists of a single column named value. However, the declaration for this column in each table depends on the kind of information to be stored. For example, in the residence table, location is a VARCHAR(25) column, so the value column in the res_location lookup table is a VARCHAR(25) as well: |
CREATE TABLE res_location ( value VARCHAR(25) NOT NULL )
res_location contains the following location names:
INSERT INTO res_location (value) VALUES ('Bradford Heights'),('Downtown'),('South Side'), ('Delford Creek'),('Fieldstone Meadows') The lookup tables associated with the bedrooms and bathrooms columns are created and initialized similarly. In each case, the value column type is the same as the type for the corresponding column in the residence table:
CREATE TABLE res_bedrooms ( value TINYINT UNSIGNED NOT NULL ) INSERT INTO res_bedrooms (value) VALUES(1),(2),(3),(4),(5),(6),(7) CREATE TABLE res_bathrooms ( value DECIMAL(4,1) NOT NULL ) INSERT INTO res_bathrooms (value) VALUES(1),(1.5),(2),(2.5),(3),(3.5),(4)
res_location, res_bedrooms, and res_bathrooms each correspond to columns in the residence table that have a small number of values. However, lookup tables can be used even for columns that are not so constrained. For example, price can have a wide range of values, but we can use a lookup table for the purpose of segmenting the possible range of values into a small set of price points. This provides a convenient way for users to select a value, because picking an item from a list generally is easier than typing in a number manually. The res_price table in which we ll store price points looks like this:
CREATE TABLE res_price ( value BIGINT UNSIGNED NOT NULL ) INSERT INTO res_price (value) VALUES(100000),(150000),(250000),(500000),(750000),(1000000)
Setting Up the Main Logic
Before writing any of the functions that implement specific phases of the search process, let s consider the application s overall operation and set up the dispatch code accordingly. Initially, the application presents a form that enables the user to specify what type of listings are of interest. When the user selects the form s Search button, res_search.pl looks up listings based on the search parameters and presents a result page that shows the matching listings. The page also will contain a new search form so that the user can enter another search directly without having to return to the preceding page. (This new form will be filled in with the same values that were submitted, to make it easy for the user to modify the parameters and resubmit it.)
The main part of the program looks like this:
#! /usr/bin/perl -w # res_search.pl - search residential real estate listings use strict; use lib qw(/usr/local/apache/lib/perl); use CGI qw(:standard escapeHTML); use WebDB; use WebDB::TableInfo; print header (), start_html (-title => "Residence Search", -bgcolor => "white"); my $dbh = WebDB::connect (); # Dispatch to proper action based on user selection my $choice = lc (param ("choice")); # get choice, lowercased if ($choice eq "") # initial invocation { display_form ($dbh); } elsif ($choice eq "search") # perform search { display_form ($dbh); # redisplay the form perform_search ($dbh); # present the results } else { print p (escapeHTML ("Logic error, unknown choice: $choice")); } $dbh->disconnect (); print end_html (); exit (0); Creating the Search Form
The search form is generated by the display_form() function and contains fields in which the user can specify values for the following aspects of the residence listings: location, architectural style, price point (maximum price the user is willing to pay), minimum number of bedrooms and bathrooms, and additional features the user wants. That s a lot of stuff, and these parameters correspond to several kinds of information: strings, numbers, ENUM values, and SET values. However, it turns out not to be difficult to generate the form because almost all the information we need is stored in the database already. The values for the architectural style and additional features are present in the column definitions for the ENUM column style and the SET column features, and the values for the other parameters are listed in the lookup tables. We ll present each list of options as a pop-up menu, except for the additional features list, which we ll display as a set of check boxes.
Let s cover the pop-up menus first. For each of these, we need to collect the information necessary for a call to popup_menu(). (If you prefer to generate radio buttons or scrolling lists, the following discussion applies without modification except that you d call radio_group() or scrolling_list().) Each pop-up menu is constructed as a list of specific values, plus we ll add an Any item so that the user doesn t have to pick a particular value. If the user picks Any from the list of locations, for example, we ll interpret that to mean any location is okay. (In other contexts, similar types of generic items might be labeled All, None of the above, and so forth, depending on your purposes.) When you provide a generic or no choice item such as Any in a pop-up menu, there are several issues to consider:
-
If you re using a lookup table to generate the menu, you might store the Any item as a row in the table, just like the other items. Similarly, if you re using an ENUM column, you might make Any a legal member of the enumeration. However, I don t like to do this. I prefer instead to obtain the nonAny values from the database, and then add Any to the list before generating the pop-up menu. There are two reasons for this. First, if you are using the column definition or lookup table for other scripts, Any may have no meaning to those scripts and would have to be removed from the list of options. If you have a data-entry application for creating new residence table records, for example, it doesn t make any sense to allow users to select Any as a particular home s location or style those columns should have some specific non-generic value. Therefore, in such an application, you d have to filter out the Any item for form generation and validation purposes. Second, you can t store Any in the list of options anyway if you re using a lookup table that contains non-string values. res_bedrooms is an example of this; it contains numbers, not strings. Any wouldn t be a legal value in the table.
-
If you want the Any item to be the default value for a list, it s easiest if you make it the first item in the list, because radio button sets and pop-up menus will display with the first item selected by default. If you add the Any item to the end of the list, you must specify its value as the default explicitly when you generate the field. (Scrolling lists display with no item selected by default, so you must specify Any explicitly as the default value no matter its position in the list.)
-
If you pass only a values parameter to the field creation call, you need to add Any only to the list of values. If you re mapping values to labels by passing a labels parameter, too, make sure to add an entry for Any to the map as well.
-
When the user submits the search form, be sure to interpret the proper value as your generic item. If you decide to change Any to All in the form generation code, for example, remember to look for All in the code that interprets form submissions.
With these points in mind, we can proceed to generate the pop-up menus for our search form. The list of locations is stored in the res_location lookup table, so we can get the list, add the Any item to the head of the list, and generate a pop-up menu from it like this:
$location_val_ref = WebDB::get_lookup_values ( $dbh, "SELECT value FROM res_location ORDER BY value"); unshift (@{$location_val_ref}, "Any"); print "Location: ", popup_menu (-name => "location", -values => $location_val_ref); If you wanted to put Any at the end of the list and make it the default, you d do so by using push rather than unshift after calling get_lookup_values(). In this case, Any no longer would be the first item, so you d have to specify it as the default explicitly:
$location_val_ref = WebDB::get_lookup_values ( $dbh, "SELECT value FROM res_location ORDER BY value"); push (@{$location_val_ref}, "Any"); print "Location: ", popup_menu (-name => "location", -values => $location_val_ref, -default => "Any"); The pop-up menus for the bedrooms and bathrooms fields are generated in much the same way as for the location field, so I won t show the code here. Creating the list of price points is a little more complicated. The values in the res_price table are just numbers, but the pop-up menu should display labels that are more descriptive (such as Up to $100,000 for the value 100000). In this case, we need a list of values as well as a hash that maps the values to labels. We can ask MySQL to format the labels for us, and if our lookup query returns two columns, get_lookup_values() will return references to both the value list and the label map.[1] Those references give us the values and labels parameters for the popup_menu() call:
[1] This behavior of get_lookup_values() is explained in Chapter 6.
($price_val_ref, $price_label_ref) = WebDB::get_lookup_values ( $dbh, # use q{ ... } so Perl leaves the $ alone q{ SELECT value, CONCAT('Up to $',FORMAT(value,0)) FROM res_price ORDER BY value }); unshift (@{$price_val_ref}, "Any"); $price_label_ref->{Any} = "Any"; print "Maximum price: ", popup_menu (-name => "price", -values => $price_val_ref, -labels => $price_label_ref); FORMAT(value,0) formats the value with commas every three digits (the 0 argument means no decimal places ), and we use CONCAT() to glue the Up to $ part to the front of the formatted value. After obtaining the value list and label map, we add Any entries to both the list and the map, and then print the pop-up menu.
For the list of architectural styles, a different approach is necessary because the values are stored in the ENUM definition for the style column, not in a lookup table. We read the table description using the WebDB::TableInfo module and extract from that information the list of legal style values:
$tbl_info = WebDB::TableInfo->get ($dbh, "residence"); @style = $tbl_info->members ("style"); unshift (@style, "Any"); print "Architectural style: ", popup_menu (-name => "style", -values => \@style); The additional features stored in the features column is a SET rather than an enumeration list. Thus, any number of features may be selected, not just one, and there is no need for an Any item. The legal values are found in the features column definition, so we refer to the table information again to get the list of members, and then use it to construct a set of check boxes:
@features = $tbl_info->members ("features"); print "Required additional features:", checkbox_group (-name => "features", -values => \@features, -linebreak => 1); Here is the entire function display_form() that generates the search form. The code is similar to what I ve just described, except that it prints the form elements in tabular format to make them line up better:
sub display_form { my $dbh = shift; my $tbl_info; my ($location_val_ref, $bed_val_ref, $bath_val_ref); my ($price_val_ref, $price_label_ref); my (@style, @features); $tbl_info = WebDB::TableInfo->get ($dbh, "residence"); # Generate the popup menus for the option lists. Add an "Any" item # to the head of each list to serve as the default value. # Values for architectural styles are in the style column definition @style = $tbl_info->members ("style"); unshift (@style, "Any"); # Values for locations, prices, and numbers of bedrooms and bathrooms # are in lookup tables. For prices, there are labels that differ from # the values. $location_val_ref = WebDB::get_lookup_values ( $dbh, "SELECT value FROM res_location ORDER BY value"); unshift (@{$location_val_ref}, "Any"); # Look up values and labels for price range. For a value of # 100000, generate a label of "Up to $100,000". ($price_val_ref, $price_label_ref) = WebDB::get_lookup_values ( $dbh, # use q{ ... } so Perl leaves the $ alone q{ SELECT value, CONCAT('Up to $',FORMAT(value,0)) FROM res_price ORDER BY value }); unshift (@{$price_val_ref}, "Any"); $price_label_ref->{Any} = "Any"; $bed_val_ref = WebDB::get_lookup_values ( $dbh, "SELECT value FROM res_bedrooms ORDER BY value"); unshift (@{$bed_val_ref}, "Any"); $bath_val_ref = WebDB::get_lookup_values ( $dbh, "SELECT value FROM res_bathrooms ORDER BY value"); unshift (@{$bath_val_ref}, "Any"); # Get additional features list from the features column definition @features = $tbl_info->members ("features"); print start_form (-action => url ()), p ("Please select the characteristics for the type of home in\n" . "which you're interested, then select the Search button."), table ({-border => 1}, Tr ( td ("Location:"), td (popup_menu (-name => "location", -values => $location_val_ref)) ), Tr ( td ("Maximum price:"), td (popup_menu (-name => "price", -values => $price_val_ref, -labels => $price_label_ref)) ), Tr ( td ("Architectural style:"), td (popup_menu (-name => "style", -values => \@style)) ), Tr ( td ("Minimum number of bedrooms:"), td (popup_menu (-name => "bedrooms", -values => $bed_val_ref)) ), Tr ( td ("Minimum number of bathrooms:"), td (popup_menu (-name => "bathrooms", -values => $bath_val_ref)) ), Tr ( td ("Required additional features:"), td (checkbox_group (-name => "features", -values => \@features, -linebreak => 1)) ) ), br (), br (), submit (-name => "choice", -value => "Search"), end_form (); } Interpreting Search Parameters and Constructing the Query
The next step is to write perform_search(), which interprets the contents of the search form when the user submits it. What we want to end up with is a query that includes an appropriate WHERE clause that reflects the conditions the user specified in the form. (We ll also have an ORDER BY clause to sort the listings, but for now we ll hardwire that in. In the section Extending the Search Application, one of the modifications we ll make is to give the user some control over the sort order, at which point we ll have to build both the WHERE and ORDER BY parts of the query from the contents of the form.)
As we examine the form parameters, we ll collect a set of conditions that qualifying listings must satisfy. However, for any parameter that is set to Any, we can skip the test on the corresponding residence table column. If the bathrooms value is 2.5, for example, the query must include a bathrooms >= 2.5 condition, but if the value is Any, no bathrooms test is needed at all. This means the number of conditions can vary from search to search. An easy way to deal with this is to collect the tests in a @condition array, and then use join() to glue them together with AND in between. Any values referenced by placeholder markers in the conditions can be collected in a @placeholder array. That gives us the information needed to build the query and run it:
sub perform_search { my $dbh = shift; my $tbl_info; my $val; my @condition; # conditions for WHERE clause my @placeholder; # values for placeholders my ($sth, $stmt, $col_list, $where); my $count; # Collect conditions corresponding to the # parameters specified in the search form. $val = param ("location"); if (defined ($val) && $val ne "Any") { push (@condition, "location = ?"); push (@placeholder, $val); } $val = param ("price"); if (defined ($val) && $val ne "Any") { push (@condition, "price <= ?"); # specified price is a maximum push (@placeholder, $val); } $val = param ("style"); if (defined ($val) && $val ne "Any") { push (@condition, "style = ?"); push (@placeholder, $val); } $val = param ("bedrooms"); if (defined ($val) && $val ne "Any") { push (@condition, "bedrooms >= ?"); # value is a minimum push (@placeholder, $val); } $val = param ("bathrooms"); if (defined ($val) && $val ne "Any") { push (@condition, "bathrooms >= ?"); # value is a minimum push (@placeholder, $val); } # Figure out the numeric value of the selected features so that # we can use it in comparisons against the features SET column. $tbl_info = WebDB::TableInfo->get ($dbh, "residence"); $val = $tbl_info->get_list_numeric_value ("features"); if ($val > 0) # are any features required? { push (@condition, "((features + 0) & ?) = ?"); push (@placeholder, $val, $val); # need value *twice* } # List of columns to select (format price with commas and # a leading dollar sign) $col_list = "id, location, CONCAT('\$',FORMAT(price,0)) AS price," . "style, bedrooms, bathrooms, built, features, description"; # WHERE clause listing the conditions $where = "WHERE " .. join (" AND ", @condition) if @condition; $where = "" unless $where; # complete query $stmt = "SELECT $col_list FROM residence $where" . " ORDER BY location LIMIT 100"; $sth = $dbh->prepare ($stmt); $sth->execute (@placeholder); $count = 0; while (my $ref = $sth->fetchrow_hashref ()) { display_listing ($ref); ++$count; } $sth->finish (); print ("Sorry, no qualifying listings were found.") if !$count; } That s the entire function. It s a mouthful, so let s examine how various parts of it work.
Testing Single-Valued Parameters
The perform_search() function begins by checking the single-valued parameters corresponding to the pop-up menu fields and constructing appropriate column tests from them. All of these can be handled pretty much the same way: Extract the value and add a condition to the query if the value isn t Any. For example, the price parameter represents the maximum price the user is willing to pay, so we want listings with price values no greater than the selected value:
$val = param ("price"); if (defined ($val) && $val ne "Any") { push (@condition, "price <= ?"); # specified price is a maximum push (@placeholder, $val); } On the other hand, the bedrooms and bathrooms parameters represent the minimum number of each type of room that homes must have, so the tests go in the other direction:
$val = param ("bedrooms"); if (defined ($val) && $val ne "Any") { push (@condition, "bedrooms >= ?"); # value is a minimum push (@placeholder, $val); } $val = param ("bathrooms"); if (defined ($val) && $val ne "Any") { push (@condition, "bathrooms >= ?"); # value is a minimum push (@placeholder, $val); } The location and style parameters are exact-valued parameters, so the tests that involve them use the = operator.
Testing Multiple-Valued Parameters
Processing the set of required features is more difficult (that is, more interesting!) than checking the pop-up menus, because multiple features might be selected, not just one of them. If fact, it might even be that none of them is selected, in which case no test is needed. The easiest and most obvious way to find records that match the features values is to concatenate the selected options into a comma-separated list of values, treat the resulting string as a SET value, and compare it to the features column:
if (@val = param ("features")) { push (@condition, "features = ?"); push (@placeholder, join (",", @val)); } Thus, if the user were to select features such as deck, pool, and basement, the result would be a condition in the WHERE clause that looks like this:
features = 'deck,pool,basement'
Unfortunately, although that s the easy thing to do, it s also incorrect. The intent of the features element is to allow the user to indicate the minimum set of features considered acceptable. If a residence has additional features, that s okay. If a home has a heated garage in addition to a deck, pool, and basement, for example, all the required features are present and it should be considered a match. The test just shown fails to find such a residence because it looks only for exact matches.
For situations in which you consider a SET value to match even if it contains other options in addition to the required ones, the condition on the SET column must be an at least test. That is, the column value must contain at least a given set of members, but is allowed to contain others. One way to perform that kind of test is to generate a complicated condition based on MySQL s FIND_IN_SET() function:
@val = (); foreach my $val (param ("features")) { push (@val, "FIND_IN_SET(?,features)"); push (@placeholder, $val); } push (@condition, join (" AND ", @val)); The resulting test looks like this:
FIND_IN_SET('deck',features) AND FIND_IN_SET('pool',features) AND FIND_IN_SET('basement',features) That ll work, but another approach is possible that uses a single comparison no matter how many options must be matched. This method is based on the fact that MySQL represents SET columns internally as numbers, with successive member values corresponding to successive bits in the number (in other words, corresponding to powers of two). For example, the features column has four members, each of which is represented internally using the following values:
| Member Name | Decimal Value | Binary Value |
| heated garage | 1 | 0001 |
| deck | 2 | 0010 |
| pool | 4 | 0100 |
| basement | 8 | 1000 |
Any value in the features column is represented internally as the sum of the individual members present in the value. For example, ' deck, pool, basement ' is represented as 2 + 4 + 8, or 14. (In binary, this is 0010 + 0100 + 1000, or 1110.) It s useful to know this because if you refer to a SET column in a numeric context within an expression, MySQL treats the value as a number rather than as a string, and you can manipulate it using bit arithmetic. That makes it possible to process a set of options in a form by mapping each option onto the appropriate numeric value to determine the composite value, and then using the result as a bit mask to find features values that have at least those bits turned on. For example, we know that the value ' deck, pool, basement ' corresponds to 14, so we can find records that have at least those elements present in the features column using the following condition:
((features+0)&14) = 14
In this expression, +0 acts to convert the value of the features column to a number (this is how you tell MySQL, treat this SET value as a number, not a string ), and &14 performs a bitwise AND (intersection) operation. The result consists of all bits that are present both in the features value and in the value 14. If the three requested features are present in a listing s features value, the result is 14 and we have a match. If other features are present as well, the result is still 14 and we have a match. However, if some required features are missing from the features value, the result will be less than 14 and there is no match.
The preceding process may seem like a lot of work, but actually can be represented in relatively little code. The biggest problem is to convert the options selected in the search form to the appropriate numeric value. That s something we can do by referring to the SET members listed in the residence table description, so let s write a function to perform this conversion as a method of the WebDB::TableInfo module.
The method takes an argument that is assumed to be the name both of the table column and the corresponding parameter in the form, converts any options that are selected in the form to numeric values, and returns the sum:
sub get_list_numeric_value { my ($self, $col_name) = @_; my @members; my (@val, $val); # Get the SET members listed in the column description @members = $self->members ($col_name); # Get the elements actually selected in the corresponding form field @val = param ($col_name); # March through the SET members. For each one selected in # the form, add its numeric value (a power of two) to the # total. This constructs a bit mask with bits turned on for # the selected SET members. $val = 0; for (my $i = 0; $i < @members; $i++) { $val += (1 << $i) if grep (/^$members[$i]$/i, @val); } return ($val); } By invoking this method, we can determine the condition that finds records containing the proper features relatively easily:
$tbl_info = WebDB::TableInfo->get ($dbh, "residence"); $val = $tbl_info->get_list_numeric_value ("features"); if ($val > 0) # are any features required? { push (@condition, "((features + 0) & ?) = ?"); push (@placeholder, $val, $val); # need value *twice* } Note that because the bitmask value is referenced twice in the condition, it s necessary to shove it onto the @placeholder array two times. Also note that the SET column need not be represented by a set of check boxes for this approach to work. It can be used with multiple-pick scrolling lists as well.
Should Search Parameters be Validated?The perform_search() function doesn t perform much validation of the form contents, which may seem curious in light of the emphasis placed on validation in Chapter 6. My rationale for this is that validation is more important when you re creating or updating information in the database than when you re just searching for it. In an application that inserts or modifies records, validation is necessary to prevent creation of records with incorrect information (either by accident on the part of innocent users or by intent on the part of malicious ones). For searching, validation is not always required. For example, in the res_search.pl application, the only options available to users are the ones we provide, so any values a user submits should be legal. In other search applications, validation can be useful or desirable. If you have the user enter a state name or abbreviation using a text field, for example, you may wish to verify that the field has a legal value before using it in a query. But what if a malicious user submits bad input? The most probable scenario is that your unfriendly visitor will supply input intended to subvert your query. This shouldn t be a problem, so long as you re properly adding form input to the query by using placeholders (as we have done in res_search.pl) or by using quote(). If you do this, your query won t be malformed no matter what input the user submits. If the user supplies bad values, it s most likely to result in a query that fails to match anything. In this sense, hack attempts are self-defeating because they make the query useless. (Note that this applies only to data values. You can t use placeholders or quote() for parts of the query such as column names, keywords, or expression operators. If you have fields in a form that correspond to those kinds of query components, you must validate them.) |
Presenting the Search Results
After examining all the relevant parameters in the search form and collecting the conditions that need to be satisfied, we have all the information necessary to construct and execute the query. This happens at the end of the perform_search() function:
# List of columns to select (format price with commas and # a leading dollar sign) $col_list = "id, location, CONCAT('\$',FORMAT(price,0)) AS price," . "style, bedrooms, bathrooms, built, features, description"; # WHERE clause listing the conditions $where = "WHERE " . join (" AND ", @condition) if @condition; $where = "" unless $where; # complete query $stmt = "SELECT $col_list FROM residence $where" . " ORDER BY location LIMIT 100"; $sth = $dbh->prepare ($stmt); $sth->execute (@placeholder); $count = 0; while (my $ref = $sth->fetchrow_hashref ()) { display_listing ($ref); ++$count; } $sth->finish (); print ("Sorry, no qualifying listings were found.") if !$count; perform_search() just displays all the listings returned by the query, subject to the constraint that it won t show more than 100 records. (The query includes a LIMIT 100 clause as a simple precaution against returning a huge number of listings.) If the user were to run a search that matches hundreds of records, that would result in a very long result page not good. One way to deal with large result sets is to display them over multiple pages; that issue is dealt with in the section Extending the Search Application. For now, we ll produce a single page display but limit its length. In the absence of more sophisticated methods that require more work to implement, LIMIT is a simple but effective way to prevent a script from swamping client browsers with gigantic amounts of information.
perform_search() passes each entry to display_listing() to handle the details of showing individual listings. display_listing() prints the information in tabular form. However, it avoids writing out a long table() invocation that lists calls to produce each table row and cell explicitly. Instead, it uses a @col_name array that lists the order in which to display columns and a %label hash that maps column names onto labels to display with the corresponding values:
sub display_listing { my $ref = shift; my @row; # array to hold display table rows my @col_name = # columns to display, in the order they should be displayed ( "id", "location", "price", "style", "bedrooms", "bathrooms", "built", "features", "description" ); my %label = # labels for each column ( "id" => "Residence ID", "location" => "Location", "price" => "Asking price", "style" => "Architectural stle", "bedrooms" => "Number of bedrooms", "bathrooms" => "Number of bathrooms", "built" => "Year built", "features" => "Additional features", "description" => "Other information" ); # Generate table rows; each one contains a label and a value foreach my $col_name (@col_name) { push (@row, Tr ( td ($label{$col_name} . ":"), # label td (escapeHTML ($ref->{$col_name})) # value )); } print hr (), table ({-border => 1}, @row); } We now have a working search application. Try it out and see how it works. But instead of being happy with it, we can think a bit about how to improve it. That s the subject of the next section.
| only for RuBoard - do not distribute or recompile |
