QueryToArray( qRecordSet )

Downloadable Files

This demonstrates how to turn a ColdFusion query into a ColdFusion 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. Is this something you really need to turn into an actual array. 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".

Let's just cover how you can access a query object as a structure. The ColdFusion query object can be accessed directly like a structure of arrays:

<cfset strValue = qRecords[ COLUMN_NAME ][ ROW_NUMBER ] />

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.

If you do 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 will have to have a copy of the column names, 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:

<cffunction name="QueryToArray" access="public" returntype="array" output="false"
hint="This turns a query into an array of structures.">
<cfargument name="Data" type="query" required="yes" />
<cfscript>
// Define the local scope.
var LOCAL = StructNew();
// Get the column names as an array.
LOCAL.Columns = ListToArray( ARGUMENTS.Data.ColumnList );
// Create an array that will hold the query equivalent.
LOCAL.QueryArray = ArrayNew( 1 );
// Loop over the query.
for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){
// Create a row structure.
LOCAL.Row = StructNew();
// Loop over the columns in this row.
for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){
// Get a reference to the query column.
LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];
// Store the query cell value into the struct by key.
LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ];
}
// Add the structure to the query array.
ArrayAppend( LOCAL.QueryArray, LOCAL.Row );
}
// Return the array equivalent.
return( LOCAL.QueryArray );
</cfscript>
</cffunction>

To test, let's set up a simple query using an ID column and a NAME column:

<cfset qTest = QueryNew( "id, name" ) />
<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "1" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "molly" />
<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "2" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "Sophia" />
<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "3" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "Stefie" />
<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "4" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "Maud" />

Now, to convert that query to an array of structures, we simple call the QueryToArray() method and pass it in as an argument.

<cfset arrTest = QueryToArray( qTest ) />

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.

<cfdump var="#arrTest#" />

Added July 10, 2006 / Updated July 10, 2006