The CFOUTPUT tag
has an attribute called GROUP that lets you to
group output from your record sets before displaying it to the
browser. There are two ways to use the GROUP
attribute of the CFOUTPUT tag. The first method
uses GROUP to remove any duplicate rows from the
query result set.[7] This is useful in
situations where the result set you return from a query contains
duplicate rows of data but you want to display only unique records.
Example 4-3 demonstrates what happens when you query
a table containing duplicate values and output the results without
using the GROUP attribute of the
CFOUTPUT tag.
Example 4-3. Failing to Use the GROUP Attribute Results in Duplicate Values in the Output
<CFQUERY NAME="GetDepartment" DATASOURCE="ProgrammingCF">
SELECT Department
FROM EmployeeDirectory
ORDER BY Department
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Using GROUP to remove duplicate records</TITLE>
</HEAD>
<BODY>
<H2>Departments:</H2>
<CFOUTPUT QUERY="GetDepartment">
#Department#<BR>
</CFOUTPUT>
</BODY>
</HTML>As you can see in Figure 4-2, executing the template results in many of the same values being output more than once.
This is easy enough to fix. To remove the duplicates from the output,
all you have to do is modify the line of code containing the
CFOUTPUT tag to read like this:
<CFOUTPUT QUERY="GetDepartment" GROUP="Department" GROUPCASESENSITIVE="No">
Adding
GROUP="Department" to the
CFOUTPUT tag tells ColdFusion to discard any
duplicate values in the result set and output only unique values. The
GROUPCASESENSITIVE attribute indicates whether
grouping should be case-insensitive or case-sensitive. This attribute
is optional and defaults to Yes. For our example,
set GROUPCASESENSITIVE to No in
case someone enters the name of a department using the wrong case.
The difference in output is shown in Figure 4-3.
It is important to note that using
GROUP to remove duplicates from the result set
does so after the result set is returned from
the database. You should consider how this might affect the
performance of your application if you want to return only a few
records from a large record set that contains numerous duplicate
values. In such a case, you should use SQL to remove the duplicates.
If you look at the code in Example 4-3, you’ll
notice that we included ORDER BY
Department in our SQL statement. It is necessary
to sort the result set by the column being grouped. To see what
happens if you don’t include the ORDER BY
clause, remove it from the query and execute the template.
As I mentioned in the beginning of this section,
GROUP can be used in two ways. The second way the
GROUP attribute can be used is to group like
records for output. This allows you to do such things as group the
output of a query by a certain field or fields such as age, gender,
department, color, etc. This is done by including (nesting) a second
set of CFOUTPUT tags without the
GROUP attribute inside the first set. Example 4-4 shows how to use the GROUP
attribute of the CFOUTPUT tag to group the results
of a query by Department.
Example 4-4. Using the GROUP Attribute of the CFOUTPUT Tag to Group Records by Department
<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
SELECT Name, Title, Department, Email, PhoneExt
FROM EmployeeDirectory
ORDER BY Department
</CFQUERY>
<TABLE CELLPADDING="3" CELLSPACING="0">
<CFOUTPUT QUERY="GetEmployeeInfo" GROUP="Department" GROUPCASESENSITIVE="No">
<TR>
<TD COLSPAN="5" HEIGHT="30" VALIGN="bottom"><FONT SIZE="+1"><B>#Department#</B>
</FONT></TD>
</TR>
<TR BGCOLOR="##888888">
<TH>Name</TH>
<TH>Title</TH>
<TH>Department</TH>
<TH>E-mail</TH>
<TH>Phone Extension</TH>
</TR>
<CFOUTPUT>
<TR BGCOLOR="##C0C0C0">
<TD>#Name#</TD>
<TD>#Title#</TD>
<TD>#Department#</TD>
<TD><A HREF="Mailto:#Email#">#Email#</A></TD>
<TD>#PhoneExt#</TD>
</TR>
</CFOUTPUT>
</CFOUTPUT>
</TABLE>The GROUP attribute of the
CFQUERY tag lets you group your query’s
result set by the specified column. In this case, we want to group
the query results by Department. It is necessary to use the
ORDER BY clause to order the result set by the
column being grouped. Failing to do so results in unwanted output.
Notice the second set of CFOUTPUT tags nested
within the pair declaring the GROUP. This creates
an outer and inner loop for looping over the result set and grouping
the output appropriately. Nested CFOUTPUT tags may
be used only when the outermost CFOUTPUT tag has a
value specified for the QUERY and
GROUP attributes. If you attempt to nest
CFOUTPUT tags without using these attributes in
the outermost tag, ColdFusion throws an error. Additionally, if you
omit the nested CFOUTPUT, the nested grouping
doesn’t occur, and you end up removing any duplicate records
from the result set (just like our previous example). Executing the
template results in the output shown in Figure 4-4.
It is entirely
possible to group data several levels deep. Doing so requires nesting
several sets of CFOUTPUT tags using the following
general syntax:
<CFOUTPUT QUERY="query_name" GROUP="column"> HTML and CFML... <CFOUTPUT GROUP="different_column"> HTML and CFML... <CFOUTPUT> HTML and CFML... </CFOUTPUT> </CFOUTPUT> </CFOUTPUT>
When ColdFusion encounters nested CFOUTPUT tags,
it executes successive levels of nested loops to group the query
result set. In general, there are a few rules you need to keep in
mind when working with nested CFOUTPUT tags:
[7] Don’t confuse the
GROUP attribute of the CFOUTPUT
tag with the SQL
GROUP
BY keyword, because they perform entirely
different functions. The SQL
GROUP
BY keyword is discussed
in Chapter 11.
Get Programming ColdFusion now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
