Programming ColdFusion MX, 2nd Edition

Looping Over a Query Result Set

As I mentioned briefly in Chapter 2, a query loop (cfloop tag with the query attribute) performs essentially the same job as using a cfoutput tag with the query attribute. A query loop iterates over each row in a query object. Optionally, a start row and end row within the query may be specified:

<cfloop query="query_name"
        startrow="row_number"
        endrow="row_number">
...
</cfloop>

The query attribute specifies the name of a valid ColdFusion query object. startrow is optional and may be used to specify the row within the query object where the loop should begin. endrow is also optional and specifies the last row within a query object that should be included within the loop.

The query loop may be used instead of the query attribute of the cfoutput tag to display the contents of a query:

<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
  SELECT Name, Title
  FROM EmployeeDirectory
</cfquery>
   
<cfloop query="GetEmployeeInfo">
  <cfoutput>#Name#, #Title#<br></cfoutput>
</cfloop>

Using a query loop allows you to work around limitations inherent in the cfoutput tag such as the inability to nest additional output queries within a cfoutput block. For example, the following code produces an error in ColdFusion because you can’t nest cfoutput tags without using the group attribute:

<cfquery name="MyQuery1" datasource="MyDSN">
  SELECT *
  FROM MyTable
  WHERE Field = Value
</cfquery>
   
<cfoutput query="MyQuery1">
  <cfquery name="MyQuery2" datasource="MyDSN">
    SELECT *
    FROM MyTable
    WHERE Field = Value
  </cfquery>
   
  <cfoutput query="MyQuery2">
    Additional processing and output code here...
  </cfoutput>
</cfoutput>

You can get around this limitation by using a query loop within the cfoutput block:

<cfquery name="MyQuery1" datasource="MyDSN">
  SELECT *
  FROM MyTable
  WHERE Field = Value
</cfquery>
   
<cfoutput query="MyQuery1">
  <cfquery name="MyQuery2" datasource="MyDSN">
    SELECT *
    FROM MyTable
    WHERE Field = Value
  </cfquery>
   
  <cfloop query="MyQuery2">
    Additional processing and output code here...
  </cfloop>
</cfoutput>

Additionally, you can use the query loop to output a section of a record set by dynamically defining the start row and end row of the query object to loop over:

<cfset TheStart = 3>
<cfset TheEnd = 5>
   
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF">
  SELECT Name, Title
  FROM EmployeeDirectory
</cfquery>
   
<cfloop query="GetEmployeeInfo" startrow="#TheStart#" endrow="#TheEnd#">
  <cfoutput>#Name#, #Title#<br></cfoutput>
</cfloop>

This technique can be used to create a next/previous record browser in which a predetermined number of rows from a query result set are displayed on the page. This allows users to browse a set number of records at a time while moving forward and backward through the record set. Next/previous browsing is discussed in detail in Chapter 11.