Programming ColdFusion

Retrieving and Displaying Data

When you query records from a database, the results (known as a record set) are returned in a special ColdFusion data type called a query object. A query object stores the records within it in rows and columns—just like a spreadsheet. Throughout this book, I’ll use the terms record and row interchangeably. Column name and field are also used interchangeably. Before we get into the specifics of querying databases and working with query objects, we need to create a database with some sample data to work with.

Creating the Example Database

The majority of examples in this chapter (and throughout the book) use a data source called ProgrammingCF that contains several database tables including one called EmployeeDirectory. The schema and sample data for this database are listed in Appendix C. For simplicity, I’ve chosen to use a Microsoft Access database for all the examples; you can download the sample Access database from O’Reilly’s catalog page for this book (http://www.oreilly.com/catalog/coldfusion/). Of course, you can use any database you choose. To get started, you need to create a new database and save it as ProgrammingCF. Next, create a new table and add the fields shown in Table 4-2.

Table 4-2. Employee Directory Table Within the ProgrammingCF Database

Field Name	Field Type	Max Length
ID (primary key)	AutoNumber	N/A
Name	Text	255
Title	Text	255
Department	Text	255
Email	Text	255
PhoneExt	Number (long int)	N/A
Salary	Number (double, two decimal places)	N/A
Picture	Memo	N/A

In this example, ID is an AutoNumber field designated as the primary key for the table. A primary key is a single field or concatenation of fields that uniquely identifies a record. In Microsoft Access, AutoNumber is a special field type that automatically assigns a sequentially incremental number when a record is inserted into the table.^[6] If you aren’t using Access as your database, and your database doesn’t have the equivalent of the AutoNumber field, consider using ColdFusion’s Create-UUID( ) function to generate a universally unique identifier (UUID) to use as the primary key value for your record. UUIDs are 35-character representations of 128-bit strings where each character is a hexadecimal value in the range 0-9 and A-F. UUIDs are guaranteed to be unique:

<!--- create a UUID and output it to the browser --->
<CFSET MyPrimaryKeyValue = CreateUUID(  )>
<CFOUTPUT>
ID: #MyPrimaryKeyValue#
</CFOUTPUT>

When you finish adding the fields, go ahead and save the table as EmployeeDirectory. Now it is time to populate the EmployeeDirectory table with data. Table 4-3 contains a short listing of records. For the complete list, see Appendix C.

Table 4-3. Employee Directory Database Table Containing Employee Contact Information

ID	Name	Title	Department	Email	Phone-Ext	Salary
1	Pere Money	President	Executive Mgmt	[e-mail]	1234	400K
2	Greg Corcoran	Director	Marketing	[e-mail]	1237	960K
3	Mark Edward	VP	Sales	[e-mail]	1208	155K

Once you finish entering all the records, save the database to a directory on your ColdFusion server. If you have downloaded the sample Access database, you need to make sure that it resides on the same machine as your ColdFusion server or on a network share available to the server. Before you can begin using the database, you need to register it as a data source with the ColdFusion Administrator. Be sure to register the data-source name as ProgrammingCF.

Retrieving Data from a Data Source

The CFQUERY tag can retrieve data from a data source by passing an SQL SELECT statement to the data source. The SELECT statement specifies what data to retrieve from the data source. For example, if you want to retrieve all records from the EmployeeDirectory table in the ProgrammingCF data source, you can use a CFQUERY tag with a SELECT statement like this:

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT *
         FROM EmployeeDirectory
</CFQUERY>

This SELECT statement uses a wildcard (*) to retrieve all records from the EmployeeDirectory table in the ProgrammingCF data source.

Alternately, if you want to retrieve data from only a few columns as opposed to all columns (don’t confuse this with all rows), you can modify the SELECT statement like this:

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name, Title
         FROM EmployeeDirectory
</CFQUERY>

This query retrieves only the Name and Title columns from each row of the database. This type of query is used when you need only a subset of the data stored in a data source. Retrieving only the data you need as opposed to the entire table contents improves the overall performance of your queries. It is much more efficient for a database to send a small subset of data back to ColdFusion as opposed to an entire table, especially when you need only a small portion of the larger data set to begin with.

In general, it is not advisable to retrieve data using SELECT *. From a database standpoint, selecting all fields using a wildcard creates a performance hit, especially on larger record sets, because the database has to do extra work to determine what fields to return. If you really do need all the fields from a particular table returned in your result set, still consider specifying them individually by name, because it saves the database from having to construct the list.

You can further refine the SELECT statement to return only a limited number of rows of data based on a condition. This is done by including the condition using the WHERE keyword. The idea at work here is to return the smallest record set you need. The more specific the data returned by a query is to your needs, the less work you need to have the ColdFusion server do to process it. Executing the following CFQUERY returns just the names and email addresses of employees in the IT department:

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name, Title
         FROM EmployeeDirectory
         WHERE Department = 'IT'
</CFQUERY>

Note the use of the single quotes around the value 'IT' in the WHERE clause. String values must always be enclosed in single quotes. Numeric, Boolean, and date values may be specified without quotes.

You can extend this one step further, providing a dynamic value for the condition in the WHERE clause:

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT Name, Title
         FROM EmployeeDirectory
         WHERE Department = '#Form.Department#'
</CFQUERY>

This example retrieves the name and title of each employee in the department specified by a form variable called Department. Using this type of technique allows you to build dynamic SQL statements that return different results depending on form or URL input. Dynamic SQL is discussed in Chapter 11.

Outputting Query Results

Once you have data stored in a query object, the next step is to display it using the CFOUTPUT tag. The CFOUTPUT tag allows you to display data contained in a query object by referencing the query name in the QUERY attribute. Example 4-1 queries the EmployeeDirectory of the ProgrammingCF data source and outputs the results to the browser using CFOUTPUT.

Example 4-1. Outputting the Results of a Query in an HTML Table

<!--- retrieve all records from the database --->
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
         SELECT ID, Name, Title, Department, Email, PhoneExt, Salary
         FROM EmployeeDirectory
</CFQUERY>

<HTML>
<HEAD>
    <TITLE>Outputting Query Results</TITLE>
</HEAD>

<BODY>

<H2>Employee Records</H2>
<!--- create an HTML table for outputting the query results.  This section 
      creates the first row of the table - used to hold the column 
      headers --->
<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
    <TH>ID</TH>
    <TH>Name</TH>
    <TH>Title</TH>
    <TH>Department</TH>
    <TH>E-mail</TH>
    <TH>Phone Extension</TH>
    <TH>Salary</TH>
</TR>

<!--- the CFOUTPUT tag is used in conjunction with the QUERY attribute to loop 
      over each row of data in the result set.  During each iteration of the 
      loop, a table row is dynamically created and populated with the query
      data from the current row. --->     
<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
    <TD>#ID#</TD>
    <TD>#Name#</TD>
    <TD>#Title#</TD>
    <TD>#Department#</TD>
    <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
    <TD>#PhoneExt#</TD>
    <TD>#Salary#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

</BODY>
</HTML>

In this example, the CFQUERY tag executes a SELECT statement against the EmployeeDirectory table of the ProgrammingCF data source. The query retrieves all the columns for all the records stored in the table. The template then creates an HTML table and generates column headings using a series of HTML <TH> tags.

Next, the CFOUTPUT tag is used in conjunction with the QUERY attribute to loop over each row of data in the result set. With each iteration of the loop, a table row is dynamically created and populated with the query data from the current row. Note the double pound signs (##) before the hex color code in the <TR> tag. Because the color code is used within a CFOUTPUT block, the pound sign that comes before the hex code must be escaped. If is isn’t escaped, ColdFusion tries to interpret the color as a variable and throws an error because no ending pound sign is found

Obtaining Additional Query Information

Whenever you perform a query using ColdFusion, four variables are automatically created that contain information about the CFQUERY operation and the result set it returned, if any. Here are the four variables:

CFQUERY.ExecutionTime

The amount of time in milliseconds it takes the query to execute

Queryname .ColumnList

Comma-delimited list of the query column names from the database

Queryname .CurrentRow

The current row of the query that is being processed by CFOUTPUT

Queryname .RecordCount

The total number of records returned by the query

These variables can be used in countless ways and are used heavily throughout this book for such things as knowing how many records were returned by a particular query and breaking the display of query result sets into manageable chunks.

---

^[6] Because portability from one database platform to another may be an issue, it’s not desirable to use an AutoNumber field as a table’s primary key value. The examples in this book use AutoNumber fields for primary key values as a matter of convenience.