There are already a couple of tutorials on EasyCFM that show how to create simple recordset paging but most have them have a common factor in that they are quite inefficient. The technique they use involves grabbing all the records from the query and then looping through them using the maxrecords attribute for cfloop.

This works fine if your query has a couple of hundred records but what if you were dealing with a query that had hundreds of thousands of records? Things could get messy so this tutorial shows a slightly different view using the LIMIT command in SQL.

In essence, LIMIT is used to only pull the records you need within the query as opposed to all the records and then looping through them. So let's take a look at using LIMIT within our queries. The code below has been tested with ColdFusion MX7 and MySQL 5 but the LIMIT statement is a standard SQL statement and could be used with other database types

OK first up, open the file that will contains your records that need paging, I'll call our file records.cfm for this tutorial.

<!---Set the number of records per page that you want to display--->
<cfparam name="recordsperpage" default="10">
<!---This parameter simply contains a start point for the page so initially it will be zero to start from the beginning--->
<cfparam name="startrow" default="0">
<!---This parameter saves you having to repeat the url being paged and is handy if you have other variables you need to pass. The vars in this url are just examples and don't need to be there--->
<cfparam name="urlstr" default="records.cfm?var1=123&var2=abc">

<!---The query below grabs the appropriate records for the page. Note the LIMIT statement is initially starting from the first record and pulling the first set of page results--->
<cfquery datasource="#dsn#" name="getrecords">
    SELECT *
    FROM tblName
    LIMIT #startrow#, #recordsperpage#

<!---This simply gets the number of records returned by the query as it might be less than the recordsperpage value--->
<cfset resultcnt=getusers.recordcount>

<!---Finally we need to quickly grab the total number of records in the query. This is using for working out how many pages we are going to have.--->
<cfquery datasource="#dsn#" name="gettotal">
    SELECT COUNT(uid) AS totalcnt FROM tblName
<cfset totalcnt=gettotal.totalcnt>
<!---The following statement works out how many pages there are going to be required to display all the records--->
<cfset numround=round(totalcnt/recordsperpage)*recordsperpage>

<!---OK now for the actual paging code. I've included a simple form submit that will allow you to change the number of records to display.--->

    <div id="paging">
        <!---Simple form to change the number of records per page--->
        <form action="#urlstr#" method="post">
            Display <select name="recordsperpage" onchange="form.submit();">
                <option value="10" <cfif recordsperpage EQ "10">selected</cfif>>10 results per page</option>
                <option value="25" <cfif recordsperpage EQ "25">selected</cfif>>25 results per page</option>
                <option value="50" <cfif recordsperpage EQ "50">selected</cfif>>50 results per page</option>
                <option value="100" <cfif recordsperpage EQ "100">selected</cfif>>100 results per page</option>
        <!---If the startrow is currently greater than 0 then we display a << to return to the very beginning.--->
        <cfif startrow NEQ 0>
            <a href="#urlstr#&startrow=0&recordsperpage=#recordsperpage#"><<</a>
        <!---If we are on the second page (or higher) of results we display a < to step back one page--->
        <cfif resultcnt LTE recordsperpage AND startrow GT 0>
            <a href="#urlstr#&startrow=#Evaluate(startrow-recordsperpage)#&recordsperpage=#recordsperpage#"><</a>
        <!---Now we just a short statement showing which page we are currently on out of the total pages--->
        Showing Records #Evaluate(startrow+1)# to #Evaluate(startrow+resultcnt)# of #numberformat(totalcnt, ',')#
        <!---Show a next page link > if there is one--->
        <cfif resultcnt EQ recordsperpage>
            <a href="#urlstr#&startrow=#Evaluate(startrow+recordsperpage)#&recordsperpage=#recordsperpage#">></a>
        <!---Finally show a >> to signify the last page of results--->
        <cfif resultcnt EQ recordsperpage>
            <a href="#urlstr#&startrow=#numround#&recordsperpage=#recordsperpage#">>></a>

As the pages are flipped through, the query at the top of the file is starting from the record for that page and then only querying the database for the next set of records

To display the records, we just outut the result of the query:

<cfoutput query="getrecords">
    <!---Your record results displayed here--->

And that's it! We now can display records quickly and efficiently!

Author: Phil Williams

Web site: Open Mind Commerce

About This Tutorial
Author: Phil Williams
Skill Level: Beginner 
Platforms Tested: CFMX
Total Views: 70,167
Submission Date: January 08, 2009
Last Update Date: June 05, 2009
All Tutorials By This Autor: 8
Discuss This Tutorial

Sponsored By...
Healing Touch Massage - Dripping Springs, Texas - Deep Tissue Massage and Swedish Massage Services just $39 for a 50 minute massage!