Programming instructions

Developing a search capability 73
Building the Search Results page
Based on the queryable columns identified earlier, the SQL query to display the search
results would look like this:
SELECT tripName, tripLocation, departureDate, returnDate, price, tripID
FROM trips
The purpose of the Trip Search form is to supply the data needed to build the WHERE
clause to finish this SQL SELECT statement and constrain the query according to the
user’s input.
When the user enters the search criteria on the Trip Search form and clicks the Search
button, the form fields are then posted to the Trip Search Results page. The posted field
values compose the WHERE clause in the SQL SELECT statement. The following
example lists the WHERE clauses that can be generated depending on the criteria set on
the search page:
WHERE tripLocation = 'China'
WHERE tripLocation Like 'C%'
WHERE tripLocation = 'China'
AND departureDate > 1/1/2001
AND price < 1500
In the previous example, the SQL AND operator joins the search condition clauses. To
simplify the trip search example, you will use the SQL AND operator to combine all the
search condition clauses. A more sophisticated search criteria page might present the user
a choice of using AND or OR to connect one search criterion with the others.
The search action page uses a SQL SELECT statement to display an HTML table with
the results of the user query using the
cfoutput block.
Building the WHERE Clause with the cfif and cfset
The WHERE clause in a SQL SELECT is a string. You use the CFML
cfset and cfif
tags to conditionally build the WHERE clause depending on values passed to the search
action page. The
cfset statement creates a new variable or changes the value of an
existing variable. For example, to create a variable named
color and initialize its value to
red you use the following statement:
<cfset color = "red">
The cfif tag instructs the program to branch to different parts of the code depending on
whether a test evaluates to True or False. For example, to have some code execute if the
color variable is equal to red, and other code execute if it is not, you use the following
pseudocode:
<cfif color EQ "red">
... statements for color red
<cfelse>
... statements for other than red
</cfif>