This interesting question came in a few days ago:
What's the easiest way to output a query in columns rather than rows?
At first, I wasn't sure what the reader meant. Did he mean that he only wanted to display one column from a query? I don't think so. But just in case....
<cfloop query="data">
<cfoutput>#name#<br></cfoutput>
</cfloop>
Maybe he thought that you needed to output all the values from a query and didn't realize you could just work with one column? Who knows. I've made some pretty silly mistakes in reasoning myself(*).
Then I thought - maybe he wants to "twist" the traditional row by row display of a query and display one column of data as one row of a table. In other words, turn the normal table display around 90 degrees or so. This is also pretty simple. Let's look at the code and then I'll explain it.
<cfset data = queryNew("id,name,age")>
<cfloop index="x" from="1" to="10">
<cfset queryAddRow(data)>
<cfset querySetCell(data,"id",x)>
<cfset querySetCell(data,"name","User #x#")>
<cfset querySetCell(data,"age",randRange(20,90))>
</cfloop>
<table border="1">
<cfloop index="col" list="#data.columnList#">
<tr>
<cfoutput><td><b>#col#</b></td></cfoutput>
<cfloop query="data">
<cfoutput><td>#data[col][currentRow]#</td></cfoutput>
</cfloop>
</tr>
</cfloop>
</table>
So, just ignore the beginning. All I'm doing is creating a fake query. I begin my table as you would normally. Now comes the changes. Instead of looping over the query, I loop over the column list. For each column, I start a new table row. I then loop over the query and output only my current column and current row using this syntax:
data[col][currentRow]
You may wonder - why didn't I use valueList? For some reason, valueList was built to only allow for a static column. In other words, I would have to hard code data.id, or data.name, etc. You can use Evaluate of course, but that is the tool of the Devil, so I recommend against it.
That's it. If you run this code you will see a nicely transformed query display. Now, I'm not sure why you would do this, but I'm sure there is a good reason. (Of course, half the things I code are for the heck of it and serve no practical purpose!)
*So, what silly mistake did I make? When I was learning AppleSoft BASIC, I read the docs on the syntax, but not very closely. So, I'd write a line of BASIC, and then start on a new line. I'd start on new line by hitting the space bar until the cursor wrapped to a new line. Ok, you can sto laughing now, I was only 12 or so!
Archived Comments
I have to ask this...isn't placing the <cfoutput>'s within each of the loops kind of like starting and stopping your engine at every stop sign? I could be wrong, but it seems I read that somewhere...
In your example, assuming three columns and just three rows, that's (1 in outer loop, 3 in inner loop) * 3 = 4*3 = 12 start/stop's of <cfoutput>. Is this the best way to do it? (You're the Jedi, after all!)
Not really, afaik. It just tells CF where to look for dynamic variables. I don't think there is much reason to be concerned. Now, let's take the issue and make it more general. Some folks are VERY anal with their cfoutputs, and only put them around a variable, while some folks don't mind if a paragraph of static text is inside a cfoutput. I'm one of those people. CF simply ignores the static text, so it doesn't matter how "big" your cfoutput is, or how small I'd say. Or if it have a performance difference, it is too small to really measure. My 2 cents.
This is also known as "pivoting" a Table.
Shouldnt the #name# include the query name? I mean, best practices scope your variables. In a large query how do you know where #name# comes from? Should it be #data.name#? did you just leave it out for a simple example?
Heh, well, there are more than one view on that. To me, my best practice is to use the full scope for everything BUT Variables scope and CFQuery scope. So I use it for server, application, url, form, cookie, un, arguments and attributes, and I think that is it. That's my opinion. Folks my disagree.
Ray Horn - thanks. I _knew_ there was a name for it.
Evaluate = the tool of the Devil. Too funny. :)