System information

76 Chapter 7: Lesson 4: Building Dynamic Queries
The action page invokes a method that builds the WHERE clause so that the SQL SELECT
statement retrieves the information that the user requests. Then, the action page displays an
HTML table with the results of the user query using the
cfoutput block.
Building the WHERE Clause with the cfif and cfset tags
The WHERE clause in a SQL SELECT statement 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 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>
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:
<cfset FirstName = "Wilson">
<cfset LastName = "Gato">
<cfset FullName = FirstName & " " & LastName>
<cfoutput>My name is #FullName#.</cfoutput>
results in the following text:
My name is Wilson Gato.
For each search criterion on the Trip Search form, the code within the Trip Search Results page
must do the following:
• Verify that the user entered data in the search criterion’s value field. To do so, you use the cfif
tag; for example,
<cfif Form.tripLocationValue GT "">.
• If data was entered, construct a WHERE subclause by concatenating the following:
■ The SQL keyword AND
■ The corresponding SQL column name (in the Trip Search example, tripLocation) for the
search criterion
■ The SQL operator equivalent of the search query operator
■ The test value entered by the user
The following code shows the creation of the WHERE subclause:
<cfif Form.tripLocationOperator EQ "EQUALS">
<cfset WhereClause = WhereClause & " AND tripLocation = '" &
form.tripLocationValue & "'" >
<cfelse>