Programming instructions

74 Lesson 2 Writing Your First ColdFusion Application
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 = "Dylan">
<cfset LastName = "Smith">
<cfset FullName = FirstName & " " & LastName>
<cfoutput>My name is #FullName#.</cfoutput>
results in the following text:
My name is Dylan Smith.
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 using 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>
<cfset WhereClause = WhereClause & " AND tripLocation like '" &
form.tripLocationValue & "%'" >
</cfif>
When you test for a string column within the WHERE clause of the SQL SELECT
statement, you must enclose the test value in quotation marks.
When you use a variable to construct a WHERE clause you must preserve the quotation
marks so that the database server does not return an error. To preserve the quotation
marks, you must use the ColdFusion
PreserveSingleQuotes function.
Constructing the initial Trip Search Results page
The following code shows how to construct the tripLocation SQL WHERE subclause.
Specifically, it uses a dynamic SQL SELECT statement built from parameters from the
Trip Search page to display the search results.
As mentioned previously, the SQL SELECT statement uses quotation marks to surround
string variable values. Unfortunately, embedded quotation marks can cause problems
when posting data to a web server. Normally, ColdFusion adds an escape character to a
string that contains a quotation mark so that an error is not generated from the web
server. The
PreserveSingleQuotes function prevents ColdFusion from automatically
escaping single quotation marks contained in the variable string passed to the function.