Basic Cf Nesting Cfoutputs And Graphing Results
Posted on 08 Mar 2002
by Isaac (isaac)
Rated 4.06 (Ratings: 3)
- More articles in Code
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.
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:
<cfset yeartotal=0><cfoutput query=articlegraph group=theyear>
<tr><td align="right"><strong><nobr>#monthasstring(themonth)# #theyear#</nobr></strong></td>
<td><hr style="color:##f00; text-align:left; height:5px;width:#evaluate(5 * montharticles)#px; border:1px ##000 solid;"></td>
<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
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.
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!