Core ColdFusion: CFOUTPUT

Dec 15, 2004

By Ryan Hartwich

They're seldom covered in articles. Most writers prefer to focus on programming techniques, strategies and methodologies. Yet knowing how to use the basics of ColdFusion, its tags and functions, is essential to all CF programmers. That's why we're starting "Core ColdFusion," a series of articles that cover essential ColdFusion concepts in depth.

This article will walk you through the major features of the CFOUTPUT tag in ColdFusion. The functionality is similar between 4, 4.5, 5, 6/6.1 and betas of Blackstone. The parameters covered, in order, are: query, group, group case sensitive, max rows, start row.

To run the code examples (which are available for download as the file CFOutput.zip), you will need ColdFusion installed with the CompanyInfo datasource setup. CompanyInfo is included in the example databases that ship with ColdFusion (4-6). In Blackstone, MM has removed the CompanyInfo database from the generic example databases and buried it inside of another database. Some minor tweaking should be expected.

CFOutput: No Parameters

The most elementary use of the CFOUTPUT tag is to output the value of a variable to the browser. One common error is the assumption that you can output the value of a variable by putting the variable name between pound signs (#).

For instance, take the following code:

<CFSET examplevariable = "won't output">
#examplevariable#

The above example will not output the phrase "won't output"?nor will it cause an error. Instead, you will see the rather bland string, "#examplevariable#". Not exactly useful!

Use the all important CFOUTPUT tag instead:

<CFSET examplevariable = "This will output, exactly as you expected!">
<CFOUTPUT>
#examplevariable#
</CFOUTPUT>

This will output, exactly as you expected!

The Query= Parameter That Returns One Record

Let's say that instead of outputting a simple variable, you want to output one specific record from a database. How would you go about that?

Let's start out with a simple query to the database. You can follow along with the test file and output included.

<CFQUERY name="Qry_Employee"
datasource="CompanyInfo">
Select * From Employee Order By Dept_ID, FirstName
</CFQUERY>
<CFDUMP var="#Qry_Employee#">

To assist you, I have included a sample of the data from the table we will be using. In no particular order, the table columns are: FirstName, LastName, Emp_ID, Dept_ID, StartDate, Salary, Contract.

Let us say you want to output the last name of an employee. You might try the following

<CFOUTPUT>
Result: #Qry_Employee.LastName#
</CFOUTPUT>

You'd get the following output:

Result: Frueh

This works great if your Select statement was very particular, say,

Select * From Employee
Where LastName = "SomethingReallyBizarre"
and StartDate = "12-18-1935"

Why does it work well? Because the variable #Qry_Employee.LastName# knows precisely what to output, the one and only record returned from the query (or nothing if zero records were returned).

However, this is problematic if there is more than one record in the record set returned from the query. For those of you who like poking fun at Americans, a large company I am working for has 900 Smith's (I wonder if they feel a little generic)... How do you really expect ColdFusion to know which Smith to output? John (27)? James (20)? Robert (14)? In the case of the simple select without a Where clause, the output was a single last name, Frueh, the first record in the returned record set.

Warning: Depending on a consistent ordering of data returned from a query that lacks an Order By clause (example: Order By LastName, FirstName, StartDate) is just asking for trouble. The order in which rows are returned from the database is random unless specified otherwise. Sure, it will appear consistent, but for how long? (Probably until your boss is performing a demonstration to someone higher up, when it will change unexpectedly!)

Be careful. Make sure that the query you wrote returns the precise records you expect it to return. As lame as this sounds, it should return a) one record or b) many records. Write your query to return a or b, your choice?

If you aren't sure how many records are being returned, try:

<CFOUTPUT>
Record Count: #Qry_Employee.RecordCount#
</CFOUTPUT>

Output:
Record Count: 19

This will tell you that there are 19 records in your table.

The Query= Parameter: Returning Multiple Rows

Why would you want to return more than one row? To display or work with more than one record. What if you want to display a list of employee first and last names? You need a simple way to return all of your employees and output them to the web page, en masse.

<CFOUTPUT query="Qry_Employee">
Department: #Qry_Employee.Dept_ID# ID:
#Qry_Employee.Emp_ID# - #FirstName# #LastName# 
</CFOUTPUT>

By adding the query="" parameter to the CFOUTPUT tag, you are telling CFOUTPUT to loop over any and all records returned from the query. For each record returned it will output the first and last name and a line break, in this case 19 lines.

Output:
Department: 1 ID: 1 - Ben Frueh
Department: 1 ID: 7 - Carolyn Lightner
Department: 1 ID: 25 - Jones Cynthia
Department: 1 ID: 17 - Robert Crooks
Department: 1 ID: 18 - Shawn Morrissey
Department: 1 ID: 8 - Sue Hove
Department: 2 ID: 19 - Donald Robinson
Department: 2 ID: 23 - Jane Smith
Department: 2 ID: 6 - Jeremy Allaire
Department: 2 ID: 20 - JJ Allaire
Department: 2 ID: 5 - Larry Concannon
Department: 2 ID: 2 - Marjorie Golden
Department: 2 ID: 24 - Stephanie Juma
Department: 3 ID: 16 - John Allen
Department: 3 ID: 3 - Sharna Fabiano
Department: 3 ID: 15 - Victoria Reiff
Department: 4 ID: 22 - Bob Jones
Department: 4 ID: 10 - John Lund
Department: 4 ID: 21 - John Doe

The Group Parameter

Next, let us add probably the most confusing and least used aspect of the CFOUTPUT tag, the group parameter.

<CFOUTPUT query="Qry_Employee" group="Dept_ID">
Department: #Qry_Employee.Dept_ID# ID: #Qry_Employee.Emp_ID# - #FirstName# #LastName# 
</CFOUTPUT>

Output:
Department: 1 ID: 1 - Ben Frueh
Department: 2 ID: 19 - Donald Robinson
Department: 3 ID: 16 - John Allen
Department: 4 ID: 22 - Bob Jones

Notice that the Group parameter is similar to the SQL's Group By clause? Now CFOUTPUT will only loop over the results 4 times, one time for each distinct grouping (department id). That is why there are only 4 records showing in the output.

But what do you do if you really want to see all of the records, but have them grouped into an easy to read format, say, something you would want in a report for your VP ...

To do this, we will nest a simple CFOUTPUT INSIDE of the other cfoutput that has a group parameter added.

<CFOUTPUT query="Qry_Employee" group="Dept_ID">
Department:
#Qry_Employee.Dept_ID# 
<CFOUTPUT>
      ID: #Qry_Employee.Emp_ID# - #FirstName# #LastName# 
</CFOUTPUT>
</CFOUTPUT>

This will produce a tiered output for each grouped Dept_ID. It will then execute the CFOUTPUT inside of it, looping over all the items in the inner grouping.

Output:
Department: 1
   ID: 1 - Ben Frueh
   ID: 7 - Carolyn Lightner
   ID: 25 - Jones Cynthia
   ID: 17 - Robert Crooks
   ID: 18 - Shawn Morrissey
   ID: 8 - Sue Hove
Department: 2
   ID: 19 - Donald Robinson
   ID: 23 - Jane Smith
   ID: 6 - Jeremy Allaire
   ID: 20 - JJ Allaire
   ID: 5 - Larry Concannon
   ID: 2 - Marjorie Golden
   ID: 24 - Stephanie Juma
Department: 3
   ID: 16 - John Allen
   ID: 3 - Sharna Fabiano
   ID: 15 - Victoria Reiff
Department: 4
   ID: 22 - Bob Jones
   ID: 10 - John Lund
   ID: 21 - John Doe

You could have produced a similar set of output by using a bunch of CFIF logic and CFSET statements. At the beginning of the output you would set a temporary variable to hold the current department. Each loop through the results you would compare the current department to the temporary value. If the values are the same, you would indent. If they were different, you would output Department: number, and reset the temporary variable. Sound confusing and a pain to code? It is, and it only gets worse! The Group parameter in CFOUTPUT is far easier (and probably faster during runtime). Below is the 'harder' code:

<CFSET TempDeptID = "It Doesn't Matter What This Is To Start">
<CFOUTPUT query="Qry_Employee">
<CFIF TempDeptID EQ Qry_Employee.Dept_ID>
      ID: #Qry_Employee.Emp_ID# - #FirstName# #LastName# 
<CFELSE>
Department:
#Qry_Employee.Dept_ID# 
<CFSET TempDeptID = Qry_Employee.Dept_ID>
</CFIF>
</CFOUTPUT>

Minor Parameters

We have covered the basics of CFOUTPUT and the GROUP parameter. There are a few minor and less frequently used parameters to cover: groupCase sensitive, maxrows, startrow.

The groupCaseSensitive parameter is pretty self explanatory. Since it is rare for ColdFusion to differentiate between upper and lower case letters, the vast majority of functions ignore case sensitivity. Occasionally there is a need to examine upper and lower cases separately. In the United States you frequently see a few last names starting with O' (O'Mally, O'Neil, O'Conner). While the vast majority have the first letter after the apostrophe capitalized, some may not. If you are printing an employee phone book, grouped by last name, you may want O'Neil and O'neil grouped separately.

The Maxrows and Startrow parameters typically go hand in hand. They are useful when creating page forward and page back functionality on long record sets. Take, for instance, a hypothetical Google search. You go to Google and search for 'ColdFusion' and are told 'Results 1 - 10 of about 5,820,000 for ColdFusion'. Obviously, you have no intention of outputting all 5 million plus records to the screen! If you were to create a phone book for your employer you may have a few or thousands of employees. If you want to display a specific part of the alphabet you would have your query return last names < 'm' and > 'r'.

If, however, you want everyone and you want the user to have the ability to page forward and back, 25 at a time, you may want

This will pull up to 25 rows of data from your query to output, starting at row 3175. By adding 'forward' and 'back' HREF links to your web page, you can use the values to page forward and back.

<a href="mypage.cfm?starthere=3150">Back</a> <a href="mypage.cfm?starthere=3200">Forward</a>

With your actual page containing:

<CFOUTPUT query="Qry_Employee" maxrows="25" startrow="<CFIF IsDefined("URL.Starthere")>#URL.starthere#<CFELSE>0</CFIF>">

I added the CFIF into the start row in order to check for the existence of the URL parameter from the page submission. If you do not do this or something similar in your page, the first time the page runs you will receive an error if the URL variable does not exist (i.e., someone forgot to type it in instead of coming from a page with the link including it).

If you accidentally use a start row number higher than the number of rows available in the query, an error will not be created. Instead, the code inside of your CFOUTPUT tag will not execute and the page will continue to process after the </CFOUTPUT> tag.

Ready to Go ...

There you go! We have covered the main functionality and 'tricks' for using the CFOUTPUT tag. Becoming familiar with the Group parameter is very helpful in constructing pretty output to please your bosses/clients. It can make your life far easier and cut down on the debugging. The maxrows and startrow parameters give your web pages paging abilities without having to repeatedly hit the database for more information (assuming you store your query results in a session variable). With these basics under your belt, you'll be able to start using the CFOUTPUT tag effectively and creatively.

To learn even more about CFOUTPUT, check out the following links (a small selection of the results of searches for "cfoutput" on House of Fusion and Google):

Livedocs: CFOUTPUT (CFMX 6.1)

Livedocs: CFOUTPUT (ColdFusion 5.0)

Group By in CFOUTPUT (CF-Talk, December 20, 2004)

<CFOUTPUT> (CFHub.com)

ColdFusion Tutorial Lesson 2: More CFOUTPUT (Webmonkey)

How to Nest CFOUTPUT Tags (Developer.BE)

Preserve Leading 0's Question in CFOUTPUT (CF-Talk, December 3, 2004)

Queries in CFCs: CFOUTPUT Has Issues (CF-Talk, November 24, 2004)

Need: .NET Equivelant for CFOUTPUT (CF-Talk, October 22, 2004)

Unnecessary CFOUTPUT Tag (CFDev.com)

Copy This Article's Url To Clipboard

If you need the current row of the query as it is running use myquery.currentrow. This is quite useful and seldom documented, even by Adobe.