Skip to page content or Skip to Accesskey List.

Work

Main Page Content

Basic Cf Nesting Cfoutputs And Graphing Results

Rated 4.06 (Ratings: 3)

Want more?

  • More articles in Code
 
Picture of isaac

Isaac

Member info

User since: 14 Dec 1998

Articles written: 67

Basics

The CFOUTPUT tag, commonly used to display the results of a database query, can be nested with use of its group attribute. This permits effective sectioning of displayed data whether by a table, list, or other means.

Application

Looping over a query

A simple application of the CFOUTPUT tag is to output data as an HTML graph. For example, we could find out how many articles have been published on the evolt.org site and graph the number per month. But we'll jump straight to the next level, and nest two CFOUTPUT tags so that we can get the publishing history of each month, and the totals of each year. First, the query (courtesy of SQL guru, Rudy Limeback):

<cfquery name="articlegraph" datasource="datasource_name">

select to_char(datemod,'yyyy') as theyear

, to_char(datemod,'mm') as themonth

, count(contentid) as montharticles

from content

where signoff = 1

group by to_char(datemod,'yyyy')

, to_char(datemod,'mm')

</cfquery>

This effectively grabs the year and month of each published article (signoff is a field denoting publication), and counts the monthly total. The query groups the results, first by year, and then month. This is crucial for the nested CFOUTPUT we'll use to give monthly and yearly totals.

Outputting As a Graph

After the query, is the output code:

<table border="0">

<cfset yeartotal=0>

<cfoutput query=articlegraph group=theyear>

<cfset yeartotal=0>

<cfoutput>

<tr>

<td align="right"><strong><nobr>#monthasstring(themonth)# #theyear#</nobr></strong></td>

<td><span>(#montharticles#)</span></td>

<td><hr style="color:##f00; text-align:left; height:5px;

width:#evaluate(5 * montharticles)#px; border:1px ##000 solid;"></td>

</tr>

<cfset yeartotal = yeartotal + montharticles>

</cfoutput>

<tr>

<td colspan="2"> </td>

<td><strong>#theyear# total: #yeartotal#</strong></td>

</tr>

</cfoutput>

</table>

You'll notice that we're first clearing a yeartotal variable that we use within the CFOUTPUT's to sum the monthly totals.

Next we commence the first CFOUTPUT of data from our "articlegraph" query, and importantly, group on theyear (as first listed in our query). Our subsequent nested CFOUTPUT automatically groups on themonth (since it's the second group by option).

Within the inner CFOUTPUT, we output in a series of table cells, the month name (using MonthAsString() to convert from the numerical form), the total number of articles for the month (montharticles), and then a styled <hr> with a height of 5px, and a width equal to 5 times the number of articles published within the month. If you wanted to account for users without JavaScript/CSS, you could easily replace this with an image.

Just prior to closing the inner CFOUTPUT, we increment the yeartotal variable. Then we output it in a row of its own in the outer loop, before clearing it (as mentioned earlier) in readiness for the looping for the following year.

Results

From the image below, you can see some example results (as displayed in IE5/Win2K). Click the image to visit a live representation of the code in action.

Our graph may not be stunning, but it will download quickly due to the absence of images, and will give you ideas to improve the graph display regardless of its current appearance!

A 50% view of the graph

Happy experimenting!

 

isaac

www.triplezero.com.au

Isaac is a designer from Adelaide, South Australia, where he has run Triplezero for almost a decade.

He was a member and administrator of evolt.org since its founding in 1998, designed the current site, and was a regular contributor on evolt.org's direction-setting discussion list, theforum.

On the side, he runs Opinion, Hoops SA, Confessions, Daily Male, and Comments, as well as maintaining a travel gallery at Bigtrip.org.

The access keys for this page are: ALT (Control on a Mac) plus:

evolt.org Evolt.org is an all-volunteer resource for web developers made up of a discussion list, a browser archive, and member-submitted articles. This article is the property of its author, please do not redistribute or use elsewhere without checking with the author.