Ben Nadel © 2008.
All content is the property of Ben Nadel and Kinky Solutions.
A new ColdFusion programmer emailed me recently asking:
How can I convert a ColdFusion query into an array?
When it comes to this type of conversion, there are a few things that you have to take into account. For starters, is it worth it? The ColdFusion query object is a very powerful, very flexible array-like object to begin with. It can already be accessed directly without having to loop over the record set. Is this conversion something you really need to do? Second, if you do want to make the conversion, what structure do you want? You can either mimic the existing query notation by creating a "Structure of Arrays" or you can do what I feel is a more natural feeling conversion to create an "Array of Structures".
To begin with, let's cover how you can access a query object as a structure. Once you see this, you might realize that no conversion is needed at all. The ColdFusion query object can be accessed directly like a structure of arrays:
Launch code in new window » Download code as text file »
The first index is the key value of the column you want to access, for example, "id" or "name". The second index value is the row for which you want to access that column. If you want to see that in english, think of it as "I want the value in the COLUMN_NAME column of the ROW_NUMBER row. After seeing this, you might not want to go through the processing overhead of converting the query object into any other structure as it is pretty awesome to start with.
After this, if you still want to convert the query object in to another structure, I suggest going to an array of structures. This adds memory overhead (over the structure of arrays method) since each row must have a copy of the column names (as look-up keys), but I think it is a much more natural way of thinking about the query set (especially if you are a traditional programmer).
In order to convert a query into an array, you basically have to create an array, loop over every row in the query record set, create a structure of the values in that row, and then append that structure to the array:
Launch code in new window » Download code as text file »
To test, let's set up a simple query using an ID column and a NAME column:
Launch code in new window » Download code as text file »
Now, to convert that query to an array of structures, we simple call the QueryToArray() method and pass in the query as an argument.
Launch code in new window » Download code as text file »
Dumping out the array, you can clearly see that it is an array or structures. Each structure at each array index has keys correlating to the query column.
Download Code Snippet ZIP File
Comments (13) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Thank you for this.
"For starters, is it worth it?"
Yes, if you need to dynamically access the columns.
MyQuery[variables.col] unfortunately only works with Structs. :(
Posted by Student Organization Guy on Aug 4, 2006 at 8:08 PM
Keeping up with 'Converting a Query to an Array' is tough nowadays...As part of the relaunch of their site, the folks at depressedpress.com (http://www.depressedpress.com) are presenting a series of CFML challenges. The first is converting a Query to an Array using only CFML as fast as you can. Merry Christmas of Poland!
Posted by BUses on Dec 26, 2006 at 7:30 PM
With regard to the comment:
MyQuery[variables.col] unfortunately only works with Structs. :(
Yes you can, but you need to suffix it with the row that you want, so it'd be MyQuery[variables.col][row]
If you're looping through the rows then just use the loop variable, or if you're inside a cfoutput or cfloop then just use currentRow.
Posted by Phil Arnold on Jan 12, 2007 at 6:23 AM
Nice solution, thank you.
Posted by John on Mar 1, 2007 at 10:40 PM
Nice solution, thank you.
Posted by John on Mar 1, 2007 at 10:41 PM
I was pulling my hair out trying to access a query column dynamically and saw this post. Thanks much for the tip!
It's funny how many years you can program CF and still find something new.
Posted by Terry Schmitt on Oct 30, 2007 at 4:43 PM
THAT is exactly what i´m looking for! cool you are my daysaver, thank you very much for sharing its a great tutorial. merry xmas.
Posted by Jonathan on Dec 23, 2007 at 9:09 AM
Nice article. I used to count 1, 2, 3... but now I'm just counting 0, 1, 2... Thanks!
Posted by Webdesigner on May 29, 2008 at 10:40 AM
Yes, if you need to dynamically access the columns.
Couldn't one also use the evaluate() function to dynamically access the columns? E.g.,
<cfset temp = evaluate("MyQuery.#variables.col#[#index#]")>
IIRC, inside a CFLOOP or CFOUTPUT of MyQuery one would only need to do the following:
<cfset temp = evaluate("#variables.col#")>
I actually prefer building queries (using queryNew(), queryAddRow(), etc.) to using arrays of structures or structures of arrays because queries are so powerful.
Posted by dcs on Jun 20, 2008 at 8:48 AM
Nice solution, thank you.
Posted by Michael Web on Jul 27, 2008 at 9:38 AM
Nice article. I used to count 1, 2, 3... but now I'm just counting 0, 1, 2... Thanks!
Posted by Michael Web on Jul 27, 2008 at 9:48 AM
Best answer I could find on the net on this topic. Thank you.
Posted by Mitran on Aug 14, 2008 at 3:01 PM
@Mitran,
Awesome :)
Posted by Ben Nadel on Aug 14, 2008 at 3:04 PM