ColdFusion Pagination Made Easy

I know I haven’t posted in quite some time, but I’ve written a small method to help handle some of my day to day pagination challenges and I thought I’d share it. I’ve created a demo which uses MySQL, fake data, a ColdFusion customTag and the pagination method.

I imagine there’s quite a few developers that have already created something like this for themselves, so I apologize for being redundant if so. This is for the people who are constantly copying and pasting the same code over and over again throughout their applications. Not only that, but for anyone who really struggles to understand and implement pagination, this is great for you.

I won’t waste any of your time, so here’s a link to the demo if you want to skip the text and start testing it out already.

For the others that have stayed, I’m going to break down how to use it. Let’s start off with the a query. I’m using MySQL pagination, so it’s bit less involved than an RDBMS like Oracle. For anyone that needs assistance with implementing pagination with Oracle, please comment or send me an email and I’ll be happy to help out.

<cfset paginationArgs = StructNew() />

<cfset paginationArgs.rowsPerPage = 30 />

<cfset paginationArgs.page = url.page />

<cfset paginationArgs.urlString = cgi.QUERY_STRING />

<cfset start = paginationArgs.rowsPerPage * url.page />

<cfset start = start - paginationArgs.rowsPerPage />

<!--- query for some data and cache the query --->

<cfquery name="getAllMembers" datasource="local_sample">

SELECT first_name,last_name,email,zip, (select count(*) from member) as totalCount

FROM member

LIMIT <cfqueryparam value="#start#" cfsqltype="cf_sql_integer" />,<cfqueryparam value="#paginationArgs.rowsPerPage#" cfsqltype="cf_sql_integer" />

</cfquery>

<cfset paginationArgs.totalCount = getAllMembers.totalCount />

<cfset paginationArgs.currentRecordCount = getAllMembers.recordCount />

You’ll notice that I had to do a bit of work before hand to get get the appropriate “start” value for the SQL query. This is merely the product of “the amount of rows per page you want to show” * “your current page” – “the amount of rows per page you want to show”. So if you’re showing 30 records a page and you’re on page 3, you’re looking at: ((30 * 3) – 30) = 60. This is telling the query that you want to start retrieving records 60 rows into the result set and you’re limiting it by 30 rows. This would mean you’re seeing rows 61-90. I’m willing to bet this is overly thorough, but better too much than too little.

You’ll notice that I’ve got a structure with some values going around that query, I’ll explain now with the pagination function.

<!----

//////////////////////////////////////////////////////////////////////////////////////////////////

Filename: Paginate.cfc

Purpose: To aggregate all of the basic pagination needs into one reusable function

Change Log:

04/22/2013 - Michael Stone - Created

//////////////////////////////////////////////////////////////////////////////////////////////////

---->

<cfcomponent>

<cffunction name="createPagination" output="no" access="public" returntype="struct" hint="a generic method that handles the essential needs of pagination">

<cfargument name="currentRecordCount" type="numeric" default="0" />

<cfargument name="totalCount" type="numeric" default="0" />

<cfargument name="rowsPerPage" type="numeric" default="10" />

<cfargument name="page" type="numeric" default="1" />

<cfargument name="maxPagesBefore" type="numeric" default="3" />

<cfargument name="maxPagesAfter" type="numeric" default="3" />

<cfargument name="urlString" type="string" default="" hint="This will typically just be cgi.query_string" />

<cfargument name="pageVar" type="string" default="page" />

<cfset local.paginationStruct = StructNew() />

<cfset local.multiUrlParamsReplace = "" />

<!--- let's first check to see if we should see the "&" or not for when we're doing the page number replace --->

<cfif listLen(arguments.urlString,"&") GT 1>

Page 1 of 3 | Next page