Exercise 2: Retrieving the information for the trip list

Relational database management systems process SQL instructions sent to them from various applications. ColdFusion sends SQL statements to database managers to manipulate data. ColdFusion needs a way to know to which database manager to send a specific SQL string for evaluation. In CFML, the cfquery tag serves this purpose. You will use the SQL SELECT statement and the cfquery tag to create a dynamic version of the Trip List page described earlier in this lesson. In this example, you use the cfquery tag to return all the trip names found in the tripName column in the Compass Travel Trips table. To use the SQL SELECT statement to dynamically retrieve this information, you must execute the SQL SELECT statement between the cfquery start and end tags.

To retrieve the trip list:

  1. If you are working in Dreamweaver, ensure that the site that you created in Lesson 1 is the current site. For more information, see Configuring Dreamweaver MX for ColdFusion development.
  2. Open a new file and save it as triplisting.cfm in the my_app directory.

    Note: If you are working in Dreamweaver, ensure that you select ColdFusion Templates in the Save As Type list.

  3. Enter the following code, or do the steps listed in the Let Dreamweaver do it section.
    <cfquery name="TripList" datasource="CompassTravel">
    	SELECT trips.tripName FROM trips
    </cfquery>
    
  4. Save the file.

Let Dreamweaver do it

Dreamweaver lets you create a query without having to enter the code.

To create a query in Dreamweaver:

  1. Click the Bindings tab in the application panel.
  2. Click the + button.
  3. Select Recordset (Query).
  4. In the Name text box, enter TripList.
  5. From the Data source list, select CompassTravel.
  6. From the Tables list, select trips.
  7. Next to Columns, click Selected.
  8. Select tripName.
  9. Click OK.
  10. Save the file.

Your ColdFusion application page retrieves the information for the trip list. Next, you need to display the information.

Reviewing the code

The following table describes the code used to build the query:

Code

Explanation

<cfquery name="TripList" datasource="CompassTravel">

ColdFusion query named TripList. Submits any SQL statement between the cfquery start and end tags to the data source specified in the datasource attribute.

SELECT trips.tripName FROM trips

SQL SELECT statement to retrieve all tripName(s) from the trips table.