Populating an array from a query

When populating an array from a query, remember the following:

  • You cannot add query data to an array all at once. A looping structure is often 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 cfloopquery= loop, you do not have to specify the query name to reference the query 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.