I've covered this topic a few times before, in various ways, but a reader wrote in today with a related question so I thought I'd write a quick "guide" to this topic. Basically - given any ColdFusion query - how would you loop over the data dynamically? Here is how I do it.
First, let's start with a basic query:
<cfquery name="getart" datasource="cfartgallery" maxrows="10">
select artname, description, price, issold, artid
from art
</cfquery>
To begin, I want to loop over every row. I can do that either with cfloop or cfoutput. cfoutput is the easiest way to do it as it automatically handles going from one to the total number of rows.
<cfoutput query="getart">
stuff
</cfoutput>
That gives us one iteration per query. So how do I get the data? Remember that queries can be accessed using struct notation. That syntax is:
queryname[columnname][rownumber]
So given a query called getart, a column called artid, and row 5, I can output it like so:
#getart["artid"][5]#
That works if you know the columns, but in this case, we are doing it dynamically. ColdFusion gives us easy access to the columns though. Every query contains a value called "columnlist". As you can imagine, it is a list of columns. We can loop over that list like so:
<cfoutput query="getart">
#currentrow#)
<cfloop index="col" list="#columnlist#">
#col#=#getart[col][currentRow]#
</cfloop>
<p/>
</cfoutput>
Where did currentRow come from? It's another built in variable. So what happens when we run this?
ARTID=3 ARTNAME=Freddy DESCRIPTION=Pastels/Charcoal ISSOLD=1 PRICE=12500 ARTID=4 ARTNAME=Paulo DESCRIPTION=Pastels/Charcoal ISSOLD=1 PRICE=11100 ARTID=5 ARTNAME=Mary DESCRIPTION=Pastels/Charcoal ISSOLD=1 PRICE=13550 ARTID=6 ARTNAME=Space DESCRIPTION=Mixed Media ISSOLD=1 PRICE=9800 ARTID=7 ARTNAME=Leaning House DESCRIPTION=Mixed Media ISSOLD=1 PRICE=7800 ARTID=8 ARTNAME=Dude DESCRIPTION=Mixed Media ISSOLD=1 PRICE=5600 ARTID=9 ARTNAME=Hang Ten DESCRIPTION=Mixed Media ISSOLD=0 PRICE=8900 ARTID=10 ARTNAME=Life is a Horse DESCRIPTION=Mixed Media ISSOLD=0 PRICE=10500
1) ARTID=1 ARTNAME=charles10b DESCRIPTION=2Pastels/Charcoal ISSOLD=1 PRICE=13002
2) ARTID=2 ARTNAME=Michael DESCRIPTION=Pastels/Charcoal ISSOLD=0 PRICE=13900
Notice anything? The columns are not in the same order as the SQL. Now typically the order of columns in your SQL query shouldn't matter, but if you did want to use the same order you have yet another option: getMetaData. When called on the query like so:
<cfset cols = getMetadata(getart)>
You get an array of structs containing data about the columns:
I can use this array to make a new list that respects the order from my query:
<cfset colList = "">
<cfloop from="1" to="#arrayLen(cols)#" index="x">
<cfset colList = listAppend(colList, cols[x].name)>
</cfloop>
Once I have that, I simply modify the code I used before to use colList instead of columnlist:
<cfoutput query="getart">
#currentrow#)
<cfloop index="col" list="#collist#">
#col#=#getart[col][currentRow]#
</cfloop>
<p/>
</cfoutput>
And that's it. I've pasted the entire test below if you want to play around with it.
<cfquery name="getart" datasource="cfartgallery" maxrows="10">
select artname, description, price, issold, artid
from art
</cfquery>
<cfoutput query="getart">
#currentrow#)
<cfloop index="col" list="#columnlist#">
#col#=#getart[col][currentRow]#
</cfloop>
<p/>
</cfoutput>
<cfset cols = getMetadata(getart)>
<cfset colList = "">
<cfloop from="1" to="#arrayLen(cols)#" index="x">
<cfset colList = listAppend(colList, cols[x].name)>
</cfloop>
<cfoutput query="getart">
#currentrow#)
<cfloop index="col" list="#collist#">
#col#=#getart[col][currentRow]#
</cfloop>
<p/>
</cfoutput>
Archived Comments
Another helpful and timely post Ray. Cheers.
just an FYI for us lazy typers:
#getart["artid"][5]#
can also be expressed as
#getart.artid[5]#
Thank you Raymond! I learned a lot from this post and used it to write http://www.cfmzengarden.com.... Working with this metadata also reminded me of the two functions ValueList and QuotedValueList.
Typically the same way I approach it too.
The other nice thing about using bracket notation is that you can get even lazier and alias your query columns with descriptive names like so:
<cfquery name="getart" datasource="cfartgallery" maxrows="10">
select artname as "Art Name", description as "Description", price as "Sale Price", issold as "Sold?", artid as "ID"
from art
</cfquery>
And not have to worry about invalid variable naming errors. Makes quick and dirty dynamic report generation a snap.
Now that's interesting Todd. I've never seen any name a column "Sold?", but that's kinda cool!
Great info Ray
Thanks a lot
@Ray:
I certainly wouldn't use a column name like 'Sold?' in an everyday query, but I've used this approach quite often in the past for pages/queries that _just_ do reports. I think of it more as a column header then a column name in those cases.
Makes perfect sense. Again - thanks for sharing that. I never woulda thought to use a scheme like that.
Another method you might try using to get your column list is the method listed here: http://existdissolve.com/20...
The code look like this:
columns = myquery.getMeta().getcolumnlabels().
This not only give you the correct order, but is case-sensitive so they;re not all upper-case.
One of the many things I wish I would have found years ago.
This is great. Just what I needed. One problem I am having is, what if I want to output the column label instead of the name. So Select somecolumn as name
I want to output the name instead of the some column. is this possible.
Sorry - what? Not getting what you mean.
Ray,
thanks for looking at this.
What I am trying to do is output a query dynamically as you did, but instead of using the actual name i want to use the label.
So I have a query.
Select action1 as babysitting, action2 as dogsitting
from tblwhatever
i want to output the query using babysitting and dogsitting, not action1 and action2.
i have tried using the getmetadata().()columnname and columnlabel, but they both say they are unavailable.
any help would be greatly appreciated.
Using the code above, I changed the SQL to
select artname as name, description, price, issold, artid
from art
and it worked fine. I saw NAME.
hmm, interesting. Because I get artname instead of name would this be affected by the type of database you are using? ie, would the metadata change form mysql to mssql?
Perhaps. I was testing with embedded Derby db.
Is col and columnlist you have used are reserved words in coldfusion?
columnList is a built in variable for a query. col was the variable I used when iterating over the list.
I tested with MSSQL,It is showing the column label(like name) instead of column name(like artname)
oops!!! tested with MySQL
I'm sorry, but I don't understand what you are saying.
Ray-
Marc Cerabona is not getting the column label name.I hope he is not calling the proper functions.
I still don't understand you.
Interesting comment thread! Maybe I can shed some light:
Ever wondered about the difference between column names and labels? Some JDBC drivers (the two I know about are Oracle and Teradata) for each column return two properties: the original column as the "name" and the alias as the "label".
select colA as "bee";
#writedump( query.getColumnNames() )# outputs COLA
#writedump( query.getMetadata().getColumnLabels() )# outputs "bee"
With other databases that don't support this, it sounds like CF sets column names to the UCASE() version and column labels to be the original queried case (I haven't tested this).
With CF, you can query.setColumnNames() and query.getMetadata().SetColumnLabel() with different values, and the matching get() functions return the values independently as you would expect. However when you want to reference the query data itself as a variable, you can only do it by the name, not the label. So the label is truly just that: only information about the pretty column name, and not useable as a variable name. (I was kind of hoping that query["bee"] would return something, but in CF9 at least, it does not.)
The best you might do dynamically is loop over the column name/label arrays and create a struct to cross-reference the name to the label so that e.g. label["COLA"] = "bee";
thus with any given any column name you could look up the label for it as needed.
Correction: I only *think* Oracle and Teradata return both names and labels. Once I worked with folks from CF and Teradata to create our own version of the Teradata driver to properly map these, as they were mismatching due to an upgrade...I never found out if that fix was permanently embedded in the next version of the Teradata driver, but that's how I learned about the difference. It sounds like from comments above that MySQL may return name and label separately as well.
The fact that getColumnLabels() *could* return an Alias which does not match getColumnNames() (depending on the database/driver), and the fact that column names (not labels) are the variable names by which the data is accessed, suggests that one should always use getColumnNames() when you want to dynamically introspect a query and loop over it's columns.
Darren, thanks for sharing these details!
Hi I have a related issue that I am hoping you can help me with.
I have a loop, in which I need to call a coldfusion query that is dynamic.
I then need to call another query, based on the first query and output the results dynamically. I am stuck on this.
Here is my loop and queries:
<cfloop index="i" from="1" to="#courseCount.recordCount#">
<cfstoredproc procedure="XXXXXX" datasource="#request.dsn#" returncode="yes">
<cfprocparam
cfsqltype="CF_SQL_INTEGER"
variable="userID"
value="#session.userID#">
<cfprocparam
cfsqltype="CF_SQL_INTEGER"
variable="course"
value="#i#">
<cfprocresult name="course#i#">
</cfstoredproc>
<cfstoredproc procedure="XXXXXX" datasource="#request.dsn#" returncode="yes">
<cfprocparam
cfsqltype="CF_SQL_INTEGER"
variable="userID"
value="#session.userID#">
<cfprocparam
cfsqltype="CF_SQL_INTEGER"
variable="courseID"
value="#i#">
<cfprocparam type="In"
cfsqltype="CF_SQL_DATE"
variable="fromDate"
value="#getCourse#i#.CREDITDESIGNATIONFROM#">
<cfprocparam type="In"
cfsqltype="CF_SQL_DATE"
variable="toDate"
value="#getCourse#i#.CREDITDESIGNATIONTO#">
<cfprocresult name="course#i#Apply#ceuPref.strCme#">
</cfstoredproc>
</cfloop>
My issue is adding the i variable in the 2nd query dynamically.
How do I so this?
If your intent is to pass the value of the creditdesignationfrom column,row 1, then you could do this a few ways.
Simplest may be to make a temporary variable:
<cfset theValue = variables["getCourse#i#"].creditdiesginationfrom>
and pass theValue. You could also put that expression in the call itself, but I like to break things out sometimes to make them more readable.