Programming ColdFusion

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 ...