Adobe ColdFusion 8

Displaying record set data incrementally

If your database is large, you can limit the number of rows displayed at one time. The following example shows how to use the currentRow query variable of a Query of Queries to do this. For more information on query variables, see Getting information about query results.

  1. Create a ColdFusion page with the following content:
    <html>
<head>
<title>QoQ with incremental row return</title>
</head>

<body>
<h3>QoQ with incremental row return</h3>
<!--- define startrow and maxrows to facilitate 'next N' style browsing --->
<cfparam name = "MaxRows" default = "5">
<cfparam name = "StartRow" default = "1">

<!--- master query: retrieve all info from Employee table --->
<cfquery name = "GetSals" datasource = "cfdocexamples">
    SELECT * FROM Employee
    ORDER BY LastName
</cfquery>

<!--- detail query: select 3 fields from the master query --->
<cfquery name = "GetSals2" dbtype = "query">
    SELECT FirstName, LastName, Salary
    FROM GetSals
    ORDER BY LastName
</cfquery>

<!--- build table to display output --->
<table cellpadding = 1 cellspacing = 1>
    <tr>
    <td bgcolor = f0f0f0>
    <b><i>&nbsp;</i></b>
    </td>

    <td bgcolor = f0f0f0>
    <b><i>FirstName</i></b>
    </td>

    <td bgcolor = f0f0f0>
    <b><i>LastName</i></b>
    </td>

    <td bgcolor = f0f0f0>
    <b><i>Salary</i></b>
    </td>
    </tr>
    
<!--- Output the query and define the startrow and maxrows
     parameters. Use the query variable currentRow to
     keep track of the row you are displaying. --->
    <cfoutput query = "GetSals2" startrow = "#StartRow#" maxrows = "#MaxRows#">
    <tr>
    <td valign = top bgcolor = ffffed>
        <b>#GetSals2.currentRow#</b>
    </td>

    <td valign = top>
        <font size = "-1">#FirstName#</font>
    </td>

    <td valign = top>
        <font size = "-1">#LastName#</font>
    </td>

    <td valign = top>
        <font size = "-1">#LSCurrencyFormat(Salary)#</font>
    </td>
    </tr>
    </cfoutput>
<!--- If the total number of records is less than or equal to 
    the total number of rows, provide a link to the same page, with the 
    StartRow value incremented by MaxRows (5, in this example) --->
    <tr>
    <td colspan = 4>
    <cfif (startrow + maxrows) lte getsals2.recordcount>
    <a href="qoq_next_row.cfm?startrow=<cfoutput>#Evaluate(StartRow +
        MaxRows)#</cfoutput>">See next <cfoutput>#MaxRows#</cfoutput> 
        rows</a>
    </cfif>    
    </td>
    </tr>
</table>
</body>
</html>

  2. Save the page as qoq_next_row.cfm in the myapps directory under the web_root.
  3. Display qoq_next_row.cfm in your browser