Exercise 5: Creating additional queries

In this exercise, you will improve the Trip List page to make it easier for the Compass Travel agents to locate trips. You must make the following improvements:

You could modify the existing query; however, you may need to use that query in the future. Instead, you can create an additional method in the gettrips.cfc component with a query that meets the preceding requirements. You can then call the new methods with the enhanced query from the triplisting.cfm page. Display the triplisting.cfm page in the browser after each step to ensure that the corresponding requirement was met.

To modify the application, you must:

Creating the query

To continue writing structured, reusable code, you create the query in a method in the existing CFC.

To create a method with enhanced query results:

  1. Open the gettrips.cfc file.
  2. Enter the following code just before the closing cfcomponent tag, or do the steps in the Let Dreamweaver do it section.
    <cffunction name="getBudgetTrips" displayName="Budget trip list" 
    	hint="List trips under $1500 alphabetically" 
    	access="public" 
    	returnType="query" 
    	output="false">
    	<cfquery name="budgetTrips" datasource="CompassTravel">
    		SELECT tripName, departureDate, returnDate, price
    		FROM trips 
    		WHERE price <= 1500
    		ORDER BY tripName
    	</cfquery>
    	<cfreturn budgetTrips>
    	</cffunction>
    
  3. Save the file.

Let Dreamweaver do it

You can use the Dreamweaver Extensions to create a CFC query and invoke the method that contains the query from the triplisting.cfm page.

To create the enhanced CFC query in Dreamweaver:

  1. Open the gettrips.cfc file and position the pointer before the closing cfcomponent tag.
  2. Click the Bindings tab.
  3. Click the + button.
  4. Select Recordset (Query).
  5. In the Name text box, enter budgetTrips.
  6. Click the New Function button.
  7. In the New function name text box, enter getBudgetTrips.
  8. Click OK.
  9. In the Data Source list, select CompassTravel.
  10. In the Table list, select trips.
  11. Click the Selected button.
  12. Select tripName, departureDate, returnDate, and price from the list of columns.
  13. In the Filter section, select price, select < from the list of operators, select Entered value, and enter 1500 in the text box.
  14. In the Sort section, select tripName, and select Ascending.
  15. Click OK.
  16. Save the file.

Invoking the new method

To use the new query, you invoke the method that contains the query.

To invoke the new method with the enhanced query:

  1. Open the triplisting.cfm file.
  2. Modify the following code, or do the steps listed in the Let Dreamweaver do it section.
    <cfinvoke
     component="cfdocs.getting_started.my_app.components.gettrips"
     method="getBudgetTrips"
     returnvariable="budgetTrips">
    </cfinvoke>
    
  3. Save the file.

Let Dreamweaver do it

You can use the Dreamweaver Extensions to create the CFC query and invoke the method that contains the query from the triplisting.cfm page.

To invoke the enhanced CFC query in Dreamweaver:

  1. Delete the cfinvoke code block.
  2. Click the Components tab.
  3. Click the + button next to cfdocs.getting_started.my_app.components, and click the + button next to gettrips.
  4. Select query getBudgetTrips and drag it to the top of the file.
  5. Change the value of the returnvariable to be the name of the query, budgetTrips, as follows:
    returnvariable="budgetTrips"
    
  6. Save the file.

Displaying the results

To display the results of the new query, you refer to the new query in the cfoutput block and include all the columns that you want to display.

To display the results of the enhanced query

  1. Change the output block in the triplisting.cfm file to output all three selected fields from the enhanced query, as follows:
    <cfoutput query="budgetTrips">
    	#tripName# 
    	departs: #departureDate# 
    	returns: #returnDate# 
    	price: #price#<BR>
    </cfoutput>
    
  2. Change the heading tag in the triplisting.cfm file from <h1>Trip List</h1> to <h1>Budget Trip List</h1>.
  3. Save both the triplisting.cfm file and the gettrips.cfc file.
  4. View the triplisting.cfm page in a browser and verify that all the new requirements were met. The revised TripListing.cfm page looks like this:


    This image shows a picture of the revised unformatted Budget Trip List page.

    The dates and prices in the preceding listing are not formatted. In Lesson 6: Creating a Main Application Page, you will enhance the look of this page.