System information

124 Chapter 11: Lesson 8: Implementing Browsing and Searching
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” on page 97, 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” on page 71, 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:
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.
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.