Ask Ben: Converting A Query To A Struct : Kinky Solutions : A Student's Perspective by Ben Nadel

Ask Ben: Converting A Query To A Struct

Posted July 19, 2006 at 7:32 AM

Is there an easy way to convert a coldfusion query record to a structure?

As I covered in my blog entry about converting a ColdFusion query to an array, you might not want to even go that far. A lot of times people want to convert queries and query records to other objects because they don't realize that they can reference rows and columns of a query without having to loop over the entire query:

Launch code in new window » Download code as text file »

<cfset strCellValue = qData[ COLUMN_NAME ][ ROW_INDEX ] />

However, if you want to convert the entire query to an array or queries, take at look at my previous post. But, if you want to convert just a single query row into a structure, I will show you a modification of my QueryToArray() method. This one, QueryToStruct() can convert a query to an array OR a single record to a struct:

Launch code in new window » Download code as text file »

<cffunction name="QueryToStruct" access="public" returntype="any" output="false"
hint="Converts an entire query or the given record to a struct. This might return a structure (single record) or an array of structures.">
<cfargument name="Query" type="query" required="true" />
<cfargument name="Row" type="numeric" required="false" default="0" />
<cfscript>
// Define the local scope.
var LOCAL = StructNew();
// Determine the indexes that we will need to loop over.
// To do so, check to see if we are working with a given row,
// or the whole record set.
if (ARGUMENTS.Row){
// We are only looping over one row.
LOCAL.FromIndex = ARGUMENTS.Row;
LOCAL.ToIndex = ARGUMENTS.Row;
} else {
// We are looping over the entire query.
LOCAL.FromIndex = 1;
LOCAL.ToIndex = ARGUMENTS.Query.RecordCount;
}
// Get the list of columns as an array and the column count.
LOCAL.Columns = ListToArray( ARGUMENTS.Query.ColumnList );
LOCAL.ColumnCount = ArrayLen( LOCAL.Columns );
// Create an array to keep all the objects.
LOCAL.DataArray = ArrayNew( 1 );
// Loop over the rows to create a structure for each row.
for (LOCAL.RowIndex = LOCAL.FromIndex ; LOCAL.RowIndex LTE LOCAL.ToIndex ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){
// Create a new structure for this row.
ArrayAppend( LOCAL.DataArray, StructNew() );
// Get the index of the current data array object.
LOCAL.DataArrayIndex = ArrayLen( LOCAL.DataArray );
// Loop over the columns to set the structure values.
for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE LOCAL.ColumnCount ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){
// Get the column value.
LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];
// Set column value into the structure.
LOCAL.DataArray[ LOCAL.DataArrayIndex ][ LOCAL.ColumnName ] = ARGUMENTS.Query[ LOCAL.ColumnName ][ LOCAL.RowIndex ];
}
}
// At this point, we have an array of structure objects that
// represent the rows in the query over the indexes that we
// wanted to convert. If we did not want to convert a specific
// record, return the array. If we wanted to convert a single
// row, then return the just that STRUCTURE, not the array.
if (ARGUMENTS.Row){
// Return the first array item.
return( LOCAL.DataArray[ 1 ] );
} else {
// Return the entire array.
return( LOCAL.DataArray );
}
</cfscript>
</cffunction>

This functions takes two arguments, the ColdFusion query and the row index of the record you want to convert to a structure. If you want to convert the entire query to an array of structures, just send in the query but do not send in a row index:

Launch code in new window » Download code as text file »

<cfset arrGirls = QueryToStruct( qGirls ) />

If you want to convert just a single record to a structure, then pass in the row index as the second argument:

Launch code in new window » Download code as text file »

<cfset objGirl = QueryToStruct( qGirls, 2 ) />

Be aware that this function can return two types of data, an array (for an entire query) or a structure (for a single record). That is why the returntype attribute is set to "any". Again though, be sure you really want to add this kind of processing overhead. When possible, and when appropriate, just use the ColdFusion query object like it is an array of arrays.

Download Code Snippet ZIP File

Ask Ben: Appending Times To Date/Time Objects

Happy Freakin' Birthday Si

Reader Comments

Ben, thanks for this post. I was just looking for something exactly like this for a project I am working on at the moment. This helped me save a couple of hours easily.
Cheers,
Kai

Posted by Kai Pradel on Jul 25, 2006 at 9:50 AM

Kai, always glad to help. If you ever need any other help, please feel free to contact me directly via the Ask Ben feature and I will try to get you a solution to your problem.

Thanks,
Ben

Posted by Ben Nadel on Jul 25, 2006 at 9:55 AM

Ben, thanks I was looking for this very same thing.

Posted by miguel on Nov 13, 2007 at 1:05 PM

Ben, thanks for this... you just saved me a solid couple hours of cutting and pasting, or alternatively trying to write something like this on my own!
Thanks again,Rick

Posted by Rick Anthony on Nov 15, 2007 at 2:36 PM

What about

</cfloop>

Posted by Miles on Mar 11, 2008 at 7:33 PM

Posted by Ron Henry on Sep 10, 2008 at 2:44 AM

Post Comment | Ask Ben

Ask Ben: Converting A Query To A Struct : Kinky Solutions : A Student's Perspective by Ben Nadel