cfloop: looping over a query

Use the cfloop tag to loop over a query object, group a set of records, and modify the query object as you loop over the object.

Description

A loop over a query executes for each record in a query record set . The results are similar to those of the  cfoutput  tag. During each iteration, the columns of the current row are available for output. The  cfloop  tag loops over tags that cannot be used within a  cfoutput  tag.

Syntax

<cfloop 
    query = "query name" 
    startRow = "row number" 
    endRow = "row number" 
    group = "Query column"> 
</cfloop>

History

ColdFusion (2018 update) Update 2: Added support for script variant of cfloop.

See also

cfabortcfbreakcfcontinuecfexecutecfexitcfifcflocationcfoutputcfswitchcfthrowcftry

For more information, see cfloop and cfbreak in Developing ColdFusion Applications.

Attributes

Attribute

Req/Opt

Default

Description

query

Required

 

Query that controls the loop. When using query attribute, you can now use dynamic references in addition to string, as shown in the following code:

<cfloop query="#getEmployees()#">

startRow

Optional

 

First row of query that is included in the loop.

endRow

Optional

 

Last row of query that is included in the loop.

group

Optional

 

Query column to use to group sets of records. Eliminates adjacent duplicate rows when data is sorted. Use if you retrieved a recordset ordered on one or more query columns. For example, if a recordset is ordered on "Customer_ID", you can group the output on "Customer_ID.".

Example

 

<cfscript>
    myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", 
                [ 
                        {id=1,name="One",amount=15}, 
                        {id=2,name="Two",amount=18}, 
                        {id=3,name="Three",amount=32} 
                ]); 
</cfscript>
<cfloop query = "myQuery"> 
    <cfoutput>#id#.#name#</cfoutput> <br/>
</cfloop>

Output

1.One 
2.Two 
3.Three 

The cfloop tag also iterates over a record set with dynamic start and stop points. This gets the next n sets of records from a query. This example loops from the fifth through the tenth record returned by the MessageRecords query:

 

<cfscript>
    myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", 
                [ 
                        {id=1,name="One",amount=15}, 
                        {id=2,name="Two",amount=18}, 
                        {id=3,name="Three",amount=32},
                        {id=4,name="Four",amount=37}, 
                        {id=5,name="Five",amount=79}, 
                        {id=6,name="Six",amount=26}
                ]); 
</cfscript>
<cfset Start = 3> 
<cfset End = 6> 
<cfloop query = "myQuery"
        startRow = "#Start#"
        endRow = "#End#"> 
    <cfoutput>#name# #amount#</cfoutput>
</cfloop>

Output

Three 32 Four 37 Five 79 Six 26

The loop stops when there are no more records, or when the current record index is greater than the value of the endRow attribute. The following example combines the pages that are returned by a query of a list of page names into one document, using the cfinclude tag:

<cfquery name = "GetTemplate" dataSource = "Library" maxRows = "5"> 
    SELECT TemplateName 
    FROM Templates 
</cfquery> 
<cfloop query = "GetTemplate"> 
    <cfinclude template = "#TemplateName#"> 
</cfloop>

Using groups in cfloop

You can use the group attribute in the following ways:

cfloop

<cfquery name = "result" datasource="cfcodeexplorer"> 
 SELECT ORDERID, CUSTOMERFIRSTNAME,STATE,CITY
 FROM ORDERS
 ORDER BY CITY
</cfquery> 

<cfloop query="result" group="CITY">
 <cfoutput>
  #result.CITY# <br/>
 </cfoutput>
</cfloop>

cfoutput

<cfquery name = "result" datasource="cfcodeexplorer"> 
 SELECT ORDERID, CUSTOMERFIRSTNAME,STATE,CITY
 FROM ORDERS
 ORDER BY CITY
</cfquery>

<cfoutput query="result" group="CITY">
 #result.CITY# <br/>
</cfoutput>

Output

Anytown 
Boston 
Cheyenne 
Colorado Springs 
Dallas 
Deadwood 
Denver 
Greeley 
Houston 
Kansas City 
Las Vegas 
Los Angeles 
New York 
Oakland 
Ogden 
Phoenix 
Santa Fe 
Scottsdale 
Seattle

You can also modify the query object as you loop over the object. For examples,

 

<!--- Create the query object --->
<cfset names = queryNew("")/>

<!--- Add the name column --->
<cfset queryAddColumn(
    names,
    "Name",
    "cf_sql_varchar",
    listToArray("John,James,Jason,Jared")
)/>

<!--- Add another column  --->

<cfset queryAddColumn(
    names,
    "Salary",
    "cf_sql_integer",
    arrayNew(1)
)/>

<!--- Populate the Salary column with random values --->

<cfloop query="names">
    <cfset names["Salary"][names.currentRow]=randRange(50000,90000)/>
</cfloop>

<!--- Dump the array result --->

<cfdump var="#names#"/>

Output

Using the attribute groups, you can also modify a query object, as shown below:

 

<cfset players = queryNew(
    "id, name, gender",
    "cf_sql_integer, cf_sql_varchar, cf_sql_varchar",
    [
        [ 1, "Ronaldo", "Male" ],
        [ 2, "Messi", "Male" ],
        [ 3, "Sharapova", "Female" ],
        [ 4, "Serena W", "Female" ],
        [ 5, "Hamilton", "Male" ]
    ]
) />

<cfoutput>
    <cfloop query="players" group="gender">
        <b>#players.gender#:</b>
        <cfloop>
            #players.name# <br/>
        </cfloop>
    </cfloop>
</cfoutput>

Output

Male: Ronaldo 
Messi 
Female: Sharapova 
Serena W 
Male: Hamilton 

cfloop as script

 

<cfscript>
    myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", [ {id=1,name="One",amount=15}, {id=2,name="Two",amount=18}, {id=3,name="Three",amount=32}, {id=4,name="Four",amount=37}, {id=5,name="Five",amount=79}, {id=6,name="Six",amount=26} ]);
    Start = 3;
    End = 6;
    cfloop(query = myQuery, startRow = "#Start#", endRow = "#End#") {
        writeOutput("#name# #amount#" & "<br/>");
    }
</cfscript>

Output

Three 32
Four 37
Five 79
Six 26

 Adobe

Schneller und einfacher Hilfe erhalten

Neuer Benutzer?