Grouping Nesting and Formatting Outputs ColdFusion Help

In this chapter. you will take a deeper look at a tag you first visited in Chapter 3. “Getting Data froma Database.” and then revisitedin Chapter lll, “Csinb CFQUERY and SQL to Interact with the Database.” This tilg is the CFOUTPUT tag,

The CFOUTPUT tag is the key to producing dynamic output. Without this capability Cold Fusion would haw limited use. In this chapter take a detailed look at using CFOlJTPUT to output the results of querics including how to nest output, how to output limited parts of a result set, and how to formal output.

You will also consider the limitations of the CFOUTPUT tag. including restrictions on vv hich other Cold Fusion tags can be used between the opening and closing CFOUTPUT tags

Using CfOUTPUT

In Chapter 3, you first encountered the notion of using CFOUTPUT to 0 “Ftit the results of a database query. You saw simple examples of taking a query result, specifying its name and outputting every record in the result set in a specified way

<CFOUTPUT QUERYE’queryname”)
Code for the output of results·
</CFOUTPUT>

Here you used a single attribute of the CFOUTPUT tag: QUERY. This attribute specified the name of the query that was to be output. Specifying the query in this \\·ay caused the code for outputting the results to be executed once for each row or record. in the query result.

look at a specific example. Consider a simple database table called Employees in a database with the data source name EmployeeData and the following fields:

<CFQUERY NAME=’Employees’ DATASOURCE-‘EmployeeData’)
SELECT EmployeeID, LastName, FirstName
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<TABLE BORDER-O>
<CFOUTPUT QUERY-‘Employees’>
<TR>
<TO>#EmployeeID#</TO>
<TO>
<STRONG>#LastName#,</STRONG> #FirstName#
</TO>
</TR>
</CFOUTPUT>
</TABLE>
This produces output similar to that shown in Figure 12.1.

FIGURE 12.1

FIGURE 12.1

What happened here is that a SQL query was executed using CFQUERY and given the name Employees. CFOUTPUT was then used to output the result of the query in the order in which it was retrieved from the database. Within the CFOUTPUT tag, each column in the current record being displayed from the query result was accessed by the name inside pound signs

Displaying Partial Record Sets

Often a query result will generate many records. However, up until this point all our examples of CFOUTPUT have displayed all records in a query result set.

Using the STARTROW and MAXROWS attributes, you can limit the number of records that are displayed by CFOUTPUT. This is especially useful when displaying a query result portion by portion (for instance, 20 records at a time).

STARTROW specifies the row at which to start displaying the records. The row number indicates the row in the resulting query and is affected by the sort order of the query. This is in contrast to the order of records as they are stored in the database. MAXROWS indicates the maximum number of rows to display.

Listing 12.2 is a simple example of using these attributes to display u to the  first five records of a result set

<CFQUERY NAME=’Employees’ OATASOURCE-‘EmployeeOata’>
SELECT EmployeeIO. LastName. FirstName
FROM Employees
ORDER BY LastName. FirstName
</CFQUERY>
<TABLE BOROER=O}
<CFOUTPUT QUERY·’Employees’ STARTROW21 MAXROWS=S>
<TR>
<TO>#EmployeeIO#</TO>
<TO>
<STRONG>#LastName#,</STRONG> #FirstName#
</TO>
</TR>
</CFOUTPUT>
</TABLE>

There are two significant points to understand about this example using STARTROW and MAXROWS:

Grouping Output

At times the data in a table being queried will contain duplicates in a certain field. For instance consider the employee table in the preceding example. What you want to produce is a list of family names of employees

At first glance it seems like you could generate a list of family names with the code in Listing 12.3.

<CFQUERY NAME=’ Employees’.DATASOURCE=’EmployeeData”)
SELECT LastName
FROM Employees
ORDER BY LastName
</CFQUERY>
<CFOUTPUT QUERY=’Employees’)
#LastNa’lle#<BR>
</CFOUTPUT>

However in a large organization this code may produce numerous duplicate rows in the final output as shown in Figure 12.2

FIGURE 12.2

FIGURE 12.2

This repetition can render the list practically useless because it results in a list that is too long, making it difficult to find each distinct name.

Using the GROUPattribute of the CFOUTPUTtag, you can eliminate this problem. This attribute specifies the name of a result column under which to group output and thereby eliminates duplicates. For instance in outputting your list of employee last names you need to add GROUP=L”astName’ to the CFOIjTPUT tag to eliminate the duplicates. Adding the attribute results in the following CFOUTPUtTag: <CFOUTPUTQUERY=Employees  GROUP=II LastName II which produces output like that shown in Figure 12.

FIGURE\12.3

FIGURE12.3

The grouping is typically done in a case-sensitive manner. However with the release of Col.d Fusion 4.5 you can force ColdFusion to perform case-insensitive grouping with the GRG’UPCASESENSITIV tag. If you use GROUPCASESENSITIV=EFALSEthen all grouping perf;’)rmed by the CFOUTPUTtag will be case insensitive

Posted on November 16, 2015 in Grouping Nesting and Formatting Outputs

Share the Story

Back to Top
Share This