Using a web page to list trips

To help Compass Travel agents take trip reservations by telephone and in person, the trip coordinator maintains a list of current trip offerings. Years ago, the coordinator would type the list and fax it to the various Compass Travel offices in an effort to keep everyone informed. When Compass Travel built an intranet accessible by all offices, the trip coordinator added the following HTML web page to the site:

This image shows a picture of the Trip List page.

Each time the Trip List HTML page is rendered in a browser, it displays the same web page. Since the page always shows an identical trip list, it is considered a static web page. You should only use static web pages when you are creating a page that is not likely to change often.

Converting to a dynamic web page

Using the static web page approach, the Trip Coordinator needs to modify all the web pages that reference trip lists when trips are added, deleted, or trip names are changed. This manual process of updating each web page can lead to inaccurate or untimely information. Luckily, since Compass Travel has built a database that contains a list of trips, you can build a more accurate and timely solution for the trip coordinator. To accomplish this, you must understand how to issue a SQL SELECT statement to retrieve the data from the Trips table in the Compass Travel database.

Understanding basic SQL SELECT statements

The SQL SELECT statement retrieves columns of data from a database. The tabular result is stored in a result table (called the record set).

You use the following SELECT statement to retrieve information from a table:

  SELECT column_name(s) FROM table_name

Consider a table named Clients to hold information about people with the following rows:

LastName

FirstName

Address

City

Jones

Tom

12 State St

Boston

Adams

Anita

521 Beacon St

Boston

Green

Peter

1 Broadway

New York

LastName	FirstName	Address	City
Jones	Tom	12 State St	Boston
Adams	Anita	521 Beacon St	Boston
Green	Peter	1 Broadway	New York

To select the columns named LastName and FirstName, use the following SELECT statement:

SELECT LastName, FirstName FROM Clients

The results of this SQL statement contains the following data:

LastName

FirstName

Jones

Tom

Adams

Anita

Green

Peter

LastName	FirstName
Jones	Tom
Adams	Anita
Green	Peter

Using the SQL WHERE clause to limit the rows returned

To conditionally select data from a table, you can add a WHERE clause to the SELECT statement resulting in the following syntax:

SELECT column_name FROM table_name WHERE column condition value

With the WHERE clause, you can use any of the following operators:

Operator

Description

=

Equal

<>

Not equal

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

AND

Joins one or more conditions

OR

Joins one or more conditions

LIKE

Specifies a search for a pattern in a column. You can use a "%" sign to define wildcards (missing letters in the pattern) before and after the pattern.

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
AND	Joins one or more conditions
OR	Joins one or more conditions
LIKE	Specifies a search for a pattern in a column. You can use a "%" sign to define wildcards (missing letters in the pattern) before and after the pattern.

For example, to select the columns named Last Name and First Name for Clients whose City is Boston, use the following SELECT statement:

SELECT LastName, FirstName FROM Clients Where City = 'Boston'

The results of the preceding SQL statement contains the following data:

LastName

FirstName

Jones

Tom

Adams

Anita

LastName	FirstName
Jones	Tom
Adams	Anita

You can compose a WHERE clause with one or more conditions; these are called subclauses. You join subclauses using the operators AND and OR.The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true. An example of a WHERE clause with multiple subclauses follows:

SELECT LastName FROM Clients Where City = 'Boston' AND FirstName = 'Anita'

The results of the preceding SQL statement contains the following data:

LastName

Adams

LastName
Adams

Note: The preceding SQL SELECT examples use single quotation marks around the value. SQL uses single quotation marks around text values. Most database systems will also accept double quotation marks. Do not enclose numeric values in quotation marks.

Sorting the results

You use the ORDER BY clause to sort the result rows. The following SQL statement returns an alphabetic list of people sorted by last name then first name from the Clients table:

SELECT * FROM Clients Order By LastName, FirstName

The default is to return the results in ascending order (top to bottom). If you include the DESC keyword in the ORDER BY clause, the rows are returned in descending order (bottom to top).

The following statement returns a reverse alphabetic list of the Clients table:

SELECT * FROM Clients Order By LastName, FirstName DESC

Note: The SQL SELECT statement is quite powerful. There are several other options for retrieving data from a SQL database using the SELECT statement, which are not described in this book. For more information, consult a SQL reference.

Using SQL with cfquery to dynamically retrieve information

Relational database management systems process SQL instructions sent to them from various applications. ColdFusion sends SQL statements to database managers to manipulate data. ColdFusion needs a way to know which database manager to send a specific SQL string for evaluation. In CFML, the cfquery tag serves this purpose. You will use the SQL SELECT statement and the cfquery tag to create a dynamic version of the Trip List page presented earlier in this lesson. In this example, you use cfquery to return all the trip names found in the tripName column within the Compass Travel Trips table. To use the SQL SELECT statement to dynamically retrieve this information, you must execute the SQL SELECT statement between the cfquery start and end tags as follows:

<cfquery name="TripResult" datasource= "CompassTravel">

  SELECT tripName FROM trips
</cfquery>

Displaying the query result using cfoutput

In Chapter 2, you learned that the ColdFusion cfoutput tag is an easy mechanism to display literal text and the contents of variables. Additionally, the cfoutput tag significantly simplifies displaying the results of queries. When used to display the results from a query, the cfoutput tag automatically loops through the record set for you. You simply specify the name of the query in the QUERY attribute of the cfoutput tag:

<cfoutput query="TripResult">

All the code between the cfoutput start and end tags is the output code block. The output code block executes repeatedly, once for each row in the record set. However, if the query returns no rows, ColdFusion skips the code contained in the output code block.

<cfoutput query = "xxx">

  ...output code block...
</cfoutput>

Displaying the column contents from the SQL statement

In CFML you surround variables with pound signs (#) to display their contents using the cfoutput tag. You also use this approach with column names specified in the SELECT statement of a cfquery. For instance, when you want to display the trip names from the SQL query, you would simply use #tripName# within the output code block.

<cfoutput query="TripResult">

  #tripname#

</cfoutput>

For additional information about using SQL with cfquery and cfoutput, see Developing ColdFusion MX Applications with CFML.

Creating a dynamic web page

In the following exercises you will build a dynamic Trip Listing web page that is always current. The first exercise guides you through constructing a query to retrieve information from the database. In the second exercise, you will enhance the query to sort the query results and to display other pertinent trip information.

For your convenience, the following figure shows the Compass Travel Trips table. You can refer to this table to verify the names of the columns you use in the queries in the exercises.

This image shows a picture of the Trips table.

Exercise: building a query using SQL, cfquery, and cfoutput

Follow these steps to build a query that lists the current trips from the Compass Travel database.

To build the query:

Open an editor and create a new ColdFusion page (.cfm).

At the top of the file, enter the following code to dynamically retrieve the names of the current trips listed in the Compass Travel database:

<cfquery name="TripResult" datasource="compasstravel">
    SELECT tripName FROM trips
</cfquery>
<html>
  <head>
    <title>Trip Listing</TITLE>
  </head>
  <body>
    <h1>Trip List</h1>
    <cfoutput query="TripResult">#tripName#<BR></cfoutput>
  </body>
</html>

Save the file as triplisting.cfm in the my_app directory.
View the triplisting.cfm page in a browser. The page lists all the trip names retrieved from Compass Travel database.

Reviewing the code

The following table describes the code used to build the query:

Code

Explanation

<cfquery name="TripResult" datasource="CompassTravel">

ColdFusion query named "TripResult". Submits any SQL statement between the cfquery start and end tags to the data source specified in the datasource attribute.

SELECT tripName FROM trips

SQL SELECT statement to retrieve all tripName(s) from the trips table.

<cfoutput query="TripResult"> #tripName#<BR></cfoutput>

Output code block. Displays the value of the column tripName for each row in the result set from the "TripResult" query.

Code	Explanation
<cfquery name="TripResult" datasource="CompassTravel">	ColdFusion query named "TripResult". Submits any SQL statement between the cfquery start and end tags to the data source specified in the datasource attribute.
SELECT tripName FROM trips	SQL SELECT statement to retrieve all tripName(s) from the trips table.
<cfoutput query="TripResult"> #tripName#<BR></cfoutput>	Output code block. Displays the value of the column tripName for each row in the result set from the "TripResult" query.

Exercise: enhancing the query

In this exercise you will improve the Trip List page to make it easier for the Compass Travel agents to locate trips. You must make the following improvements:

Sort the trip names in alphabetic order.
Display the departure date, return date, and price for each trip.
Develop a Budget Trip List report that identifies trips that are priced $1500 or less.

To enhance the trip listing query to meet these new requirements, you will modify the query you created in the previous exercise.

Follow these steps to enhance the query to meet the new requirements. Display the triplisting.cfm page in the browser after each step to ensure the corresponding requirement was met.

To enhance the query results:

To sort the trip names in alphabetical order in the triplisting.cfm page, modify the SQL SELECT statement within the cfquery tags as follows:
```
SELECT tripName FROM trips ORDER BY tripName
```
To display the departure, return date, and price for each trip, modify the same SQL statement.
1. Modify the SQL SELECT statement, as follows:
```
  SELECT tripName, departureDate, returnDate, price
  FROM trips 
  ORDER BY tripName
```
2. Change the output block (the code immediately preceding the </cfoutput> tag) from just #tripName# to include all three selected fields, as follows:
```
  #tripName# departs: #departureDate# returns: #returnDate# price:
  #price#<BR>
```
Create the Budget Trip List report by doing the following:
1. Modify the SQL SELECT statement, as follows:
```
  SELECT tripName, departureDate, returnDate, price
  FROM trips 
  WHERE price <= 1500
  ORDER BY tripName
```
2. Change the heading tag from <h1>Trip List</h1> to <h1>Budget Trip List</h1>.
View the triplisting.cfm in a browser and verify that all the new requirements were met. The revised TripListing.cfm page looks like this:

Note that the dates and prices in the preceding listing are not formatted. In Lesson 3 you will enhance the look of this page.

Getting Started Building ColdFusion MX Applications
Writing Your First ColdFusion Application