Thursday, September 2, 2010 2:00 PM

Introduction
This Tutorial will show you an example of a more efficient pagination method than those commonly used on small web sites. Although the query does run on every new page view, it ONLY returns the rows needed for the current page. Another great thing about this method is that the url variable to go to any page is simply the page number that you want to go to.

Download the CFML Code Here
Download the SQL for the Sample Database Here
A Working Example


Requirements
Cold Fusion 5, 6, or 7 (tested on 6 and 7)
MySQL (tested with version 4.0x and 4.1x)


Set Up Variables
A few variables are required to run this script:

The default page is always the first page. If no page number is specified, then page 1 will be shown

<cfparam name="url.page" default="1">


Here we set how many page number links will be shown at one time. If there are 100 pages of records, we probably do not want 100 page links running across the screen.

<cfset page_links_shown = 5>


How many records do we want on each page? I will set this to 5 for this tutorial. 1 to n is acceptable. Choosing how many records per page depends on your web site and how you want it to look and feel.

<cfset records_per_page = 5>


The variable start_record will tell MySQL the first record we want to get. Take the start page (let's say we are viewing page three) 3, multiply by records_ per_page (we set that to 5) and minus records_per_page (5) That leaves us with 3 * 5 - 5 = 10. Now we know the first row that we need (start_record) should be 10.

<cfset start_record = url.page * records_per_page - records_per_page>


The Queries...
The database that this tutorial uses is extremely simple. It has an 'id' field and a 'names' field. We will pull out the names to display.

This Query will give us the total number of records available. We will us the result of this later.

<cfquery name="get_count" datasource="#dsn#">
   SELECT COUNT(id) AS records FROM pages_names
</cfquery>


This Query will get the actual records for the page to display. This query actually has the secret to this whole tutorial. The MySQL LIMIT function. We tell it what row to start on, and how many rows after that to get. Simply plug in our variables start_record and records_per_page.

<cfquery name="get_names" datasource="#dsn#" result="get_data">
   SELECT names
   FROM pages_names
   ORDER BY names
   LIMIT #start_record#, #records_per_page#
</cfquery>


The Page Links...
The page links can come in many different varieties. I will show a few basic ones here, and a neat way to keep the page number links under control so they don't spread across the screen when there are a LOT of pages

Each of the Following section can be used separately or put in a different order. (previous / next links, first / last links, and the pages index)

First we will set up a few more variables that some of these page link examples will use.

Variable total_pages finds out how many pages total there will be by simply calculating the records per page and total records that we got earlier.

<cfset total_pages = ceiling(get_count.records / records_per_page)>


And let's start up our cfoutput so our variables can be used on screen.

<cfoutput>


We will output the query first. Remember, page one is default.

<cfloop query="get_names">
   #start_record + currentrow#. #names#<br/>
</cfloop>
<hr>


Previous Page Link:
The previous page link is dead simple. If the current page does not equal 1, then the previous page = current page minus 1. If the current page = 1, then show the text only, no link.

<cfif url.page EQ 1>
   Prev Page
<cfelse>
   <a href="pages.cfm?page=#url.page-1#">Prev Page</a>
</cfif>
<hr>


Next Page Link:
Next link is just as simple. If the current page multiplied by the records_per_page variable is Less than the total number of records, then there must be a next page, so show the link. Otherwise, show the text only.

<cfif url.page * records_per_page LT get_count.records>
   <a href="pages.cfm?page=#url.page+1#">Next Page</a>
<cfelse>
   Next Page
</cfif>
<hr>


Page Numbers Links Index:
Page number links are a little trickier, and can be done several ways. Here is a method I like. We need to set a few more variables if we are going to use this method. We need to know how many page links to display at one time. If there are too many page links on the screen, it can look bad. Imaging the numbers 1 through 100 on the screen on every page.

start_page is the default page to start showing links to. This is different than the default page of records to show. This variable only applies to the first page that we want to link to, and by default, it must be 1.

<cfparam name="start_page" default="1">


show_pages will determine how many page links to show at a time. We said 5 for this tutorial, but 10 or 15 is on too. If there are not enough records to fill the default, we will later set this to a lower number.

<cfparam name="show_pages" default="#min(page_links_shown,total_pages)#">


Now this next bit of code simply makes sure that our current page link is listed in the currently displayed page links index, since we are not showing every page link at a time. This code will 'roll' through the page links. This will make more sense in the working example. So if we have 10 pages, and at first we show links to pages 1,2,3,4,5 and click on page 5, when the page reloads, we will have links to pages 3,4,5,6,7 showing. You can dissect the code if you wish, but thats what it does. It gives us a value for start_page that will be used in the loop that write the page links out in the next step.
<cfif url.page + int(show_pages / 2) - 1 GTE total_pages>
   <cfset start_page = total_pages - show_pages + 1>
<cfelseif url.page + 1 GT show_pages>
   <cfset start_page = url.page - int(show_pages / 2)>
</cfif>


We need to set an end page for our page links to this is simply our start_page + show_pages variable that we set earlier minus 1.

<cfset end_page = start_page + show_pages - 1>


Now that we have a start_page and end_page variable, we'll loop from one to the other. We do a quick check that if the current page equals the page link we are writing, then don't make a link. The cfif within this loop is optional, but the loop and the output of the a href is required.

<cfloop from="#start_page#" to="#end_page#" index="i">
    <cfif url.page EQ i>
       #i#
   <cfelse>
       <a href="pages.cfm?page=#i#">#i#</a>
    </cfif>
</cfloop>
<hr>


First Page Link:
First Page / Last Page links are as easy as previous / next. First page ALWAYS is page 1.

 <a href="pages.cfm?page=1">First Page</a>
<hr>


Last Page Link:
Last page is ALWAYS the total_pages variable that we set earlier! That simple.
 <a href="pages.cfm?page=#total_pages#">Last Page</a>
<hr>


Finish the cfoutput

</cfoutput>


Optional features
· You may want to use the int() function on your outputs of page numbers in case some goober types a decimal into the url page= variable. This way, it will still work, but show whole numbers instead of decimal numbers for page links.

· Check for non-numerical url.page values. For the simplicity of this tutorial, I did not. This code will throw an error if url.page is a non-numeric value.

· You can also add a more complex page link index that has a (next 10) and (previous 10) pages option. Do what you choose, and whatever fits your layout and data the best.

CFDump To see your query results, query execution time, etc...
<cfdump var="#get_names#">
<cfdump var="#get_data#">