Query to array

Please Register to submit score.

Average Score	0.0
Scores Submitted	0
Date Added	Apr 25, 2008
Last Updated	Apr 25, 2008
Tags	array coldfusion query querytoarray

Introduction

This demonstrates how to turn a ColdFusion query into a ColdFusion array :P. 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 :).

Btw this wasnt made completly by me..im new to this i made few changes. but 99.9% wasnt me but idk u may find it useful :)

Grab the Code

<cfsilent>
	
	
	<cfsetting showdebugoutput="false" />
	
	
	<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>

<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" />
	
	
	
	<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>
	
	
	<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>
	
	
	<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>&lt;cfset strValue = qRecords[ COLUMN_NAME ][ ROW_NUMBER ] /&gt;</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>&lt;cffunction name="QueryToArray" access="public" returntype="array" output="false"</li>
			<li class="tab1">hint="This turns a query into an array of structures."&gt;</li>
			<li><br /></li>
			<li class="tab1">&lt;!--- Define arguments. ---&gt;</li>
			<li class="tab1">&lt;cfargument name="Data" type="query" required="yes" /&gt;</li>
			<li><br /></li>
			<li class="tab1">&lt;cfscript&gt;</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">&lt;/cfscript&gt;</li>
			<li>&lt;/cffunction&gt;</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>&lt;!--- Set up the query for testing. ---&gt;</li>
			<li>&lt;cfset qTest = QueryNew( "id, name" ) /&gt;</li>
			<li><br /></li>
			<li>&lt;cfset QueryAddRow( qTest ) /&gt;</li>
			<li>&lt;cfset qTest[ "id" ][ qTest.RecordCount ] = "1" /&gt;</li>
			<li>&lt;cfset qTest[ "name" ][ qTest.RecordCount ] = "molly" /&gt;</li>
			<li><br /></li>
			<li>&lt;cfset QueryAddRow( qTest ) /&gt;</li>
			<li>&lt;cfset qTest[ "id" ][ qTest.RecordCount ] = "2" /&gt;</li>
			<li>&lt;cfset qTest[ "name" ][ qTest.RecordCount ] = "Sophia" /&gt;</li>
			<li><br /></li>
			<li>&lt;cfset QueryAddRow( qTest ) /&gt;</li>
			<li>&lt;cfset qTest[ "id" ][ qTest.RecordCount ] = "3" /&gt;</li>
			<li>&lt;cfset qTest[ "name" ][ qTest.RecordCount ] = "Stefie" /&gt;</li>
			<li><br /></li>
			<li>&lt;cfset QueryAddRow( qTest ) /&gt;</li>
			<li>&lt;cfset qTest[ "id" ][ qTest.RecordCount ] = "4" /&gt;</li>
			<li>&lt;cfset qTest[ "name" ][ qTest.RecordCount ] = "Maud" /&gt;</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>&lt;!--- Convert the query to an array. ---&gt;</li>
			<li>&lt;cfset arrTest = QueryToArray( qTest ) /&gt;</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>