<!--- Kill extra output. --->
<cfsilent>
<!--- Set page settings. --->
<cfsetting showdebugoutput="false" />
<cffunction name="QueryToArray" access="public" returntype="array" output="false"
hint="This turns a query into an array of structures.">
<!--- Define arguments. --->
<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>
<!--- Set up the query for testing. --->
<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" />
<!--- Convert the query to an array. --->
<cfset arrTest = QueryToArray( qTest ) />
</cfsilent>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>QueryToArray( qRecordSet ) @ KinkySolutions.com</title>
<!-- Styles. -->
<style type="text/css">
body { background-image: url("http://www.bennadel.com/images/global/background_logo.jpg") ; background-position: top right ; background-repeat: no-repeat ; margin: 20px 20px 20px 20px ; }
body, td { font: 11px verdana ; }
h2 { color: #FA3E0A ; font: 25px verdana ; margin: 0px 90px 15px 0px ; }
h3 { color: #FA3E0A ; font: 16px verdana ; margin: 0px 0px 15px 0px ; }
p, ul, ol, table { line-height: 18px ; margin: 0px 90px 12px 0px ; }
form { margin: 0px 0px 0px 0px ; }
input, select, textarea { font: 11px verdana ; vertical-align: middle ; }
input, textarea { padding: 2px 2px 2px 2px ; }
div.code, div.codefixed { border: 1px solid #999999 ; margin-bottom: 18px ; padding: 2px 2px 2px 2px ; overflow: auto ; width: 95% ; }
div.codefixed { height: 200px ; }
div.code ul, div.codefixed ul { font-family: monospace, verdana ; font-size: 11px ; list-style-type: none ; margin: 0px 0px 0px 0px ; padding-left: 0px ; }
div.codefixed ul { width: 1200px ; }
div.code ul li, div.codefixed ul li { background-color: #F5F5F5 ; margin-bottom: 1px ; padding: 1px 3px 1px 3px ; }
div.code ul li.tab1, div.codefixed ul li.tab1 { padding-left: 30px ; }
div.code ul li.tab2, div.codefixed ul li.tab2 { padding-left: 60px ; }
div.code ul li.tab3, div.codefixed ul li.tab3 { padding-left: 90px ; }
div.code ul li.tab4, div.codefixed ul li.tab4 { padding-left: 120px ; }
div.code ul li.tab5, div.codefixed ul li.tab5 { padding-left: 150px ; }
div.code ul li.tab6, div.codefixed ul li.tab6 { padding-left: 180px ; }
div.code ul li.tab7, div.codefixed ul li.tab7 { padding-left: 210px ; }
div.code ul li.tab8, div.codefixed ul li.tab8 { padding-left: 240px ; }
div.code ul li.tab9, div.codefixed ul li.tab9 { padding-left: 270px ; }
#pagefooter { color: #666666 ; font-size: 10px ; font-style: italic ; margin: 30px 0px 0px 0px ; }
#pagefooter a { color: #666666 ; text-decoration: none ; }
#pagefooter a:hover { color: #FA3E0A ; text-decoration: underline ; }
</style>
<!-- Scripts. -->
<script type="text/javascript">
</script>
</head>
<body>
<h2>
QueryToArray( qRecordSet )
</h2>
<p>
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".
</p>
<p>
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:
</p>
<p>
<strong><cfset strValue = qRecords[ COLUMN_NAME ][ ROW_NUMBER ] /></strong>
</p>
<p>
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.
</p>
<p>
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).
</p>
<p>
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:
</p>
<div class="codefixed">
<ul>
<li><cffunction name="QueryToArray" access="public" returntype="array" output="false"</li>
<li class="tab1">hint="This turns a query into an array of structures."></li>
<li><br /></li>
<li class="tab1"><!--- Define arguments. ---></li>
<li class="tab1"><cfargument name="Data" type="query" required="yes" /></li>
<li><br /></li>
<li class="tab1"><cfscript></li>
<li><br /></li>
<li class="tab2">// Define the local scope.</li>
<li class="tab2">var LOCAL = StructNew();</li>
<li><br /></li>
<li class="tab2">// Get the column names as an array.</li>
<li class="tab2">LOCAL.Columns = ListToArray( ARGUMENTS.Data.ColumnList );</li>
<li><br /></li>
<li class="tab2">// Create an array that will hold the query equivalent.</li>
<li class="tab2">LOCAL.QueryArray = ArrayNew( 1 );</li>
<li><br /></li>
<li class="tab2">// Loop over the query.</li>
<li class="tab2">for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){</li>
<li><br /></li>
<li class="tab3">// Create a row structure.</li>
<li class="tab3">LOCAL.Row = StructNew();</li>
<li><br /></li>
<li class="tab3">// Loop over the columns in this row.</li>
<li class="tab3">for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){</li>
<li><br /></li>
<li class="tab4">// Get a reference to the query column.</li>
<li class="tab4">LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];</li>
<li><br /></li>
<li class="tab4">// Store the query cell value into the struct by key.</li>
<li class="tab4">LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ];</li>
<li><br /></li>
<li class="tab3">}</li>
<li><br /></li>
<li class="tab3">// Add the structure to the query array.</li>
<li class="tab3">ArrayAppend( LOCAL.QueryArray, LOCAL.Row );</li>
<li><br /></li>
<li class="tab2">}</li>
<li><br /></li>
<li class="tab2">// Return the array equivalent.</li>
<li class="tab2">return( LOCAL.QueryArray );</li>
<li><br /></li>
<li class="tab1"></cfscript></li>
<li></cffunction></li>
</ul>
</div>
<p>
To test, let's set up a simple query using an ID column and a NAME column:
</p>
<div class="codefixed">
<ul>
<li><!--- Set up the query for testing. ---></li>
<li><cfset qTest = QueryNew( "id, name" ) /></li>
<li><br /></li>
<li><cfset QueryAddRow( qTest ) /></li>
<li><cfset qTest[ "id" ][ qTest.RecordCount ] = "1" /></li>
<li><cfset qTest[ "name" ][ qTest.RecordCount ] = "molly" /></li>
<li><br /></li>
<li><cfset QueryAddRow( qTest ) /></li>
<li><cfset qTest[ "id" ][ qTest.RecordCount ] = "2" /></li>
<li><cfset qTest[ "name" ][ qTest.RecordCount ] = "Sophia" /></li>
<li><br /></li>
<li><cfset QueryAddRow( qTest ) /></li>
<li><cfset qTest[ "id" ][ qTest.RecordCount ] = "3" /></li>
<li><cfset qTest[ "name" ][ qTest.RecordCount ] = "Stefie" /></li>
<li><br /></li>
<li><cfset QueryAddRow( qTest ) /></li>
<li><cfset qTest[ "id" ][ qTest.RecordCount ] = "4" /></li>
<li><cfset qTest[ "name" ][ qTest.RecordCount ] = "Maud" /></li>
</ul>
</div>
<p>
Now, to convert that query to an array of structures, we simple call the QueryToArray()
method and pass it in as an argument.
</p>
<div class="code">
<ul>
<li><!--- Convert the query to an array. ---></li>
<li><cfset arrTest = QueryToArray( qTest ) /></li>
</ul>
</div>
<p>
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.
</p>
<cfdump var="#arrTest#" />
<p id="pagefooter">
Copyright Ben Nadel @ <a href="http://www.kinkysolutions.com" target="_blank">KinkySolutions.com</a><br />
Updated on July 10, 2006
</p>
</body>
</html>
