| Getting Started Building ColdFusion MX Applications |
|
|
Creating a Main Application Page |
|
| Getting Started Building ColdFusion MX Applications |
|
|
Creating a Main Application Page |
|
In this lesson you will enhance the Trip Maintenance application that you created in Lesson 2. You will modify the application to include a main application page that lets Compass Travel employees do these tasks:
The following list identifies the application components that you will create in this lesson:
The primary users of these components will be the Compass Travel coordinators and agents, not the general public.
By design, the Trip Search Results page displays a subset of the information about a trip. To get additional information about any of the trips displayed, the user should be able to click on any row to display the detailed trip data.
In the following exercise, you build a Trip Detail page to provide all the information about a particular trip that is stored in the Compass Travel trips database. The following figure shows an example of the Trip Detail page that you will build:
After you complete the Trip Maintenance application in this tutorial, you will use this Trip Detail page in several ways:
http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24
Follow these steps to build a Trip Detail page.
<cfquery name="TripQuery" dataSource="compasstravel" maxRows=1>
SELECT tripID, tripName, tripDescription, tripLocation, departureDate,
returnDate, price, tripLeader, photo, baseCost, numberPeople, depositRequired
FROM TRIPS
<cfif IsDefined("URL.ID")>
WHERE tripID = #URL.ID#
</cfif>
</cfquery>
cfoutput code after the code you added in step 2.<cfoutput query="TripQuery"> <img src="images/tripmaintenance.gif"> </cfoutput>
</cfoutput> tag you added in step 3. Alternatively, you can copy this HTML code from the tripdetail.txt file in the solutions directory.<table>
<tr>
<td valign="top"> Trip Name:
</td>
<td> #tripName#
</td>
</tr>
<tr>
<td valign="top"> Description:
</td>
<td> #tripDescription#
</td>
</tr>
<tr>
<td valign="top"> Location:
</td>
<td> #tripLocation#
</td>
</tr>
<tr>
<td valign="top"> Departure Date:
</td>
<td> #departureDate#
</td>
</tr>
<tr>
<td valign="top"> Return Date:
</td>
<td> #returnDate#
</td>
</tr>
<tr>
<td valign="top"> Price:
</td>
<td> #price#
</td>
</tr>
<tr>
<td valign="top"> Base Cost:
</td>
<td> #baseCost#
</td>
</tr>
<tr>
<td valign="top"> Trip Leader:
</td>
<td> #tripLeader#
</td>
</tr>
<tr>
<td valign="top"> Number People:
</td>
<td> #numberPeople#
</td>
</tr>
<tr>
<td valign="top"> Deposit Required:
</td>
<td> #depositRequired#
</td>
</tr>
<tr>
<td valign="top"> Photo File:
</td>
<td> #photo#
</td>
</tr>
</table>
<cfoutput query = "TripQuery"> line:<html><head> <title> Trip Maintenance - View Record </title> </head> <body>
</body> </html>
tripdetail.cfm in the my_app directory.
The following page shows the expected result:
The following table describes the ColdFusion code used to build the Trip Detail page:
As you can see, you can build comprehensive database query applications using CFML and dynamic SQL. To further test the new Trip Detail page that you created, you will link it to the search facility that you built in Lesson 2. However, before you link the search facility you built in Lesson 2, you need to understand a potential security risk using dynamic SQL. The following section describes this risk and how to code around it.
To reduce round trips between the client and the database server, many SQL database servers permit the client to submit multiple SQL statements in a single request, separated by a semicolon (;). For these database managements systems, the following SQL request is valid:
DELETE from trips where tripLocation = 'China'; SELECT tripName from trips
This request may be an efficient way to list the trips that remain after the database management system removes the China trip. Problems arise when the SQL statement is built dynamically.
In the Trip Maintenance application, when the client program or user passes an ID in the URL that calls the Trip Detail page, the page displays the relevant trip information. The following code builds the correct WHERE clause supporting this behavior:
<cfif IsDefined("URL.ID")>
WHERE tripID = #URL.ID#
</cfif>
If a user called the Trip Detail page using the following statement:
http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24;DROP+trips
the SQL database management system executes the proper SQL SELECT statement, then immediately erases the Trips table from the database.
To ensure that your application is protected from such an attack, you can exploit the fact that the ID must be a numeric value. The CFML Val function returns the numeric value at the beginning of a string expression. You can use the Val function as follows:
<cfif IsDefined("URL.ID")>
WHERE tripID = #Val(URL.ID)#
</cfif>
Now if non-numeric data is passed within the URL ID field, the Val statement returns 0, and the trip with ID 0 displays (if one exists). If the user enters the previously cited URL (http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24;DROP+trips), the application ignores the non-numeric values and displays the trip information of trip ID 24.
Warning: The exercises in this tutorial ignore the dynamic SQL risk from attack. You must use the Val function in your applications to eliminate the risk.
In the next exercise you will modify the Trip Search Results page to let the user view the details of any trip. To do this, you will convert the trip name entries in the results page to links, which will display the trip's detailed information in the detail page.
Use the following steps to link the Trip Search Results page (tripsearchresult.cfm) to the Trip Detail page (tripdetail.cfm).
#tripName# in the cfoutput block with the following code:<a href="tripdetail.cfm?ID=#URLEncodedFormat(tripID)#"> #tripName# </a>
Note: The URLEncodedFormat is a ColdFusion function that returns a URL-encoded string. Spaces are replaced with %20, and nonalphanumeric characters with equivalent hexadecimal escape sequences. The function lets you pass arbitrary strings within a URL, because ColdFusion automatically decodes URL parameters that are passed to the page.
The Trip Search Results page displays a hyperlink for each trip name listed, as the following figure shows:
You might notice that the dates and prices in both the Trip Detail and Trip Search Results pages are unformatted. You will improve the appearance of the application in the next exercise.
The Trip Maintenance search now provides a useful drill-down mechanism for locating trips. While this application is functionally sound, the appearance of the dates and dollar amounts could be improved.
ColdFusion provides several functions to improve the application appearance. The DateFormat and DollarFormat functions format dates and currency variables. ColdFusion provides.
Another part of the application that could be improved is the Trip Search Results table. In a large list, it is sometimes difficult to correctly read the contents of a row in the middle of the table because it is sandwiched between two other rows. To avoid mistakes in reading the table, it would be helpful to alternate the background color of each row in the table.
The HTML table row <tr> tag has a bgcolor attribute to change the background color for a given row. To highlight every other row, the background color could be changed alternatively to yellow or white. To change the background color for each row, you must determine whether the row is an odd or even row. Therefore, you must obtain the sequence number of the current row and test if it is evenly divisible by 2.
As described in Chapter 2, ColdFusion offers a modulus function (MOD) that returns the remainder (modulus) after a number is divided by a divisor; for example,10 MOD 3 is 1.
If the MOD function returns a value greater than 0, a cfif test of its result evaluates to True. If the MOD function returns 0, the cfif check fails.
The following example uses the MOD function to alternate the background color of table rows:
<cfoutput query="tripResult">
<cfif CurrentRow Mod 2> <cfset BackColor="White"> <cfelse> <cfset BackColor="Yellow"> </cfif>
<tr bgcolor= "#BackColor#">
Notice that the MOD function uses a variable called CurrentRow. Notice also that CurrentRow is not defined anywhere in tripsearchresult.cfm. The CurrentRow variable is one of a few variables that ColdFusion automatically exposes that provide information about the query. These variables are often called query variables.
In addition to using the cfquery tag to return data from a ColdFusion data source, you also can use it to return information about a query. To do this, the cfquery tag creates a query object, providing information in query variables as described in the following table:
When a query variable is referenced within a cfoutput block, the qualifying query_name is assumed to be the query identified in the QUERY attribute of the cfoutput tag and does not need the qualifier query_name. That is why the CurrentRow variable is unqualified in the previous modulus code example.
For more information about using the modulus function or query variables in ColdFusion applications, see Developing ColdFusion MX Applications with CFML.
In this exercise, you format the currency and date fields in the Trip Search Results page and the Trip Detail page. Additionally, you modify the Trip Search Results page to alternate the background color of the result table rows.
| Existing code |
Change to |
|---|---|
| #departureDate# |
#dateformat(departureDate, "mm/dd/yyyy")# |
| #returnDate# |
#dateformat(returnDate, "mm/dd/yyyy")# |
| #price# |
#dollarformat(price)# |
| #baseCost# |
#dollarformat(baseCost)# |
departureDate, returnDate, and price as in step 1.
<tr> that immediately follows the <cfoutput query="TripResult">, and replace it with the following code:<cfif CurrentRow Mod 2> <cfset BackColor="White"> <cfelse> <cfset BackColor="Yellow"> </cfif> <TR bgcolor= #BackColor#>
Search. The Trip Search Results page appears:
The properly formatted Trip Detail page appears:
To this point in the tutorial, you created a very useful drill-down query facility. Compass Travel trip coordinators can produce lists required by management and easily locate and display information about any trip. There are several requirements that were identified in Lesson 1, however, that you have not addressed: the ability to browse through the Trips table, and the ability to add, delete, and edit trip information.
The trip coordinator must be viewing the details of a specific trip to edit trip information or delete a trip. The Trip Detail page provides this trip-specific detail information. Therefore, you will use the Trip Detail page as the main Trip Maintenance application page.
You will modify the Trip Detail page so that it can act as a main switchboard to accomplish this additional functionality. The Trip Detail page shows information about a single trip. You will convert the Trip Detail page into the main application page by adding the following functionality:
The drill-down search function developed in the last exercise is very useful when the user knows some search criteria to enter. Unfortunately, however, flipping back and forth between the results page and the detail page to navigate through a record set can be tedious. Moreover, on occasion the trip coordinator might want to browse the Trips database just to check for anomalies or to become familiar with its contents. In these cases, the user does not know the criteria to search for in advance.
For example, an anomaly may exist on any trip record, the trip coordinator would have no idea what search criteria to specify on the search form to find these problem records. To solve this problem, the browse function gives the coordinator the ability to navigate sequentially through the trips table using the single record trips display. The following figure shows the navigation buttons. The label under each button describes which row to display relative to the currently displayed row:
In this exercise, you use the HTML form and input tags to add the navigational buttons to the Trips Detail page.
</table> and</cfoutput> tags in the tripdetail.cfm file:<form action="navigationaction.cfm" method="post"> <input type="hidden" name="RecordID" value="#tripID#"> <!--- graphical navigation buttons ---> <input type="image" name="btnFirst" src="images/first.gif"> <input type="image" name="btnPrev" src="images/prev.gif"> <input type="image" name="btnNext" src="images/next.gif"> <input type="image" name="btnLast" src="images/last.gif"> </form>
Note: Notice that the current trip record ID (tripID) is hidden within the form code. This is desirable because the action page must have the current record ID in order to build the query that navigates to the appropriate record in the trips database table.
The Trip Search Results page appears:
An error occurs because the navigation action page (navigationaction.cfm) does not exist. The navigation action page processes the navigation button requests. You will build the navigation action page in the next lesson.
The following table describes the navigation code in the Trip Detail page:
The search and sequential navigation capabilities are features for locating Compass Travel trips. After the trip coordinator locates a trip, they must be able to modify or delete the trip. Additionally, when viewing the detail for a trip, they must be allowed to add a new trip or use the search facility. To enable trip coordinators to do this, you will add the following buttons to the Trip Detail page:
As described earlier, it is important to pass the current record ID (tripID) to the action page to build the proper SQL statement to process the navigation button requests. It is also important to pass the current record ID to the Maintenance Action page. Therefore, you will use an HTML input tag to hide the current recordID and post it to the maintenanceaction.cfm page.
Follow these steps to add the database maintenance buttons to the Trip Detail page.
tripdetail.cfm from my_app subdirectory.
<cfoutput query="TripQuery"> tag in the tripdetail.cfm file:<form action="maintenanceaction.cfm" method="post"> <input type="hidden" name="RecordID" value="#tripID#"> <input type="submit" name="btnAdd" value=" Add "> <input type="submit" name="btnEdit" value=" Edit "> <input type="submit" name="btnDelete" value="Delete"> <input type="submit" name="btnSearch" value="Search"> </form>
Note: The current trip record ID (tripID) is in a hidden field in the form code. This field provides the action page with current record ID that it must have in order to build the query to access the appropriate record in the Trips database table.
tripdetail.cfm page in a browser (http://localhost/CFDOCS/getting_started/my_app/tripdetail.cfm). The page appears as follows:
An error occurs because the Maintenance Action page does not exist. The Maintenance Action page is required to process the maintenance button requests. You will develop this page in the next lesson.