• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Database Basics > Grouping Output

4.6. Grouping Output

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.[3] 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.

[3] 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.

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.

Figure 4-2. Duplicate records are displayed because GROUP wasn't used


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.

Figure 4-3. Using GROUP to remove duplicate records


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.

Figure 4-4. Using CFOUTPUT to group query results


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:

  • The outermost CFOUTPUT tag must have the QUERY and GROUP attributes defined.

  • The innermost CFOUTPUT tag can't have any attributes specified.

  • All other CFOUTPUT tags may have only the GROUP attribute specified.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint