CFML Scripts and Tutorials

Easy Pagination with MySQL

Get the Code for this Tutorial
View a Working Example of This Code
Comment on this Tutorial

Introduction
This Tutorial will show you an example of a more efficient pagination method than those commonly used. Most simple pagination methods get every record for every view. Say we have a table with 5000 records. Page one gets all 5000 records, shows you 1 through 50. Click on page two. It goes back to the database, gets all 5000 records, and shows you 51 through 100. Try that with 100,000 records and your fired because your site is too slow. On the other hand, there is no pagination system that will work for every situation. Some data must always be fresh, sometimes there is millions of records. This method does still require a table scan to perform an order by. It is probaly not the best solution for HUGE tables. It IS a good solution for large tables that require FRESH data.

This technique only SELECTS the data need for the current page. For data that changes too often to cache for all, and is too large to cache per session this technique works great. The only drawback could be that this example only works with MySQL and should work on PostgreSQL (not tested). Another consideration is that although this example does not bring back the COMPLETE recordset each page, it DOES hit the database for every page. Even though it is only getting what is needed per page. I will have an alternate method available soon for caching recordsets per session for fast changing recordsets that can still benefit from query caching.

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.


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


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. If you are using MySQL 4.1 or higher, that supports sub queries, you can integrate these two queries into one for a cleaner code. For this tutorial I decided to keep them separate.

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 cfml_example_data
</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 and OFFSET function.
We tell it what row to start on with OFFSET, and how many rows after that to get with LIMIT. Simply plug in our variables start_record and records_per_page.

<cfquery name="get_names" datasource="#dsn#" result="get_data">
   SELECT names
   FROM cfml_example_data
   ORDER BY names
   LIMIT <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#records_per_page#">
   OFFSET <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#start_record#">
</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. You will probably want to put a table around this or something if you have more then one column of data..

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


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>


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>


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>
   

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>


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>

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#">
____________

Tutorial Code

Comments

Gavy  
04/18/2006

Awesome Tutorial Keep it Up

Justice  
04/25/2006

Just a Q: Why would you want to do pagination in a DB specific way as opposed to using the query startrow / maxrows options? I understand that this will make the SQL query result set smaller and may realize some speed increase from that, but if you properly cache the query, any resulting page request can just use startrow / maxrows and not even have to hit the DB at all? Not to mash on your code man, I just dont know that I would do it exactly the same ;)

Webmaster  
06/26/2006

1. Caching a query that returns a massive recordset is not practical when the data needs to be fresh to each user. 2. The startrow / maxrows attributes are for cfoutput, you still need a full recordset from your query. You could use maxrows on the cfquery tag, but then you still need to specify in the SQL somewhere a start row, otherwise your data is wrong, or you have to return the whole recordset again, and get stuck again with a DB specific query. This is not the be all, end all of pagination. It is simply a EASY and efficient method using MySQL. It is meant for data that needs to be fresh, and will just work fine on somewhat large tables.

humblepie  
12/16/2006

If you've set up a search page that uses one submit button for a text box, radio buttons of dates and dropdown list of from dates and to dates,and the form is self-posting, where would you place the code above? Also, will this work with a sql 2005 database as well?

Webmaster  
12/16/2006

Where you place the code is up to you and what fits your application better. You would want to place the query in a place that can use the form variables that you are posting, and the other navigational code in a place that the user can use to move through the pages. It would be in the 'action' part for the script for that form. Hope that helps?

david  
03/19/2007

Nice one Kev, Q:I am using this was shown with UNION with a few tables. When the records_per_page is set to more than the total records it displays them all fine, but when the records_per_page is set less and i start to page through the records the last two records are not being displayed?, would this be from how i querying the db? here's a cut down how im going about it. I have 7 tables and using UNION.. SELECT COUNT(ID) AS records FROM db.t1 WHERE t1.c3 = v UNION SELECT COUNT(ID) AS records FROM db.t2 WHERE t2.c3 = v ---------- (SELECT ID,t1.c2 WHERE t1.c3 = v) UNION (SELECT ID,t2.c2 WHERE t2.c3 = v) LIMIT #start_records#, #records_per_page#

Read

Database

Let the database do it's job. Make use of database functions such as SUM(), AVG(), COUNT(). Your code will be simpler, and more effiecent.

Books

MX 7 Certified Developer Study Guide

Advanced MX 7 Application Development

MX7 Web Application Construction Kit

A lot-o-nothin website | Copyright © Lot-o-Nothin 2008