Enhancing the Trip Maintenance application

In this lesson, you will make enhancements to the sample Trip Maintenance application that you created in previous lessons. In , you added buttons to the Trip Detail page to browse, add, edit, delete or search for records in the database. In this lesson you will build the action pages that implement the actions for these buttons.

Page flow

You will combine the Trip Detail page and the search pages you built in the previous lessons with a maintenance action page and a navigation action page you will build in this lesson. In the final two lessons you will build a Trip Edit page to complete the trip maintenance facility. The following figure shows the flow of the finished Trip Maintenance application pages:

Image shows picture of how the pages in the Trip Maintenance application are processed.

Notice that the Trip Detail page is at the center of the Trip Maintenance application. Depending on the user action, the Trip Detail page navigates the records in the database or connects to the appropriate page to add, edit, delete, or search for records in the database.

In order for the application to process the user actions from the Trip Detail page, you must build the two action pages for the navigation and maintenance functions.

Navigation action page

This navigation action page determines which triprecord displays on the Trip Detail page after the user presses 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 display on the Trip Detail page. In this dynamic SQL statement the proper tripID is passed as a parameter to the URL then redirects it to the Trip Detail page.

Maintenance action page

The maintenance action page processes a user's maintenance request from the Trip Detail page. The request can be any of the following actions:

Delete the currently displayed trip.
Launch the search facility.
Add a new trip (implemented in the previous two lessons).
Update the currently displayed trip (implemented in the previous two lessons).

Application development steps

You will review or participate in the following application construction steps:

Steps

Description

1

Build the navigation action page to navigate and display the proper trip record.

2

Build the maintenance action page to process the user's selection on the Trip Detail page.

Steps	Description
1	Build the navigation action page to navigate and display the proper trip record.
2	Build the maintenance action page to process the user's selection on the Trip Detail page.

Using dynamic SQL to browse (navigate) the Trips table

The tripID uniquely identifies a trip in the Trips table. In Lesson 3, you displayed the Trip Detail page for a trip by passing the ID as a parameter of the URL launching the detail page. Therefore, you would navigate to 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 with a proper URL identifying the correct tripID based on the navigation button clicked. Unfortunately, because trips are added and later deleted, trips might not be ordered sequentially by ID. There can be missing IDs where trips were deleted. Therefore, if the current trip ID is 1 and the user clicks the next navigation button, it will not navigate to 2.

In order to ensure that the proper tripID is retrieved, you must create a query to 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 2, you used ColdFusion string manipulation to construct the proper SQL SELECT WHERE clause. In this lesson, you will use a similar approach to build the WHERE clause for navigation. Additionally, it is necessary to use the proper ORDER BY clause to select the correct trip row.

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

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.

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.

Limiting the number of result rows

Each of the SQL statements in the previous table return 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, since only the initial row in the result set is needed for the Trip Detail page display.

ColdFusion provides the maxRows attribute on 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.

The navigation action page

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 then 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. The following code (from the navigationaction.cfm) processes the navigation button requests on the Trip Detail page:

<!---      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")>
    ORDER BY tripID DESC

  </cfif>

</cfquery>
  <cfif TripQuery.RecordCount is 1>

  <cflocation url="tripdetail.cfm?ID=#TripQuery.tripID#">

<cfelese>
  <cflocation url="tripdetail.cfm?ID=#Form.RecordID#">

</cfif>

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" will be 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 if the user pressed a navigation button on the browse page. The X property is checked since the buttons on the detail page use image type HTML input tags. The X property is a mouse offset that gets sent when you click a graphic button.

The WHERE and ORDER BY clauses will vary depending on the navigation button clicked by the user.

<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 if the query returned a row to display. If it did, use that tripID to form a URL to navigate to using the cflocation tag. If the query returned no rows, navigate back to the detail page with current record id passed in the hidden form variable RecordID.

Code	Explanation
<cfquery name="TripQuery" dataSource="compasstravel" maxRows=1>	The `cfquery` tag identifies that a query named "TripQuery" will be 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 if the user pressed a navigation button on the browse page. The X property is checked since the buttons on the detail page use image type HTML input tags. The X property is a mouse offset that gets sent when you click a graphic button. The WHERE and ORDER BY clauses will vary depending on the navigation button clicked by the user.
<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 if the query returned a row to display. If it did, use that `tripID` to form a URL to navigate to using the `cflocation` tag. If the query returned no rows, navigate back to the detail page with current record id passed in the hidden form variable `RecordID`.

Exercise: implement trip record browsing (navigation)

Follow these steps to implement the trip record browsing functionality (navigation buttons) on the Trip Detail page. In this exercise, you will use the supplied navigation action page to implement and test the navigation buttons on the Trip Detail page.

To implement the trip record browsing functionality:

Copy the navigationaction.cfm file from the solutions subdirectory in the getting_started directory to the my_app directory.
View the tripdetail.cfm page from the my_app directory in a browser and test the navigation buttons as follows:
1. Click Next Row.
  The Trip Detail page shows information about the second trip.
2. Click Previous Row.
  The Trip Detail page shows information about the first trip.
3. Click Last Row.
  The Trip Detail page shows information about the last trip.
4. Click First Row.
  The Trip Detail page shows information about the first trip.

Building the maintenance action page

The maintenance action page (maintenanceaction.cfm) handles the user's maintenance requests. The delete request is handled directly within the maintenance action page. The search request is accomplished by linking the Trip Search page. The maintenance action page navigates to another page for data capture for add and edit requests.

You will build the tripedit.cfm page to capture the information for add and edit requests in the next lesson. The following table identifies the button clicked on the Trip Detail page with the action taken in the maintenance action page:

Button

Action taken in maintenaceaction.cfm

Search

Navigate to tripsearch.cfm built in Lesson 4.

Delete

Execute SQL DELETE statement for current tripID.

Edit

Navigate to tripedit.cfm with ID parameter in URL set to current tripID.

Add

Navigate to tripedit.cfm with ID parameter in URL set to blank.

Button	Action taken in maintenaceaction.cfm
Search	Navigate to tripsearch.cfm built in Lesson 4.
Delete	Execute SQL DELETE statement for current `tripID`.
Edit	Navigate to tripedit.cfm with ID parameter in URL set to current `tripID`.
Add	Navigate to tripedit.cfm with ID parameter in URL set to blank.

Maintenance action page code

ColdFusion creates a variable only for the button that the user clicked. Therefore, the IsDefined function is used to test which action to take. The following code is an excerpt from the maintenanceaction.cfm page that tests which action to take:

<cfif IsDefined("Form.btnSearch")>

...
<cfelseif IsDefined("Form.btnDelete")>
...
<cfelseif IsDefined("Form.btnEdit")>
...
<cfelseif IsDefined("Form.btnAdd")>
...

</cfif>

The first two buttons are the easiest to handle because they do not require building any new pages. Therefore, you will implement the functionality for the Search and Delete buttons first.

Linking the Trip Detail page to the Trip Search page

The ColdFusion cflocation tag navigates from the current page to a target HTML or CFML page. The URL attribute contains the name of the target page and any arguments. The code to navigate to the search page (tripsearch.cfm) follows:

<cflocation url="tripsearch.cfm">

Deleting the current trip record shown on the Trip Detail page

Before you can write the code to delete a trip, you must understand the underlying SQL statement to delete rows from the trips table.

SQL DELETE Statement

The SQL DELETE statement removes existing rows in a relational table. The format of the DELETE statement is as follows:

DELETE FROM table_name WHERE column_name = some_value

Consider a database table named Clients that holds information about people with the following rows:

LastName

FirstName

Address

City

Jones

Tom

50 Main St

New York

Adamson

Anita

521 Beacon St

Boston

Green

Peter

1 Broadway

New York

LastName	FirstName	Address	City
Jones	Tom	50 Main St	New York
Adamson	Anita	521 Beacon St	Boston
Green	Peter	1 Broadway	New York

To delete everyone from New York from the table, use the following statement:

DELETE FROM Clients WHERE City = 'New York'

After the database management system processed the preceding statement, the table would contain the following row only:

LastName

FirstName

Address

City

Adamson

Anita

521 Beacon St

Boston

LastName	FirstName	Address	City
Adamson	Anita	521 Beacon St	Boston

To ensure that the Trip Maintenance application deletes only the proper trip, you must use the unique tripID key when issuing the SQL DELETE. The RecordID field holds the tripID. Therefore, using the hidden RecordID input tag from the Trip Detail page, the following SQL statement deletes a row from the Trips table reads:

DELETE FROM trips WHERE tripID = #Form.RecordID#

Exercise: handle search and delete in maintenance action page

In this exercise, you will link the search function developed in to the main page. Further, you will provide code to support the trip delete function. Then you will test this functionality by deleting a trip, then searching for it to ensure it was deleted successfully.

Follow these steps to create the maintenance action page that implements the search and delete functionality.

To create the maintenance action page:

In your editor, create a CFM page.
Delete any default lines of code if your editor automatically adds them.

To handle the Search and Delete buttons from the Trip Detail page, enter the following code:

<!---      SEARCH BUTTON        --->
<cfif IsDefined("Form.btnSearch")>
    <cflocation url="tripsearch.cfm">
<!---      DELETE BUTTON       --->
<cfelseif IsDefined("Form.btnDelete")>
  <cfquery name="DeleteRecord" dataSource="CompassTravel">
    DELETE FROM trips WHERE tripID = #Form.RecordID#
  </cfquery>
  <cflocation url="tripdetail.cfm">
</cfif>

Save the page as maintenanceaction.cfm in the my_app directory.
View the tripdetail.cfm page in a browser.
The current trip is Nepal. Notice that the destination for Nepal Summit Challenge trip is Imji Himal, Nepal.
Click Search.
The Trip Search page appears.
In the Trip Search page, select begins with in the selection box for Trip Location. Then enter Imji in the trip location value field.
The following figure shows the search form looks:
Click Search. Verify that only a single trip is found whose location begins with Imji.
To return to the Trip Detail page for this trip, click the hyperlink for Imji.
In the Trip Detail page, click Delete to remove this record from the Trip database file.
Verify that the trip record was removed from the Trips database by repeating the search in step 7.

Getting Started Building ColdFusion MX Applications
Implementing the Browsing and Maintenance Database Functions