![]() ![]() ![]() |
||
|
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.
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 |
Previous Row |
SELECT tripID FROM trips WHERE tripID < 6 ORDER BY tripID DESC |
Returns the list of all |
Next Row |
SELECT tripID FROM trips WHERE tripID > 6 ORDER BY tripID |
Returns the list of all |
Last Row |
SELECT tripID FROM trips |
Returns the list of all |
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.
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.
<!--- 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>
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 Developers Guide.
The following table describes the code used to process the navigation button requests:
Code |
Explanation |
---|---|
<cfquery |
The |
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
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 |
The Trip Detail page shows information about the second trip.
The Trip Detail page shows information about the first trip.
The Trip Detail page shows information about the last trip.
The Trip Detail page shows information about the first trip.
|
||
![]() ![]() ![]() |