System information

Exercise 2: Building a query that uses dynamic SQL 75
</form>
</body>
</html>
3. Save the file as tripsearchform.cfm in the my_app directory.
Reviewing the code
The following table describes the search criteria code and its function:
Exercise 2: Building a query that uses dynamic SQL
Dynamic SQL is a term that refers to SQL code that 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.
Based on the columns that you can query in this tutorial, 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 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 you can generate depending on the criteria set on the search page:
WHERE tripLocation = 'Aruba'
WHERE tripLocation Like 'C%'
WHERE tripLocation = 'China'
AND departureDate > 1/1/2001
AND price < 1500
In this 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.
Code Explanation
<form action="tripsearchresult.cfm"
method="post">
Identifies tripsearchresult.cfm as the search
action page. Results of user entry are passed
to the search action page.
<select name="tripLocationOperator">
<option value="EQUALS">is
<option value="BEGINS_WITH">begins with
</select>
Builds a drop-down list offering the query
operators for tripLocation. There must one
operator list box for each queryable column.
<input type="text" name="tripLocationValue">
Captures a value to test. There is one text
control for each queryable column.