Here's the problem. The table in our database looks like the diagram at the right. But our client/boss/customer wants to display the data on the web page like this:Level 1: Example A, Example B, Example C
Level 2: Example D, Example E, Example F
Level 3: Example G, Example H, Example I
So how do we get from table to page? It's honestly not difficult in hindsight, but I had to combine a few items I don't often use in my daily routine to accomplish it.
The first step is to get the data out of the table so we can manipulate it. Let's start with a simple Master Query.
<cfquery name="MasterQuery" datasource="ExampleDB">SELECT * FROM ExampleTable
</cfquery>
Now that we've created a recordset with our results, we can use the Query of Queries feature, to create smaller record subsets, based upon the value contained in Level.
<cfquery name="Level1" dbtype="query">
SELECT * FROM MasterQuery WHERE Level = '1' ORDER BY Name
</cfquery>
<cfquery name="Level2" dbtype="query">
SELECT * FROM MasterQuery WHERE Level = '2' ORDER BY Name
</cfquery>
<cfquery name="Level3" dbtype="query">
SELECT * FROM MasterQuery WHERE Level = '3' ORDER BY Name
</cfquery>
We've almost accomplished our task. We've got the results split into smaller recordsets by level. All we need is to display our results! But we can't just drop them into a typical CFOUTPUT tags with query attributes that matches our subqueries, like so . . .
<h2>Level 1:</h2>
<cfoutput query="Level1">#Name, #</cfoutput>
. . . because, as any fool can plainly see, we'll wind up having an extra comma trailing after the last item! However, we can nest a valuelist function (which turns one column of a recordset into a list, separated by the delimiter of our choice) inside a "plain" cfoutput tag:
<h2>Level 1:</h2>
<cfoutput>#valuelist(Level1.Name, ", ")#</cfoutput>
<h2>Level 2:</h2>
<cfoutput>#valuelist(Level2.Name, ", ")#</cfoutput>
<h2>Level 3:</h2>
<cfoutput>#valuelist(Level3.Name, ", ")#</cfoutput>
I'm sure plenty of ColdFusion vets would look at this and wonder why I think this is a big deal because it's the obvious solution to them. What can I say? This is the first time I've run across a legit situation requested by a client that required me to use Query of Queries, valuelist and cfoutput, so to me this is new territory.