Ntesting and Grouping Output ColdFusion Help

The power of CFOUTPUTbecomes even more apparent when you start to look at nesting or embedding,’, one CFOUTPUTtag within another. This technique ties in closely with the GROUPattribute that you just looked at and shows the real power of grouping.

Although it is useful to be able to eliminate duplicatesconsider the following example. You want to output an employee list organized by gender without repeating the gender with each record. Instead you want results that look something like the following lines of code:

GENDER: M
ID LastName FirstName
ID LastName FirstName
Etc.
GENDER: F
ID LastName
ID LastName
FirstName
FirstName

The first inclination of many people will be to achieve this result by using two queries. as shown in Listing 12.4

<CFQUERY NAME-‘Female’ DATASOURCE-‘EmployeeOata’>
‘SELECT EmployeeID, LastName, FirstName, Gend~r
FROM Emp1oyees
WHERE Gender-‘F’
ORDER BY LastName, FirstName
</CFQUERY>
<H3>GENDER: F</H3>
<TABLE BORDER-O>
<CFOUTPUT QUERY-“Female’>
<TR>
<TD>#EmployeeID#</TD>
<TO>
<STRONG>#L,astName#,</STRONG> IF;rstName#
</TO>
</TR>
</CFOUTPUT>
</TABLE>
<CFQUERY NAME-‘Male’ DATASOURCE-‘EmplcyeeData’>
SELECT EmployeeID, LastName. FirstName, Gender
,FROM Employees
WHERE Gender-‘M’
ORDER BY LastName, FirstName
</CFQUERY>
<H3>GENDER: M</H3>
<TABLE BORDER-O>
<CFOUTPUT QUERY·’Male’>
<TR>
<TD>#EmployeeID#</TD>
<TO>
<STRONG>#LastName#.</STRONG> #FirstName’
</To>
</TR>
</CFOUTPUT>
</TABLE>

Using two queries works fine because the Gender field has only two possible values. But what if you were grouping by a field with an unlimited number of possible values? Then this approach breaks down.

This problem is solved using the GROUP attribute with nested CFOUTPUT tags. Let’s consider the gender example; to use GROUP you need do the following:

1. Sort your query result first by the Gender field (and then by any other fields you want).

2. Use a nested CFOUTPUT statement: The outer CFOUTPUT displays the gender and the inner .CFOUTPUT displays each record within the specified gender group.

Completing these steps results in the ColdFusion template shown in Listing 12.5.

<CFQUERY ~AME·’ Emp1oyees’ DATASOURCE-‘ Enip1oyeeData ‘>
SELECT Emp1oYeeID •.LastName. ‘FirstName. Gender
FROM Employees
ORDER BY Gender, LastName, FirstName
</CFQUERY>
<CFOUTPUT QUERY-‘Employees’ GROUP-‘Gender’>
<H3>GENDER: #Gender'</H3>
<TABLE BORDER-O>
<CFOUTPUT>
<TR>
<TD>#EmployeeID'</TD>
<TO> .
<STRONG>#LastName#.</STRONG> IFirstNamel
</TO>
</TR>
</CFOUTPUT>
</TABLE>
</CFOUTPUT>

Here, the outer CFOUTPUT tag groups the results by gender and displays the title for each gender. The inner CFOUTPUT tag then displays each record from within the gender group currently being displayed by the outer CFOUTPUT tag. The results look like those in Figure 12.4

FIGURE 12.4

FIGURE 12.4

There are a couple of important points to note here:

• You must sort by the fields that are being grouped. Otherwise problems occur. Figure 12.5 shows the results of the previous grouped and nested output in which the query result is not sorted by gender.
• You’must use the GROUP attribute to nest CFOUTPUT tags. Without the GROUP attribute in the outer tag ColdFusion will generate an error like the one in Figure 12.6 when nesting CFOUTPUT tags .

FIGURE 12.5

FIGURE 12.5

FIGURE 12.6

FIGURE 12.6

Multiple Levels of Nesting

It is possible to group and nest output more than one level deep, as you have already done. Let’s extend our gender example one level further. How would you group output first by gender and then by last name to produce output like these fllowing lins of code

GENDER: F
– Last Name: Last Name
– 10 First Name
– 10 First Name
– Etc.
– Last Name: Last Name
– 10 First Name
– 10 First Name
– Etc.
– Etc.
GENDER: M
– Last Name: Last Name
– 10 First Name
– 10 First Name
– Etc.
– L~st Name: Last Name
– 10 First Name
– 10 First Name
– Etc.
– Etc.

To achieve this, these two following things need to happen:

1. The query result needs to be sorted, in order, by gender and last name.
2. A third CFOUTPUT tag needs to be nested in the output, and the second-level CFOUTPUT tag needs to include the attribute GROUP-‘ LastName’ .

The result looks like the code in Listing 12.6.

FIGURE 12.7

FIGURE 12.7

Notice that the first (outer) CFOUTPUT tag groups by gender the second inner tag groups by last name and the third has no GROUP attribute and displays all records that match the current gender and last-name grouping

Formatting Output

In addition to being able to quickly and easily output the results of queries, you can easily format cUfferent types of data for output by using numerous tags and functions provided by ColdFusion.

For instance let’s consider dates. Cold Fusion stores dates in a special format which when displayed produces results that are less than desirable. For instance displaying a date variable produces output that looks like this

Its ‘1999-02-10 17:50:17’}

The DateFormat function however makes it possible to output the same date in numerous ways including the following

10-Feb-99 )
Wednesday, February 10; 1999
10/02/99
02-10-99
Feb. 10, 1999

Other functions exist to easily format decimal numbers currencies numbers HTML code and times

In addition to these tags for formatting data output, ColdFusion also provides the CFSETTINGtag, which enables you to gain some control over how templates are turned into output

Formatting Your Data Using ColdFusion Functions

Let’s start by considering functions that enable you to format the output of different types of data. These data types are the following

• Dates.
• Decimal numbers
• Currencies
• Numbers
• HfMLcode
• Times
• Boolean values

Formatting Dates

You have already been introduced to the purpose of the OateFormat function. This function takes dates stored in ColdFusion’s default format and displays them in a user readable format.

The DateFormat function takes two arguments: DateFonnatCOate. Mask). As would be expected, the date should be a value in Co~uSion’ date format and is necessary for the function to work The mask, on the other hand, isas\.optional argument that specifies  how the resulting output should be formatted

By default when no mask is provided DateFormat outputs dates in the form dd-nmnyy the day and year are displayed as two-digit numbers and the month is displayed as a three-letter abbreviation.

for instance, the following ColdF.usion code:
<CFOUTPUT>
tDateFormat(Now())I
</CFOUTPUT> .’
produces the following result:
lO-Feb-99

The mask is what makes the DateFormat function interesting. Using a mask it is possible to specify in great detail how the date should be displayed. A mask generally consists of spaces punctuation and special characters as listed in Table 12.1.

TAB L E 1 2 1:-

TAB L E 1 2 1:-

By way of example Table 12.2 shows how to produce different types of date formats and the masks that produce those results

TAB L E 1 2 .2:

TAB L E 1 2 .2:

Formatting Decimal Numbers

The Decimal Format function performs a simple task: It returns a decimal number formatted as a decimal with two places following the decimal point and a comma used as a thousands separator

The function tags a single argument: Deci ma1Format(Number). For instance the following Cold Fusion code

<CFOUTPUT>#DecimalFormat(123456789.0123456789)/CFOUTPUT>

produces the following result:

123,456,789.01

Posted on November 16, 2015 in Using Advance Query Techniques

Share the Story

Back to Top
Share This