MACROMEDIA COLDFUSION MX 61-GETTING STARTED BUILDING COLDFUSION..., Getting Started

Page: 66 / 134

66 Chapter 6: Lesson 2: Writing Your First ColdFusion Application
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 a
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 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
•
price
•
tripID
In later exercises, you will reference these columns when you build the SQL SELECT statement
for the
cfquery
in the search action page.