Basic CF: Nesting CFOUTPUTs and graphing results

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!

Happy experimenting!

isaac
www.triplezero.com.au