Programming instructions

Developing a search capability 69

Developing a search capability

The dynamic listings developed in the previous exercise meet many of Compass Travel’s

requirements for locating trips. However, what if the number of trips were in the

thousands or tens of thousands? Locating the right trip for a customer might be difficult

and certainly time consuming. Moreover, it is very hard, if not impossible, to anticipate

all the ways that a user might want to search for trips.

A better solution is to provide an interface for the user to specify the search criteria. The

results of the user’s criteria selection are then posted to a search results page. The logic

contained within the search results page builds the SQL SELECT statement contained in

cfquery tag using ColdFusion string manipulation. Finally, the action page displays

the result using the

cfoutput tag. This approach of building and executing SQL

statements on the fly is called dynamic SQL.

Dynamic SQL

Dynamic SQL is a term used to refer to SQL code your program generates using

variables before the SQL is executed. You can use dynamic SQL to accomplish tasks such

as adding WHERE clauses to a search based on the fields that the user filled out on a

search criteria page.

Designing the search criteria page

When designing the search criteria page, it is a good idea to develop a list of possible

queries the user might issue when searching for the records. Since most Compass Travel

customers are primarily concerned with trip locations, departure dates, and price, the

following is a list of the types of queries the agents are likely to issue at Compass Travel:

• List the trips located in Hawaii.

• Identify the trips with a price greater than $3,000.

• Show the trips departing after 11/11/2002 that are priced less than $2,000.

There are a number of considerations to take into account, when you design a search

page to capture the user’s search criteria. Two of the most important considerations are as

follows:

• For which database columns will the user be allowed to specify a search condition?

• Should the user be allowed to identify which database columns to include in the

record set?

In this lesson, the Compass Travel trip coordinator will search the trips based on

tripLocation, departureDate, and price. These queryable columns, therefore, will be the

only ones contained in the WHERE clause of the generated SQL Statement. Further, the

coordinator will have no control over which columns are returned in the record set. The

query will always return the same columns to identify a trip:

• tripName

• tripLocation

• departureDate

• returnDate