Paging ColdFusion Results

posted January 14th 2005 at 1633 EST in All, ColdFusion

Everyone has to page through a set of query results sooner or later. This is the easiest block of code I have to do it.

Example
Example of Recordset Paging

<cfparam name="start" type="numeric" default="1">
<cfparam name="step" type="numeric" default="10">
<cfquery datasource="..." cachedwithin=".01" name="queryResults">
select * from table
</cfquery>
<cfif queryResults.recordcount gt 0>
<cfoutput>
    <p>
    <!--- if past start --->
    <cfif (start-step-step) gt 1>
        <a href="#cgi.SCRIPT_NAME#?start=1"><img src="/images/beginning_blue.png" alt="Beginning" align="absbottom"></a>
    </cfif> 
    <cfif start gt 1>
        <a href="#cgi.SCRIPT_NAME#?start=#start-step#"><img src="/images/previous_blue.png" alt="Previous" align="absbottom"></a>
    </cfif>
    <strong>#start# - #iif(start + step gt queryResults.recordcount,queryResults.recordcount,start + step-1)# of #queryResults.recordcount# records</strong>
    <!--- if still some not displayed --->
    <cfif (start + step) lte queryResults.recordcount>
        <a href="#cgi.SCRIPT_NAME#?start=#start+step#"><img src="/images/next_blue.png" alt="Next" align="absbottom"></a>
    </cfif>
    <cfif (start+step+step) lte queryResults.recordcount>
        <a href="#cgi.SCRIPT_NAME#?start=#queryResults.recordcount-step+1#"><img src="/images/end_blue.png" alt="End" align="absbottom"></a>
    </cfif> 
    </p>    
</cfoutput>
</cfif>
<cfloop query="queryResults" startrow="#start#" endrow="#start + step-1#"> 
...... output query here
</cfloop>

Yes, for those that want to know, you can grab those beautifull blue images from my site.

6 Responses

  1. #1 cf-tags.com
    4 years, 10 months ago

    The trouble is that you create query for all records. If table contains 10000 records and you output it by 10 on page – then query for 10000 will be created each time page requested.

    Here is a solution: cf_msquery

  2. #2 Neil Merton
    4 years, 4 months ago

    It does exactly what it says on the tin – I like it :)

  3. #3 Aaron James
    2 years, 10 months ago

    Hi, I just want to know if you paging as the first and last page link. I am having difficulty…I need your help,,,

  4. #4 Rodolfo Aburto
    1 year, 6 months ago

    Great code. Simple. It is working for me. Thanks

  5. #5 SDaniel
    9 months, 1 week ago

    Hello,

    I was wondering if anyone had an example on recordset paging when a stored procedure is called. I have looked all over for this and have not had any success. I am using coldfusion and sql server 08

  6. #6 scott
    8 months, 2 weeks ago

    Thanks much! Perfect.