Populating an array from a query

When populating an array from a query, keep the following things in mind:

• You cannot add query data to an array all at once. A looping structure is generally required to populate an array from a query.
• You can reference query column data using array-like syntax. For example, myquery.col_name[1] references data in the first row in the col_name column of the myquery query.
• Inside a cfloop query= loop, you do not have to specify the query name to reference the query's variables.

You can use a cfset tag with the following syntax to define values for array indexes:

<cfset arrayName[index]=queryColumn[row]>

In the following example, a cfloop tag places four columns of data from a sample data source into an array, myarray.

<!--- Do the query --->
<cfquery name="test" datasource="cfdocexamples">
   SELECT Emp_ID, LastName, FirstName, Email
   FROM Employees
</cfquery>

<!--- Declare the array --->
<cfset myarray=arraynew(2)>

<!--- Populate the array row by row --->
<cfloop query="test">
   <cfset myarray[CurrentRow][1]=Emp_ID>
   <cfset myarray[CurrentRow][2]=LastName>
   <cfset myarray[CurrentRow][3]=FirstName>
   <cfset myarray[CurrentRow][4]=Email>
</cfloop>

<!--- Now, create a loop to output the array contents --->
<cfset total_records=test.recordcount>
<cfloop index="Counter" from=1 to="#Total_Records#">
   <cfoutput>
      ID: #MyArray[Counter][1]#,
      LASTNAME: #MyArray[Counter][2]#,
      FIRSTNAME: #MyArray[Counter][3]#,
      EMAIL: #MyArray[Counter][4]# <br>
   </cfoutput>
</cfloop>

This example uses the query object built-in variable CurrentRow to index the first dimension of the array.

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | KnowledgeBase | Bug Reporting

Version 7

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00000957.htm

Add Comment