Before getting into this entry, let me share two quick notes about the last entry. First off, if you are a subscriber and only read the emails, I had made a small mistake in my logic. It was corrected on the entry. Thanks to my reader Fernando for pointing it out. Secondly, Rob Gonda made the point that you probably don't want to do pagination in ColdFusion if you are returning a hundred thousand or so results. He is absolutely correct and shows a better alternative here.
In the first entry, I demonstrated how you can use ColdFusion to do simple pagination. I had a query and a variable determining how many records to show per page. I showed how you could make links to let the user go back and forward one page a time. This works fine for a small set of results, but if you have more results, a user will have to hit "Next Page" a few too many times. What if you could simply provide a list of pages so a user could immediately jump to the page? It is really rather simple and I will show you how. Before going on though, be sure you read the last entry. (Boy, I sure wish BlogCFC support related entries. Oh wait, it does!.)
Let me start by showing the code, and then explaining what I did. At the end of the entry I'll post the complete code.
<cfset page = 0>
<cfloop index="x" from="1" to="#data.recordCount#" step="#perpage#">
<cfset page = page + 1>
<cfif url.start is not x>
<cfset link = cgi.script_name & "?start=" & x>
<cfoutput><a href="#link#">#page#</a></cfoutput>
<cfelse>
<cfoutput>#page#</cfoutput>
</cfif>
</cfloop>
The first thing I do is create a variable that will store the page number. This isn't actually needed as I could use math, but I've learned not to use math before I've had enough coffee.
Next I do a loop from one to the record count of the query. However, I use a step value of perpage. This makes it easy for me to split up my data into the right segments.
Inside the loop I increment page by one. Then I check to see if our current starting position is equal to X. If it isn't, it means I'm not on that page. I make a link (just like I did for the previous and next links) and just use X for my starting position. If not, I simply output X.
That's it. Now I have both a previous and next link, and between them, a list of pages for direct access to a particular page. Here is the complete code.
<cfset data = queryNew("id,name,age,active","integer,varchar,integer,bit")>
<cfloop index="x" from="1" to="31">
<cfset queryAddRow(data)>
<cfset querySetCell(data,"id",x)>
<cfset querySetCell(data,"name","User #x#")>
<cfset querySetCell(data,"age",randRange(20,90))>
<cfset querySetCell(data,"active",false)>
</cfloop>
<cfset perpage = 10>
<cfparam name="url.start" default="1">
<cfif not isNumeric(url.start) or url.start lt 1 or url.start gt data.recordCount or round(url.start) neq url.start>
<cfset url.start = 1>
</cfif>
<h2>Random People</h2>
<cfoutput query="data" startrow="#url.start#" maxrows="#perpage#">
#currentrow#) #name#<br />
</cfoutput>
<p align="right">
[
<cfif url.start gt 1>
<cfset link = cgi.script_name & "?start=" & (url.start - perpage)>
<cfoutput><a href="#link#">Previous Page</a></cfoutput>
<cfelse>
Previous Page
</cfif>
<cfset page = 0>
<cfloop index="x" from="1" to="#data.recordCount#" step="#perpage#">
<cfset page = page + 1>
<cfif url.start is not x>
<cfset link = cgi.script_name & "?start=" & x>
<cfoutput><a href="#link#">#page#</a></cfoutput>
<cfelse>
<cfoutput>#page#</cfoutput>
</cfif>
</cfloop>
<cfif (url.start + perpage - 1) lt data.recordCount>
<cfset link = cgi.script_name & "?start=" & (url.start + perpage)>
<cfoutput><a href="#link#">Next Page</a></cfoutput>
<cfelse>
Next Page
</cfif>
]
</p>
Archived Comments
Unlike you to make a fix to something the day after release Ray....
I know. What's up with that? I never make mistakes.
> ...I've learned not to use math before I've had enough coffee.
"I don't do math in public." - overheard on NPR; it has become my stock response when someone asks me to do arithmetic in a meeting.
A possible solution that sort of lies midway between this solution and Rob's is using a caching parameter in the query. It speeds up retrieval, and as long as you don't have too much change in the data or too many rows to retrieve, you get the performance boost with the cf pagination.
I thought about that too, but until cfmx7, query of queries has been buddy with datatypes...
Now with cfmx7, I wanted to find a medium not to cache the entire table, because again, my table weights about 10Gb :) Downloading 1000 records at a time and caching them in CF, and use CF pagination for smaller blocks could work ... but I find that to be too much complication.
One solution I've used in the past is to run two queries, each using the exact same joins, where clauses, grouping, and ordering. The only difference is that the first query is cached and returns only one column, whereas the second query returns the rows corresponding to the values in the first query that match the "page" you're viewing (e.g., rows 51 - 100 representing the second page of a 50-rows-per-page pagination).
It's definitely a complicated solution, but it works well to pull only a few records from the db in what could otherwise be a massive query.
Rob, that's a sweet idea...one way you might implement it is by having CF do the loading in the background. I did a site not too long ago that had to pull medical provider info for multiple insurance plans. I didn't know which provider they would start with (depends on which page they enter the site through), but I wanted them to have every provider's page ready after entering the zip code any any entry page.
So, I stored the data for each user's zip in session, and then had CF GET the provider's list. When the page rendered, I had CF decide which providers were left to get, write a little JavaScript in the page to do an AJAX load of the remaining session objects (one per provider). When the user went to the next page, CF checked for a populated object and did the GET itself if the AJAX failed. Otherwise, voila, the object is already there. It's really simple AJAX, since there is nothing to render - it's just doing an asyn load in the background.
Anyhow, you could simplify block caching in this way. You work with the current 1,000 record set until the user pages up near the end of the block, then AJAX-call the next block into cache. It is a little more complex, but you can use the same stored proc and just let CF decide what the page limits should be.
Tom, I've done that as well. Our CMS uses it for pagination in the admin.
Another option lies in SQL2005 where it has query pagination built in..
<code>
SELECT
myField1
, myField2
, RowID
FROM
(
SELECT
myField1
, myField2
, ROW_NUMBER() OVER ( ORDER BY myField1, myField2 ) AS RowID
FROM
myHugeAndIncomprehensibleTablesAndJoins
WHERE
AnyFiltersIWant = 1
) myPageQuery
WHERE
RowID BETWEEN 21 AND 30
ORDER BY
RowID
</code>
Neil,
Yes, or use rownum in Oracle, but you still need to hit the db and pull the data across the network. With caching, the query is right there in CF memory.
MySQL has LIMIT to do the same.
So its interesting to randomly come to Rays site and see the new things in discussion. It just so happened that i was bored one day and wanted to set up a db for myself and my friends to look at and search my music. (Before anyone goes all anti-pirate on me, there is no access to the actual music. It is just a listing of what i have.)
I have seen some interesting ways to do paging of results but this one caught my eye. It is the ability do display the pages in "chunks" as i call it. I chose 3 page chunking. This allows the first 3 pages and last 3 pages always to be visible and then the rest are in a "..." filler. The current page would show 2 previous pages and 2 next pages in the page listing. so for example it would be this link: http://wilsondesigns.ath.cx...
I would explain it but seeing it is easier. Now it took me a couple hours to figure out the algorithm for this, but i am not sure if there was a better way to do it. If there isnt then the code is below for anyone to use.
<!--- Begin: Code --->
<cfparam name="page" default="0">
<cfparam name="pageChunk" default="3">
<cfparam name="perPage" default="50">
<cfparam name="searchCriteria" default="">
<cfquery>QUERY CODE HERE</cfquery>
<cfset startRow = (page*perPage)+1>
<cfset endRow = (startRow+perPage)-1>
<cfif endRow GT searchdb.recordCount>
<cfset endRow = searchdb.recordCount>
</cfif>
<cfset numPages = Ceiling(searchdb.recordCount/perPage)>
<cfset pagesLeft = numPages - pageChunk>
<cfif pagesLeft GT pageChunk>
<cfset endFrom = numPages-pageChunk>
<cfelse>
<cfset endFrom = numPages-pagesLeft>
</cfif>
<!--- Begin: Next Previous --->
<cfoutput>
<cfif page GT 0>
<a onfocus="this.blur();" href="?page=#page-1#&searchCriteria=#searchCriteria#"><< Prev</a>
<cfelse>
<< Prev
</cfif>
<cfif page LT numPages-1>
<a onfocus="this.blur();" href="?page=#page+1#&searchCriteria=#searchCriteria#">Next >></a>
<cfelse>
Next >>
</cfif>
</cfoutput>
<!--- End: Next Previous --->
<!--- Begin: Page Numbering --->
<cfoutput>
<!--- Begin: FirstChunk --->
<cfif numPages GT pageChunk>
<cfloop from="0" to="#pageChunk-1#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
<cfif i GT 0>
|
</cfif>
<a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
<cfif i GT 0>
|
</cfif>
#sRow#-#eRow#
</cfif>
</cfloop>
<!--- End: FirstChunk --->
<!--- Begin: MiddleChunk --->
<cfif numPages GT 2*pageChunk>
<cfif page-pageChunk GTE pageChunk>
| ...
</cfif>
<cfset midTo = page+(pageChunk-1)>
<cfif midTo GTE endFrom>
<cfset midTo = endFrom-1>
</cfif>
<cfif midTo GTE pageChunk>
<cfset midFrom = page-(pageChunk-1)>
<cfif midFrom LTE pageChunk>
<cfset midFrom = pageChunk>
</cfif>
<cfif midFrom LT endFrom>
<cfloop from="#midFrom#" to="#midTo#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
| <a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
| #sRow#-#eRow#
</cfif>
</cfloop>
</cfif>
</cfif>
<cfif endFrom-page GT pageChunk>
| ...
</cfif>
</cfif>
<!--- End: MiddleChunk --->
<!--- Begin: LastChunk --->
<cfloop from="#endFrom#" to="#numPages-1#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
| <a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
| #sRow#-#eRow#
</cfif>
</cfloop>
<!--- End: LastChunk --->
<cfelse>
<!--- Begin: One Chunk Case --->
<cfloop from="0" to="#numPages-1#" index="i">
<cfset sRow = (i*perPage)+1>
<cfset eRow = (sRow+perPage)-1>
<cfif eRow GT searchdb.recordCount>
<cfset eRow = searchdb.recordCount>
</cfif>
<cfif i NEQ page>
<cfif i GT 0>
|
</cfif>
<a onfocus="this.blur();" href="?page=#i#&searchCriteria=#searchCriteria#">#sRow#-#eRow#</a>
<cfelse>
<cfif i GT 0>
|
</cfif>
#sRow#-#eRow#
</cfif>
</cfloop>
<!--- End: One Chunk Case --->
</cfif>
</cfoutput>
<!--- End: Page Numbering --->
wow that looks hideous...how can i make that code look nicer in with tabs?
Raymond, I think you forgot one part in your StartRow sanitation, how about 'not structKeyExists(URL, "start")' ?
No, I have a cfparam above it.
I am currently in the hell-hole that is pagination in SQL-Server 2000, and its driving me bonkers to no end.
I am trying to return 50 results per page, and I just can't get the SQL to return stuff in the correct order, or to return the right data.
I started off with a variation of MSDN's official suggestion:
(link: http://msdn.microsoft.com/l...
---------
SELECT TOP <pageSize> CustomerID,CompanyName,ContactName,ContactTitle
FROM
(SELECT TOP <currentPageNumber * pageSize>
CustomerID,CompanyName,ContactName,ContactTitle
FROM
Customers AS T1 ORDER BY ContactName DESC)
AS T2 ORDER BY ContactName ASC
---------
That kind of works - except it starts at the end, with the Z's. I kind of got this to work, but wrapping this in yet ANOTHER SELECT top 50 order by statement. So now there are 3 levels, and i know for sure one of them is redundant. I was frustrated enough that i was willing to accept this and be done with it, except i noticed im not getting accurate results for some reason.
So my question for the group - can someone recommend a better nested SQL query, or a different way BESIDES using a stored procedure?
Here is the actual SQL code i am using:
SELECT *
FROM (SELECT TOP 50 *
FROM (SELECT TOP 150 first_name, last_name, label_name
FROM USR_WEB_CONTACTS
WHERE 1 = 1 AND (Last_Name LIKE '%smith%' OR
first_name LIKE '%smith%')
ORDER BY Last_Name ASC) DERIVEDTBL
ORDER BY Last_Name DESC) DERIVEDTBL
ORDER BY last_name
-----
I have noticed that when i add columns to the list (first_name, last_name, label_name) it starts giving me back a different set of data. which i find very surprising - how could this happen?
I was using Ray's fabulous pagination code and ran into this lovely IE bug (err... feature).
Here was my url created for the next set of results:
mysite.com/page.cfm?start=1...
It worked beautifully in Mozilla, Opera, and Safari but didn't in IE. Internet Explorer rendered the link as:
mysite.com/page.cfm?start=10°_id=15
It turns out there is a special HTML code for the degree sign. The code is ° IE is assuming that I forgot the semi-colon and is plugging the special character in for me.
Thanks but no thanks IE. Guess I'll have to move my deg_id variable to the start of the query string so that it shows up after the question mark. Either that or change the variable name.
I'd just change the variable name.