Creating the CFC query

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. To continue the good coding practice of separating business logic and presentation, you put the code to build the query using dynamic SQL in a function in the CFC that you have been working with.

To add the new query to the CFC:

  1. Open the file gettrips.cfc file and position the pointer before the closing cfcomponent tag.
  2. Enter the following code, or do the steps in the Let Dreamweaver do it section:
    <cffunction name="getTripsFromForm" access="public" returntype="query">
    	<cfquery name="TripResult" datasource="CompassTravel">
    		SELECT tripID, tripName, tripLocation, departureDate, 
    			returnDate, price FROM trips
    	</cfquery>
    	<cfreturn TripResult>
    </cffunction>
    
  3. Add the logic for creating the WHERE clause dynamically by entering the highlighted code.
    <cffunction name="getTripsFromForm" access="public" returntype="query">
    	<!--- Create WHERE clause from data entered via 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> <cfquery name="TripResult" datasource="CompassTravel"> SELECT tripID, tripName, tripLocation, departureDate, returnDate, price FROM trips </cfquery> <cfreturn TripResult> </cffunction>
  4. Add the highlighted code to the cfquery block to use the dynamically built WHERE clause in the query:
    	<cfquery name="TripResult" datasource="CompassTravel">
    		SELECT tripID, tripName, tripLocation, departureDate, 
    			returnDate, price FROM trips
    			WHERE #PreserveSingleQuotes(WhereClause)#
    	</cfquery>
    
  5. Save the file.

Let Dreamweaver do it

You can use the Dreamweaver Extensions to create the CFC query.

To construct the query with Dreamweaver:

  1. Click the Bindings tab.
  2. Click the + button.
  3. Select Record set (Query).
  4. In the Name text box, enter TripResult.
  5. Click the New Function button.
  6. In the New function name text box, enter getTripsFromForm and click OK.
  7. From the Data source list, select CompassTravel.
  8. From the Tables list, select trips.
  9. Click the Selected radio button.
  10. Select tripName, tripLocation, departureDate, returnDate, price, and tripID.
  11. Click OK.
  12. Return to Step 3 in the To add the new query to the CFC: section.