Grouped Output Table Data
Get the Code for this Tutorial
View a Working Example of This Code
Comment on this Tutorial
Introduction
This is very similar to the Output Table Data Horizontally tutorial, but it allows you to use the cfoutput 'group' attribute to group similar data in each cell.
Getting Started
We need to first set the variable for the number of columns we want. GroupCount should always start at one. These will be changed later as we run through our data. Groupcount will count each loop of the main cfoutput. Since we are grouping data, we can't rely on currentrow to determine when cell we are on.
<cfset cols = 4>
<cfset groupCount = 1>
And get our query ready. Your query will need data that can be grouped, naturally. I used the sample database and took the first letter of each name, and the name itself. I will group by the first letter of each name. You can group by your categories, or whatever.
<cfquery name="get_names" datasource="#dsn#">
SELECT names, LEFT(names,1) AS first_letter
FROM cfml_example_data
ORDER BY first_letter, names
</cfquery>
Start the html table and cfoutput. Notice the 'group' attribute
<table>
<cfoutput query="get_names" group="first_letter">
If groupCount divided by cols has a remainder of 1, we need to start a new row with the html 'tr' tag.
<cfif groupCount mod cols EQ 1>
<tr>
</cfif>
This is out html data cell. Output the grouped item from the query (first_letter in this case) and all of it's related items with a second set of cfoutout tags. Inside the nested cfoutput we put the 'names' field. This will output all the names that belong in the current group.
<td>
<b>#first_letter#</b><br/>
<cfoutput>
#names#<br/>
</cfoutput>
</td>
We need to know when to close the html rows. This is why we created a groupCount variable. We can't rely on the currentrow of the query, because we grouped the output, so we count our groups by incrementing groupCount.
If groupCount divided by cols has no remainder, it's time to end the row. That means that the groupCount we just looped over was evenly dividable by the number of columns we want to display.
<cfif groupCount mod cols EQ 0>
</tr>
If we don't need to start a new row yet, check to see if we are out of data, and need to fill in empty cells to preserve valid html.
We know we need to fill in empty cells when the current row equals the recordcount, AND the recordcount is greater than the number of columns we are displaying.
<cfelseif currentrow EQ recordcount AND recordcount GT cols>
<cfloop from="1" to="#cols - (groupCount mod cols)#" index="i">
<td> </td>
</cfloop>
</tr>
</cfif>
Increment the groupCount variable by one to keep a count of the main cfoutput, since current row is not accurate because we do not know how many rows are in each group.
<cfset groupCount = groupCount + 1>
Close the cfoutput and finish the html table.
</cfoutput>
</table>
____________
Tutorial Code
Download the Database<!--------------------------------------------
Kevin Sargent
October 14th 2005
http://www.lot-o-nothin.com/cfml/
Please Give Credit When Used.
--------------------------------------------->
<!--- set the number of columns we want --->
<cfset cols = 4>
<!--- groupCount always starts at 1 --->
<cfset groupCount = 1>
<!--- The query. You will probably use this script for data that is a 'category / item' type data. So each category has several items in it. In this case, the category is the first letter. --->
<cfquery name="get_names" datasource="#dsn#" result="get_data">
SELECT names, LEFT(names,1) AS first_letter
FROM cfml_example_data
ORDER BY first_letter, names
</cfquery>
<!--- the html table. --->
<table>
<!--- start the first output with the 'group' attributes --->
<cfoutput query="get_names" group="first_letter">
<!--- make a new row when needed --->
<cfif groupCount mod cols EQ 1>
<tr>
</cfif>
<!--- make the html table cell --->
<td>
<!--- output the group data --->
<b>#first_letter#</b><br/>
<!--- the nested cfoutput will loop over the grouped data --->
<cfoutput>
#names#<br/>
</cfoutput>
</td>
<!--- determine when to close the row --->
<cfif groupCount mod cols EQ 0>
</tr>
<!--- determine when to fill in the blank cells to keep html valid. --->
<cfelseif currentrow EQ recordcount AND recordcount GT cols>
<cfloop from="1" to="#cols - (groupCount mod cols)#" index="i">
<td> </td>
</cfloop>
</tr>
</cfif>
<!--- increment the groupCount variable to keep track of what cell we are on. --->
<cfset groupCount = groupCount + 1>
<!--- close main output and table --->
</cfoutput>
</table>
