| Getting Started Building ColdFusion MX Applications |
|
|
Writing Your First ColdFusion Application |
|
| Getting Started Building ColdFusion MX Applications |
|
|
Writing Your First ColdFusion Application |
|
The dynamic listings developed in the previous exercise meet many of Compass Travel's requirements for locating trips. However, what if the number of trips were in the thousands or tens of thousands? Locating the right trip for a customer might be difficult and certainly time consuming. Moreover, it is very hard, if not impossible, to anticipate all the ways that a user might want to search for trips.
A better solution is to provide an interface for the user to specify the search criteria. The results of the user's criteria selection are then posted to a search results page. The logic contained within the search results page builds the SQL SELECT statement contained in a cfquery tag using ColdFusion string manipulation. Finally, the action page displays the result using the cfoutput tag. This approach of building and executing SQL statements on the fly is called dynamic SQL.
Dynamic SQL is a term used to refer to SQL code your program generates using variables before the SQL is executed. You can use dynamic SQL to accomplish tasks such as adding WHERE clauses to a search based on the fields that the user filled out on a search criteria page.
When designing the search criteria page, it is a good idea to develop a list of possible queries the user might issue when searching for the records. Since most Compass Travel customers are primarily concerned with trip locations, departure dates, and price, the following is a list of the types of queries the agents are likely to issue at Compass Travel:
There are a number of considerations to take into account, when you design a search page to capture the user's search criteria. Two of the most important considerations are as follows:
In this lesson, the Compass Travel trip coordinator will search the trips based on tripLocation, departureDate, and price. These queryable columns, therefore, will be the only ones contained in the WHERE clause of the generated SQL Statement. Further, the coordinator will have no control over which columns are returned in the record set. The query will always return the same columns to identify a trip:
In later exercises, you will reference these columns when you build the SQL SELECT statement for the cfquery in the search action page.
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:
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 |
A simple design for a search criteria page presents an operator list and data entry field for each of the queryable columns. Following this pattern, a page to collect the Compass Travel Trip search criteria looks like this:
Since all the code used to produce the search criteria page is HTML, you are not requested to build this page. You will, however, use this page (tripsearch.cfm) later in this lesson to test the search action page. The source code for the Trip Search form (tripsearch.cfm) is as follows:
<html>
<head>
<title>Trip Maintenance - Search Form</title>
</head>
<body>
<img src="images/tripsearch.gif">
<!--- Search form --->
<form action="tripsearchresult.cfm" method="post">
<table>
<!--- Field: tripLocation --->
<tr>
<td> Trip Location
</td>
<td>
<select name="tripLocationOperator">
<option value="EQUALS">is
<option value="BEGINS_WITH">begins with
</select>
</td>
<td>
<input type="text" name="tripLocationValue">
</td>
</tr>
<!--- Field: departureDate --->
<tr>
<td> Departure Date
</td>
<td>
<select name="departureOperator">
<option value="EQUALS">is
<option value="BEFORE">before
<option value="AFTER">after
</select>
</td>
<td>
<input type="text" name="departureValue">
</td>
</tr>
<!--- Field: price --->
<tr>
<td>Price
</td>
<td>
<select name="priceOperator">
<option value="EQUAL">is
<option value="GREATER">greater than
<option value="SMALLER">smaller than
</select>
</td>
<td>
<input type="text" name="priceValue">
</td>
</tr>
</table>
<p>
<input type="submit" value="Search">
</form>
</body>
</html>
The following table describes the search criteria code and its function:
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.
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>
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:
cfif tag; for example <cfif Form.tripLocationValue GT "">
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.
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.
<!--- Create Where clause for query from data entered thru search form --->
<cfset WhereClause = " 0=0 ">
<!--- Build subclause for trip location --->
<cfif Form.tripLocationValue GT "">
<cfif Form.tripLocationOperator EQ "EQUALS">
<cfset WhereClause = WhereClause & " and tripLocation = '" &
form.tripLocationValue & "'" >
<cfelse>
<cfset WhereClause = WhereClause & " and tripLocation like '" & form.tripLocationValue & "%'" >
</cfif>
</cfif>
<!--- Query returning search results --->
<cfquery name="TripResult" datasource="compasstravel">
SELECT tripName, tripLocation, departureDate, returnDate, price, tripID
FROM trips
WHERE #PreserveSingleQuotes(WhereClause)#
</cfquery>
<html>
<head>
<title>Trip Maintenance - Search Results</title>
</head>
<body>
<img src="images/tripsearchresults.gif">
<table border="0" cellpadding="3" cellspacing="0">
<tr bgcolor="Gray">
<td> Trip Name
</td>
<td> Location
</td>
<td> Departure Date
</td>
<td> Return Date
</td>
<td> Price
</td>
</tr>
<cfoutput query="TripResult">
<tr>
<td> #tripName#
</td>
<td> #tripLocation#
</td>
<td> #departureDate#
</td>
<td> #returnDate#
</td>
<td> #price#
</td>
</tr>
</cfoutput>
</table>
</body
The following table describes the code used to build the tripLocation WHERE subclause:
Note that the preceding code only builds the tripLocation subclause. In the following exercise you will add code for the other two queryable columns, departureDate and price.
In the following exercises you will test and modify tripsearchresults.cfm. In the first exercise, you will test the Trip Search Results page by entering criteria on the Trip Search form and inspecting the results. In the second exercise, you will finish the code to construct the complete WHERE clause for all three queryable columns from the Trip Search form.
Follow these steps to test the Trip Search Results page:
The Trip Results page displays several entries as follows:
The Trip Results page displays only one entry for the trip to China in the HTML table.
Obviously, either of these conditions would produce a results page with no rows.
The Search Result page should be identical to Step 3 because the code to build the WHERE clause in the Trip Results page does not include departure date and price.
In this exercise you will modify the Trip Search Results page to add the criteria needed for the departure and price query.
<!--- Query returning search results --->
departureDate WHERE subclause, enter the code in the following table immediately following the comment line.
price WHERE subclause, enter the following code after the code you entered in step 2. <!--- Build subclause for price--->
<cfif Form.priceValue GT "">
<cfif Form.priceOperator EQ "EQUALS">
<cfset WhereClause = WhereClause & " and price = " & form.priceValue>
<cfelseif Form.priceOperator EQ "GREATER">
<cfset WhereClause = WhereClause & " and price > " & form.priceValue>
<cfelseif Form.priceOperator EQ "SMALLER">
<cfset WhereClause = WhereClause & " and price < " & form.priceValue>
</cfif>
</cfif>
price and departureDate are now considered in the query, as in step 4 in the previous exercise:Now, because the departure date is considered in the query, there are no rows returned.
Note: If you planned to use many more fields as search criteria, the approach used to add departure date and price criteria to the Trip Search form is not the most elegant solution. A generic routine to handle WHERE clause string construction based on specific data types could reduce the code and be a more extensible solution then the one presented here. This more extensible approach is beyond the scope of this tutorial, however.