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
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>
<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
<!--- 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
<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
Recevez de l’aide plus rapidement et plus facilement
Nouvel utilisateur ?