QueryToStruct( qRecordSet, intRowIndex ) : ColdFusion Snippets : Kinky Solutions : A Student's Perspective by Ben Nadel

QueryToStruct( qRecordSet, intRowIndex )

Snippets Overview

ColdFusion

DHTML

Flash

Javascript Library

Downloadable Files

query_to_struct_function.cfm.txt ( 13,547 Bytes )

When converting a query to a structure there are two angles from which to approach. You can either convert an entire query to structures or a single record in the query to a structure. Here is a function that accomplishes both feats in one:

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

Since ColdFusion structures have no sense of records or indexes, we can't really model a query with structures alone. What we can do thought is create an array of structures where each structure represents an individual record in the query. We have a function capable of doing both. That is why the returntype of the funciton is "any": it has to be able to return either an array or a structure.

Try converting the entire query into an array of structures:

<cfset arrGirls = QueryToStruct( qGirls ) />

To convert a single record, all you have to do is pass in the row index for which you want to create a structure:

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

To make things easy, we start off the function assuming that we are going to create an array of structures. This way, we are always looping over a number of records and creating a structure for each row. Then, at the end of the function, we return the first structure for a single record, or the entire array for the entire record set.

Added July 19, 2006 / Updated July 19, 2006