Programming instructions

70 Lesson 2 Writing Your First ColdFusion Application
• 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.
Understanding search query operators
Now that you decided on the queryable columns (tripLocation, departureDate, and
price), you can build a simple form that allows the user to enter values for each of these
fields. If the user enters a value (for example, Boston) for the tripLocation field and leaves
the other two fields blank, the search results page constructs the following SQL
statement:
SELECT tripName, tripLocation, departureDate,
returnDate, price, tripID
FROM trips
WHERE tripLocation = 'Boston'
But, what if the user wants a list of all the trips where the trip location begins with a "B"?
SQL is well-suited for this type of query. When designing the Search Criteria page, you
must decide which operators to support for each of the queryable columns. The
operators that you use depends on the data type of the SQL column.
For example, price is a numeric data type. The user can specify any of the following:
• price is 5000
• price less than 600
• price greater than 1500
Unlike trip location, it is not semantically correct to consider whether a price begins with
"B". Typical SQL string operators are equals, starts with, contains, and ends with.
While many more operators are permissible, for simplification, you can use the following
the operators for the Compass Travel queryable columns:
Queryable column Query operators
tripLocation is, begins with
departureDate is, before, after
price is, greater than, less than