View comments | RSS feed

Exercise 1: Enabling users to browse trip details

In Lesson 6: Creating a Main Application Page, you added buttons to the Trip Detail page to browse records in the database. In this exercise, you build the action pages that implement the actions for these buttons.

To enable browsing, you build the Navigation Action page. This page determines which trip record appears on the Trip Detail page after the user clicks one of the navigation buttons. There is no HTML output displayed from this action page. Instead, this page uses dynamic SQL to identify the tripID that must appear on the Trip Detail page. In this dynamic SQL statement, the proper tripID is passed as a parameter to the URL, and then redirects the tripID to the Trip Detail page.

Using dynamic SQL to browse the Trips table

The tripID uniquely identifies a trip in the Trips table. In Lesson 6: Creating a Main Application Page, you displayed the Trip Detail page for a trip by passing the ID as a parameter of the URL. For example, you would use the following URL to display the detail information for a trip with the ID of 20: 

http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=20

The main objective of the Navigation Action page (navigationaction.cfm) is to navigate to the Trip Detail page using a URL that includes the correct tripID based on the navigation button clicked. Because trips are added and later deleted, trips might not be ordered sequentially by ID. There can be missing IDs where trips were deleted. For example, if the current tripID is 10 and the user clicks the Next navigation button, the tripID of the next trip might not be 11; it could be 14.

To retrieve the proper tripID, you must query the database to find out what the next (or previous, first, or last) ID is, based on the current tripID. The navigation action page uses dynamic SQL to build a query to find the appropriate ID to use.

In Lesson 4: Building Dynamic Queries, you used ColdFusion string manipulation to construct the proper SQL SELECT WHERE clause. In this lesson, you use a similar approach to build the WHERE clause for navigation. Additionally, you use the proper ORDER BY clause to select the correct trip row from the trips table.

For example, if the current tripID equals 6, the following table identifies the proper SQL statement based on the navigation button that the user clicked:

Navigation button SQL statement to navigate to correct trip ID SQL statement description

First Row

SELECT tripID FROM trips

ORDER BY tripID

Returns the list of all tripIDs in ascending (1,2,3...) order.

Previous Row

SELECT tripID FROM trips

WHERE tripID < 6

ORDER BY tripID DESC

Returns the list of all tripIDs less than 6 in descending (5,4,3...) order.

Next Row

SELECT tripID FROM trips

WHERE tripID > 6

ORDER BY tripID

Returns the list of all tripIDs greater than 6 in ascending (7,8,9...) order.

Last Row

SELECT tripID FROM trips
ORDER BY tripID DESC

Returns the list of all tripIDs in descending (99,98,97...) order.

To properly build the SQL SELECT statement for previous and next row navigation, you must know the current tripID. This is the reason for using the hidden input tag RecordID on the Trip Detail page. You can use the form variable #Form.RecordID# in the Navigation Action page for building the proper test in the WHERE clause of the SQL SELECT statement.

Limiting the number of result rows

Each of the SQL statements in the preceding table returns a result set of trips rows. The result set can range from zero to any number of rows. The Navigation Action page must limit the result set count to 1, because only the initial row in the result set appears on the Trip Detail page.

ColdFusion provides the maxRows attribute for the cfquery tag for this purpose. This attribute limits the number of result rows returned from the database. To show only a single row at a time in the Trip Detail page, set maxRows to 1.

To build the Navigation Action page:

  1. Create a blank file.
  2. Enter the following code in the blank file: 
    <!---			NAVIGATION BUTTONS 			--->
	<cfquery name="TripQuery" datasource="CompassTravel" maxrows="1">
		SELECT tripID FROM trips			
		<cfif IsDefined("Form.btnPrev.X")>
			WHERE tripID < #Form.RecordID#
			ORDER BY tripID DESC		
		<cfelseif IsDefined("Form.btnNext.X")>
			WHERE tripID > #Form.RecordID#
			ORDER BY tripID		
		<cfelseif IsDefined("Form.btnFirst.X")>
			ORDER BY tripID		
		<cfelseif IsDefined("Form.btnLast.X")>
			WHERE tripID > #Form.RecordID#
			ORDER BY tripID DESC
		</cfif>
	</cfquery>
	<cfif TripQuery.RecordCount is 1>
		<cflocation url="tripdetail.cfm?ID=#TripQuery.tripID#">
	<cfelse>
		<cflocation url="tripdetail.cfm?ID=#Form.RecordID#">
	</cfif>
  3. Save the file as navigationaction.cfm in the my_app directory.

Note: In previous lessons, you adhered to good coding practices by putting queries in ColdFusion components. To optimize performance, and because the Navigation Action page contains only a query, the page is a ColdFusion page rather than a CFC. For more information about code reuse, see Creating ColdFusion Elements in ColdFusion MX Developer's Guide.

Reviewing the code

The following table describes the code used to process the navigation button requests:

Code Explanation 
<cfquery
   name="TripQuery"
   dataSource="CompassTravel" 
   maxRows=1>

The cfquery tag identifies that a query named TripQuery is executed against the CompassTravel data source. The number of rows returned cannot exceed 1 (maxRows=1).

 
SELECT tripID FROM trips

The SQL SELECT statement will always start with "SELECT tripID FROM trips."

 
<cfif IsDefined("Form.btnPrev.X")>
   WHERE tripID < #Form.RecordID#
   ORDER BY tripID DESC

<cfelseif IsDefined("Form.btnNext.X")>
   WHERE tripID > #Form.RecordID#
   ORDER BY tripID

<cfelseif IsDefined("Form.btnFirst.X")>
   ORDER BY tripID

<cfelseif IsDefined("Form.btnLast.X")>
   WHERE tripID > #Form.RecordID#
   ORDER BY tripID DESC

</cfif>

</cfquery>

The cfif tag checks whether the user pressed a navigation button on the browse page. The X property is checked because the buttons on the detail page use image type HTML input tags. The X property is a mouse offset that gets sent when the user clicks a graphic button.

 

The WHERE and ORDER BY clauses vary depending on the navigation button that the user clicks.

 
<cfif TripQuery.RecordCount is 1>
<cflocation url="tripdetail.cfm?RecordID=#TripQuery.tripID#">
<cfelse>
<cflocation url="tripdetail.cfm?RecordID=#Form.RecordID#">
</cfif>

The cfif tag checks to see whether the query returned a row to display. If it did, that tripID is used to form a URL to navigate to using the cflocation tag. If the query returned no rows, it navigates back to the detail page with the current record ID, which passed in the hidden form variable RecordID.

To test the navigation:

  1. View the tripdetail.cfm page from the my_app directory in a browser.
  2. Click the Next Row button.

    The Trip Detail page shows information about the second trip.

  3. Click the Previous Row button.

    The Trip Detail page shows information about the first trip.

  4. Click the Last Row button.

    The Trip Detail page shows information about the last trip.

  5. Click the First Row button.

    The Trip Detail page shows information about the first trip.

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | KnowledgeBase | Bug Reporting

Version 7

Comments


No screen name said on Apr 19, 2006 at 10:03 AM :
The query to retrieve the last row in the first table is correct, but in the two following times it is listed on the page is wrong. The code snippet shows this:

WHERE tripID > #Form.RecordID#
ORDER BY tripID DESC

When it should be just this:

ORDER BY tripID DESC

Change it according in order for your script to work correctly.

 

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00000167.htm