Coldfusion Pagination Tutorial

Hi everyone.

I’m trying to implement some pagination on a site that I’m building. I’m interfacing with MySQL, and was wondering if anyone knew of any tutorials that were good besides this:

It’s a pretty well commented bunch of code, but i can’t seem to connect to the Access DB that he has available for download. Alternatively, help with this would be helpful. How’s that sentence for redundancy?

we use this tag and find it to be awesome.

Hey jonese

Thanks a lot. That tag worked like a charm!

the code i tend to use alot can be found:

those solutions do not address the real issue of pagination. the ENTIRE recordset is still being retreived on EVERY page. So now instead of ONE large recordset, and ONE large display, you’ll get a large recordset for EVERY display page.

Pagination needs to take place at the query.

but if query results caching is employed, the entire recordset is returned only once

:slight_smile: :slight_smile:

Easy Pagination With MySQL and ColdFusion

Pagination really isn’t as hard as it seems once you get into it. You just have to remember what you are after. Like imstillatwork said, if your query is bringing back the full recordset every time, you arent helping yourself any except with the UI. But, like rudy said, if you bring back the query and cache it, it works just as well. And again, there is more than one way to cache the query.

So, are you still looking for help understanding pagination, or did that custom tag work just fine for you?

As far as I can tell the custom tag worked fine. But, then again, I wouldn’t mind going through that tutorial. I think it’s always better to build/modify something yourself rather than using someone else’s code. Just easier for me to get my head around.

In terms of the custom tag, there was some modification I had to do when passing variablesfrom “Page to Page”. You had to just have the id, start and page_no parameters passed in the URL as opposed to just two. I forget which two got passed initially.

But other than that it seems to work fine. With regards to addressing the issue of the whole recordset being passed… this could become a query-time issue if you’re accessing a large record set each time, could it not?

do you mean query time as far as execution time? theres two ways to handle that. Either you only bring back the records you need for that one page at a time, or you do the entire query and cache it, using it each time. Either way would lessen that impact.

Here’s the code I wrote for this very thing…

Author:			Andy Matthews
Client:			myCMS
Date:			9/13/2005

<!--- these params are used in the paging setup in the LIST case statement --->
<!--- set incoming page arguments --->
<cfparam name="" default="0">
<cfparam name="URL.rid" default="0">
<cfparam name="URL.start" default="0">
<cfparam name="" default="0">
<cfparam name="" default="1">
<cfparam name="totalperpage" default="15">
	The first thing we do is to determine how many records there are in the db
	so that we can tell the user how many records there are
<!--- get total number of records --->
<cfquery name="getAll" dataSource="#Application.DSN#" dbType="#Application.DBType#" username="#Application.username#" password="#Application.password#">
	SELECT news_id
	FROM news_posts
<cfset totalrecords = getAll.recordcount>

	Then we determine how many records to be displayed on this page
<!--- the actual display query --->
<cfquery name="getAllInRange" dataSource="#Application.DSN#" dbType="#Application.DBType#" username="#Application.username#" password="#Application.password#">
	SELECT news_id, news_title, fk_user_id, news_date, news_status
	FROM news_posts
	ORDER BY news_date DESC
	LIMIT #URL.start#, #totalperpage#
<cfset onthispage = getAllInRange.recordcount>
<cfset totalpages = Ceiling(getAll.recordcount/totalperpage)>

<!--- begin: output block --->

	<!--- display total records  --->
	<b>Displaying #onthispage# of #totalrecords# news posts.</b>
	<cfif totalpages GT 1>
		<div id="paging">
		<cfset URL.start = 0>
		<cfloop index="page" from="1" to="#totalpages#" step="1">
			<cfif page EQ>
				<a href="#thispage#?&start=#URL.start#&page=#page#">#page#</a>
			<cfif page NEQ totalpages>|</cfif>
			<cfset URL.start = (URL.start+totalperpage)>

wow… thanks guys. i have one question which i feel silly asking… i don’t understand what is meant by “creating a query and caching it…”

If anyone could be kind enough to explain this it would be much appreciated.

the first thing you do is return a bazillion rows to coldfusion and let coldfusion count them??


no offence to you personally, andy, because i’ve seen this a hunnert times, but the first thing you should do is look up the COUNT SQL function, and then decide that it’s a lot more efficient to ask the database to tell you how many rows are in the table, so that it returns a single value with the number, resulting in a much, much, much more efficient database call


i take it Access doesn’t support LIMIT. could you use TOP 3, 9 in the same way?

nope, to achieve the same using TOP requires jumping through triple hoops backwards and handcuffed

i’ve got a link to an article which explains it, but it’s at home

the situation isn’t pretty

in effect, both the LIMIT and the TOP approach require that the database engine retrieves all rows, but then returns only some of them to the calling script

Thanks Rudy!

which is not easy.

yeah, i see what you mean.

forgot to mention, the reason the database has to retrieve all rows (but then it only returns some of them to you) is because it has to sort them all!!

the reason it has to sort all the rows is of course because the idea of paging through results makes no sense unless there’s a specific sequence

producing the sorted result set that comes out of executing a query could be the single most expensive thing you can possibly do – just think about the performance of a query that requires several joins, with elaborate search criteria (e.g. WHERE CustomerName LIKE ‘%foo%’

to me, it makes a lot of sense to bring the entire result set into coldfusion query cache only once, and then let coldfusion page through the cache using STARTAT and MAXROWS

this way, you incur the most expensive performance hit only once, rather than on every single call for another page of results

so in my opinion, pagination should not occur at the query, which contradicts what was said in post #5

as far as the caching goes, unless this data hardly ever changes, I would do one select to get the entire recordset, and then store it in the session. Then you can pull out of the session using a cfloop with the startrow and endrow attributes to handle your pagination.

I agree with rudy, you shouldnt handle the pagination in the query itself.

And yeah creole, a count() query would save you mucho processo in that script. But if you bring back the entire query anyway, you already know how many records there are. (still using your recordCount member)