User Guide

70 Chapter 6: Lesson 2: Writing Your First ColdFusion Application

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:

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:

... statements for color red

... statements for other than red

</cfif>

Building a SQL WHERE clause in code is largely an exercise in string concatenation. The &

operator combines two strings in ColdFusion. For example, the following code snippet:

<cfoutput>My name is #FullName#.</cfoutput>

results in the following text:

My name is Dylan Smith.