• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

The Easiest Method for "Previous | Next" Recordset Navigation (aka - Recordset Paging)

The Easiest Method for "Previous | Next" Recordset Navigation (aka - Recordset Paging) 

This tutorial, like my others, is designed to provide a very easy method for accomplishing a task many people find quite difficult. This time I want to provide a simple and quick way for CF'ers to add Recordset Navigation to their website. Though the methods are easy, the end result is a robust and complete solution that will work on any size application.

First things first, Recordset Navigation, or as some call it, Recordset Paging, is the process of using Previous and Next links or buttons to allow users to navigate through a large number of returned records. In general this usually involves determining how many records per page are to be displayed, as well as providing information about how many total records were returned, and where the user is in terms of record numbers at any given time.

This is usually thought to be a very difficult process, but I'm going to attempt to demystify it now. So let's get started:

For the record, lets say that this page is named users.cfm more on that later.
Any Recordset Navigation must start with a query. For this example we'll make a query that returns a list of names and email addresses.

<!--- the query for which we want to add the recordset navigation --->
<cfquery name="getNames" datasource="#yourdatasource#">
    SELECT first_name, last_name, email
    FROM user_info
</cfquery>

Next we'll want to output the results of this query, and we'll then go back and add the navigation elements.
<!--- output the results of the query --->
<table>
    <tr>
        <td>First Name</td>
        <td>Last Name</td>
        <td>Email Address</td> 
    </tr> 
    <cfoutput query="getNames">
    <tr>
        <td>#first_name#</td>
        <td>#last_name#</td>
        <td>#email#</td> 
    </tr> 
    </cfoutput> 
</table>

That's our simple query output. However, if a query returns a bunch of records, we need to provide a useful way for our users to navigate through them all. This is where Recordset Navigation comes in. The following will describe each of the needed elements we'll have to add to make recordset navigation a reality. Then we'll put it all together at the end for a real, working example.

<!--- First lets set our current row and max rows variables --->
<!--- This sets a default value for our url.currentrow variable, which is needed for the initial page of results as a previous or next link will not have been clicked yet. --->
<CFPARAM name="URL.currentrow" default="1">
<!--- This sets the qmaxrows variable, which is the maximum number of records we want to be displayed on each page. So if the query returns 100 total records and we set qmaxrows to 10, we will then have 10 pages of 10 records each. --->
<cfset qmaxrows = 10>
<!--- Here we'll set the next and prev variables. These variables will be used in our previous and next links. --->
<CFSET next = URL.currentrow + qmaxrows>
<CFSET prev = URL.currentrow - qmaxrows>

<!--- Next lets get the total number of records returned by our query, and call that 'qtotal'. We will use this to let the user know how many total records there are to navigate through. --->
<cfset qtotal = getNames.recordcount>

<!--- Lets also create a variable for the number of the last recorded returned for each page and call it thispagemax ...huh? For our example of a query that returns 100 total results, and we're displaying 10 per page, for the first page thispagemax would be 10, for the second thispagemax would be 20, for the 6th it would be 60. You'll see how this is useful in a minute. --->
<cfset thispagemax = (qmaxrows + url.currentrow) - 1>

<!--- After we end our cfquery output, at the bottom of each page we'll want to show the users a running tab of exactly how many total records were returned, what page they are are currently on and how many total pages there will be. --->
<cfoutput>
    Showing #URL.currentrow# - 
    <cfif thispagemax GT qtotal>
        #qtotal#
    <cfelse>
        #thispagemax#
    </cfif> of #qtotal# records.
</cfoutput>

<!--- Finally, lets not forget our Previous and Next links, we might need those! --->
<a href="users.cfm?currentrow=#prev#">PREVIOUS</a> | <a href="users.cfm?currentrow=#next#">NEXT</a>

Those are all of the elements we'll need to get this bad boy running. Now lets put it all together! Here's the entire working code. See how easy that was!