cfloop: looping over a query

<cfloop 
   query = "query_name"
   startRow = "row_num"
   endRow = "row_num">
</cfloop>

<cfquery name = "MessageRecords" 
   dataSource = "cfsnippets"> 
      SELECT * FROM Messages 
</cfquery>
<cfloop query = "MessageRecords"> 
   <cfoutput>#Message_ID#</cfoutput><br>
</cfloop>

<cfset Start = 10> 
<cfset End = 20> 
<cfloop query = "MyQuery" 
   startRow = "#Start#" 
   endRow = "#End#"> 
   <cfoutput>#MyQuery.MyColName#</cfoutput><br>
</cfloop>

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

Comments

wlee said on Mar 25, 2004 at 10:58 AM :

We are working to improve the examples in the ColdFusion reference pages. We propose to replace the current example on this page with the the following example. If you have any comments on this example, add them to this page.

<cfquery name = "MessageRecords"
dataSource = "cfsnippets">
SELECT * FROM Messages
</cfquery>
<cfloop query = "MessageRecords">
<cfoutput>#Message_ID#</cfoutput><br>
</cfloop>

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 5th through the 10th record returned by the MessageRecords query:

<cfset Start = 5>
<cfset End = 10>
<cfloop query = "MessageRecords"
startRow = "#Start#"
endRow = "#End#">
<cfoutput>#MessageRecords.Message_ID#</cfoutput><br>
</cfloop>

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 advantage of looping over a query is that you can use CFML tags that are not allowed in a cfoutput tag. 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 = "TemplateName">
<cfinclude template = "#TemplateName#">
</cfloop>

ECDatDOT said on Jun 22, 2004 at 8:38 AM :

The last of the new examples has a typo:
<cfquery name="GetTemplate">
...
<cfloop query="TemplateName">
...

And I've never heard of <cfinclude> not being allowed inside a <cfoutput> before.

halL said on Jul 9, 2004 at 12:13 PM :

There is no specific reason not to use a cfinclude inside a cfoutput tag.
We should remove the statement:
"The advantage of looping over a query is that you can use CFML tags that are not allowed in a cfoutput tag."

ECDatDOT said on Jul 9, 2004 at 12:28 PM :

However, that in itself is one of the advantages of looping over a query. Should there be for some reason a need to nest a query output loop, you can write something like this:

<cfoutput query="outerQuery">
<cfloop query="innerQuery">
...

Of course, that's not best practice, but then this book isn't about best practices.

Another (probably more important) advantage of cfloopping over a query is whitespace control. Even if this command is issued:
<cfsetting enableoutputonly="true">
one can still control whitespace inside a <,cfloop> unlike in a <cfoutput query="">

herakles_uk1 said on Oct 10, 2004 at 4:34 PM :

Nice.. wonder if the seemingly dodgy ripoff named "BlueDragon" can muster that...

Another advantage of course is removing the need to treat queries as arrays when done inside outer queries with overkill such as...
<CFLOOP from="1" to="#myQuery.recordCount#" index="x">
item number #x# in this subqueries name is: #item[x]#
</CFLOOP>

But really, I guess I'm just expanding on the previous point, so probably shouldn't post this, ahh well, guess for yourself whether I clicked "Submit" or "Cancel"

ultrarodMX said on Dec 8, 2004 at 4:46 PM :

It is possible to put into a cfloop with query a nested cfloop using any value of the external loop?

<cfquery name="names">
Select name, ID_Name, area, areabelong
from tnames
order by ID_name
</cfquery>

<cfquery name="myarea">
Select name, ID_Name, area, areabelong
from tnames
where area = #area#
</cfquery>

what i need is:

<cfloop query="names">
<cfoutput>
<cfset myarea = #myarea.area#>
<cfset belong = #areabelong#>
</cfoutput>
<cfloop condition = "myarea is not belong ">
<cfquery name = "notmine">
Select area
from tnames
where area = #belong#
</cfquery>
<cfoutput><cfset belong = #notmine.area#></cfoutput>
<cfif #notmine.area# is 0>
<cfbreak>
</cfif>
</cfloop>
<cfif #notmine.area# is not 0>
<cfoutput> #name#, #area#>br>
</cfif>
</cfloop>

I just want a list from all those are in my area beyond areabelong is not like myarea

No screen name said on Feb 9, 2005 at 12:27 AM :

When I nest multiple <cfloop query=""> statements I have a problem of the 2nd loop "remembering" what happened in the first itterations,
eg, say q1 returns a list of numbers from 1 through 10 and q2 returns 1 row
<cfquery name="q1" ...>
<cfloop query="q1">
inner: #q1.x#
<cfquery name="q2" ...>
<cfloop query="q2">
outter: #q1.x#
</cfloop>
</cfloop>

I will get:
inner: 1
outter: 1
..
inner: 2
outter: 1
..
inner: 3
outer: 1

why?

kuMX said on Mar 8, 2005 at 7:43 AM :

Nested <cfloop query=""> statements are indeed buggy. The workaround is to use an additional variable in the outer loop:

<cfquery name="q1" ...>
<cfloop query="q1">
inner: #q1.x#
<cfset temp = #q1.x#>
<cfquery name="q2" ...>
<cfloop query="q2">
outter: #temp#
</cfloop>
</cfloop>

GameDev said on Mar 27, 2006 at 1:17 PM :

I think to be a little more clear, yes there is a problem when nesting loops w/ queries. It seems that the inner loop takes over the scope therefore preventing your outer loop from iterating. A simple fix though would be this:

<cfloop query="outerloop">
<cfset nestedVar = outerloop.value>
<!-- any other values you may need in the loop -->
<cfloop query="innerloop">
#nestedVar#
</cfloop>
<cfset nestedVar = 0>
</cfloop>