Outputting a ColdFusion query dynamically

This post is more than 2 years old.

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?

1) ARTID=1 ARTNAME=charles10b DESCRIPTION=2Pastels/Charcoal ISSOLD=1 PRICE=13002 2) ARTID=2 ARTNAME=Michael DESCRIPTION=Pastels/Charcoal ISSOLD=0 PRICE=13900

  1. ARTID=3 ARTNAME=Freddy DESCRIPTION=Pastels/Charcoal ISSOLD=1 PRICE=12500

  2. ARTID=4 ARTNAME=Paulo DESCRIPTION=Pastels/Charcoal ISSOLD=1 PRICE=11100

  3. ARTID=5 ARTNAME=Mary DESCRIPTION=Pastels/Charcoal ISSOLD=1 PRICE=13550

  4. ARTID=6 ARTNAME=Space DESCRIPTION=Mixed Media ISSOLD=1 PRICE=9800

  5. ARTID=7 ARTNAME=Leaning House DESCRIPTION=Mixed Media ISSOLD=1 PRICE=7800

  6. ARTID=8 ARTNAME=Dude DESCRIPTION=Mixed Media ISSOLD=1 PRICE=5600

  7. ARTID=9 ARTNAME=Hang Ten DESCRIPTION=Mixed Media ISSOLD=0 PRICE=8900

  8. ARTID=10 ARTNAME=Life is a Horse DESCRIPTION=Mixed Media ISSOLD=0 PRICE=10500

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>

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by SuperAlly posted on 3/15/2011 at 10:34 PM

Another helpful and timely post Ray. Cheers.

Comment 2 by MikeG posted on 3/15/2011 at 11:04 PM

just an FYI for us lazy typers:
#getart["artid"][5]#

can also be expressed as

#getart.artid[5]#

Comment 3 by Phillip Senn posted on 3/15/2011 at 11:12 PM

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.

Comment 4 by todd sharp posted on 3/16/2011 at 12:54 AM

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.

Comment 5 by Raymond Camden posted on 3/16/2011 at 12:58 AM

Now that's interesting Todd. I've never seen any name a column "Sold?", but that's kinda cool!

Comment 6 by Sooraj posted on 3/16/2011 at 10:52 AM

Great info Ray

Thanks a lot

Comment 7 by todd sharp posted on 3/16/2011 at 8:27 PM

@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.

Comment 8 by Raymond Camden posted on 3/16/2011 at 8:31 PM

Makes perfect sense. Again - thanks for sharing that. I never woulda thought to use a scheme like that.

Comment 9 by TM posted on 3/17/2011 at 12:44 AM

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.

Comment 10 by Marc Cerabona posted on 8/18/2011 at 6:59 PM

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.

Comment 11 by Raymond Camden posted on 8/18/2011 at 9:39 PM

Sorry - what? Not getting what you mean.

Comment 12 by Marc Cerabona posted on 8/18/2011 at 10:31 PM

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.

Comment 13 by Raymond Camden posted on 8/18/2011 at 10:34 PM

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.

Comment 14 by Marc Cerabona posted on 8/18/2011 at 10:48 PM

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?

Comment 15 by Raymond Camden posted on 8/19/2011 at 12:46 AM

Perhaps. I was testing with embedded Derby db.

Comment 16 by Jack posted on 8/6/2013 at 1:54 AM

Is col and columnlist you have used are reserved words in coldfusion?

Comment 17 by Raymond Camden posted on 8/6/2013 at 1:55 AM

columnList is a built in variable for a query. col was the variable I used when iterating over the list.

Comment 18 by Animesh Dutta posted on 12/4/2013 at 1:42 AM

I tested with MSSQL,It is showing the column label(like name) instead of column name(like artname)

Comment 19 by Animesh Dutta posted on 12/4/2013 at 2:03 AM

oops!!! tested with MySQL

Comment 20 by Raymond Camden posted on 12/4/2013 at 2:40 AM

I'm sorry, but I don't understand what you are saying.

Comment 21 by Animesh Dutta posted on 12/4/2013 at 11:06 PM

Ray-
Marc Cerabona is not getting the column label name.I hope he is not calling the proper functions.

Comment 22 by Raymond Camden posted on 12/5/2013 at 3:59 AM

I still don't understand you.

Comment 23 by Darren Cook posted on 2/18/2014 at 4:17 AM

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.

Comment 24 by Darren Cook posted on 2/18/2014 at 4:31 AM

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.

Comment 25 by Raymond Camden posted on 2/18/2014 at 7:44 AM

Darren, thanks for sharing these details!

Comment 26 by Monique Boea posted on 4/17/2014 at 8:35 PM

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?

Comment 27 by Raymond Camden posted on 4/17/2014 at 11:46 PM

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.