One of the more common tasks a web developer gets asked to do is add pagination to a result set. By pagination I simply mean displaying one "page" of content at a time. So if you had 22 records and wanted to show 10 at a time, there would be three pages of content. Let's take a look at one way to solve this problem.
First, let's get some data:
<cfset data = queryNew("id,name,age,active","integer,varchar,integer,bit")>
<cfloop index="x" from="1" to="22"> <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>
I use queryNew to create a "fake" query. I then populate it with random data. Normally you would have a real cfquery here, but I think you get the point. Now I'm going to need to know how many items to show per page. I could hard code a number and use that, but I know it's best to abstract this into a variable:
<cfset perpage = 10>
I'd probably suggest an application variable actually as if you use pagination in one place, you will probably use it in multiple places, and you want to be consistent. Next I'm going to create a variable that will tell me what record I'll be starting with. This isn't the current page per se, but ends up being the same thing. So if we had 22 records, the first page will start with record 1. The second page will start with record 11. Here is the variable I will use along with the validation:
<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>
There is a lot going on in that conditional, so let me break it out. IsNumeric will ensure that the URL variable is a number and not some other string like "apple." The lt 1 and gt data.recordCount simply ensures we are starting between 1 and the total number of rows in the query. Lastly, the round check simply ensures we have an integer value and not something like 10.2. Probably a bit overkill, but you can't be too careful with URL (and other client controlled) data.
Now let's display the data:
<h2>Random People</h2>
<cfoutput query="data" startrow="#url.start#" maxrows="#perpage#">
#currentrow#) #name#<br />
</cfoutput>
Nothing magic here. I simply tell cfoutput to loop from the starting index and stop after perpage records. Notice my lovely use of HTML. Ok, my design sucks, but you get the idea. Now let's do the pagination code:
<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>
/
<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>
Basically there are two things going on here, ignoring my simple HTML. The first cfif block checks to see if we need to make a linked or plain text "Previous Page" output. If url.start is above 1, then we need to make the previous link hot. I do this by checking the current script_name. I could have hard coded it as well. I then simply set start to the current value minus the number of entries per page. Note - a user could change url.start so that it is a low number, like 3. Then the value in the link would be negative. However, I already took care of that so I'm covered. This link doesn't handle other URL variables in the link. I'll cover that in a later blog entry if folks are interested.
The next cfif block is virtually the same as the first one. The logic here is to check if the current starting row, plus the per page value, minus one, is less then the total. Seems a bit complex, but the basic idea is to see if we have complete additional page of records to display.
That's it! Very quick and simple pagination.
Note: I edited the entry due to a bug found by Fernando. Thanks Fernando!
Archived Comments
Great!
I've been using the pagination that was supplied in the CF WACK book, but this is another way for me to learn what's going on.
<blockquote>I use queryNew to create a "fake" query.</blockquote>
I prefer to call it a "manual" query or more appropriately a "manual query result set". Bah. Semantics.
Nah, you are right. I'll start using that term as well.
I like to do a variation upon this theme:
<cfparam name="startRow" default="1">
<cfparam name="rowsPerPage" default="20">
... cfquery or cfstoredproc ...
<cfset totalRecords = query.recordcount>
<cfset endRow = (startRow + rowsPerPage) - 1>
<!--- If the endrow is greater than the total, set the end row to to total --->
<cfif endRow GT totalRecords>
<cfset endRow = totalRecords>
</cfif>
<!--- Add an extra page if you have leftovers --->
<cfif (totalRecords MOD rowsPerPage) GT 0>
<cfset totalPages = totalPages + 1>
</cfif>
<!--- Display all of the pages --->
<cfloop from="1" to="#totalPages#" index="i">
<cfset startRow = ((i - 1) * rowsPerPage) + 1>
<a href="foo.cfm?startRow=#startRow#">#i#</a>
</cfloop>
A little verbose, but it manages extra pages just fine. You can refine the process to calculate the boundaries, but the practice is similar.
A little trick for your end row logic. Don't forget the max/min functions:
<cfset endRow = max(startRow+rowsperpage-1, totalrecords)>
Oops, I meant min(...) :)
That is exactly the refinement I was referring to. =)
Good stuff, RC.
The problem is: it breaks when the result set has 21 records.
(that is the problem with those #@#*@# "paginations" ever.
There's a lot of ways to solve it, like use ceiling(recordcount/perpage) to know the exact number of pages you will have - just to say, as I don't like to be the guy who points the problem and don't solve it ;)
Dang it. I had meant to test an 'edge' condition like that and forgot. I'm very sorry to all my readers! I will try to fix this asap.
Ok, I kinda remember this issue. This change to the cfif will fix it:
<cfif (url.start + perpage - 1) lt data.recordCount>
I'm going to update the blog entry in an hour or so. (Since printing doesn't show comments.)
I try to keep pagination in the SQL level, never in CF. It is extremely inefficient to transfer 50k records each time I want to display the next block of 20... SQL is much more efficient in fetching only the block I need and transfer only the minimum required information back to the application server.
The advantage of doing it within CF is that it is generalized for all datasources, but IMO the consequences are not worth it. I use a generic stored proc (mssql) to paginate any of my tables or views. It's been working like a charm for years.
The code works perfectly for small datasets, but if you have anything in the thousands (or millions like me) ... not such a good idea :)
Fernando, thanks for the bug report. I edited the entry.
You're welcome, Ray. Your work for the community is memorable, *I* thank you!
Best wishes!
Rob, care to share your generic MSSQL implementation with any of us? I'm very curious as to how you chose to implement it. We've played around with doing that here at our company, but never got it quite right.
I am curious as well about the MSSQL solution. I ahve seen pagination with dynamic SQL statements, numbered aggregated tables that are searched often and data hierarchies that resemble Celko's data hierarchy.
If your using MySQL or PostgreSQL you can use the LIMIT and OFFSET in your SQL I have an example here: http://www.petefreitag.com/...
For the equivalent of LIMIT and OFFSET with MS SQL Server, see this article:
http://msdn.microsoft.com/l...
It includes various methods for doing pagination in MSSQL Server:
- Select Top (usually not practical)
- User-Specific Records (SQL Query or Stored Procedure)
- Application-Wide Records (Stored Procedure)
just posted my solution. Feel free to check it out:
http://www.robgonda.com/blo...
hi
i need ur help. I want to implement pagination using flash forms...
Can u please guide me in this matter
pagination is swell. there ar a tons of ways to do it, and it kind of depands on how fresh you need your data (caching or not, etc..)
I have a DELIMA!
How can I EFFICIENTLY implement a pagination system with the query has a 1 to many JOIN and the output is using GROUP?
The record count and start rows are NOT accurate to the groups of data being worked with....
for example, I could have a query that returns 5 rows, but in reality, it is 2 main rows, one with 2 JOINED rows and the other with 3 joined rows... pagination goes to hell real quickly!
Anyone go t a suggestion?
How to you add the number of the pages and on which page are you?
For example the initial page would be "Page 1 from 10", when you hit Next you would have "Page 2 from 10"...and so on...
Thanks.
You know what row you are starting on, it's the url variable..
you know how many rows per page...it's a variable in the script...
so if perpage = 10, and start = 21 (page 3) is:
Records #perpage# to #perpage+url.start#
Thanks, Ray
How would I add a button to this schema to show "All" records on one page?
The form would simply pass a value that you would check for, and when you display the query, you wouldn't filter by a page.
I have a similar problem as 'imstillatwork'. I have a query that returns data that has one-to-many relationships. To output correctly, I use group="[variable]" to keep rows from outputting twice.
However, the RecordCount counts those double records, before I group and output. So, even though my page says
Records 1 - 50 of 250 records, when it really should say something like 1-50 of 185 records. The difference of 65 records is directly related to the fact that some records are foreign keys in a different table, and may refer to the 'main' record more than once. Is there an efficient way to count the rows after the query and the grouping?
Ok, so I have a work around for my question above.
After my [query of a] query, I am simply looping through a <CFOUTPUT> grouping and setting a variable of how many actual rows (after being grouped) are to be displayed.
I know this can't be the most efficient way to do this, but it works for now. Any suggestions/improvements would be appreciated.
<cfset groupedRows = 0>
<cfoutput query="FilterDocs" group="fulldoc_id">
<cfset groupedRows = groupedRows+1>
</cfoutput>
Raymond, thanks for your website and your CF work.
Cody R.
I'm in vacation mode now so my brain isn't all hear, but if I grok you right, I think keeping a simple counter is a fine way to do it.
thanks for this, Ray. It just helped me.
Very simple and easy to follow. Thanks Ray!
Thanks Ray. That is a nice little pager.
For applications that fall between the thousands or millions of records like Rob Gonda needs where you would want to do it in SQL and for just a few pages of records where Ray's pagination would work well and be very efficient, I use the cf_pager that is available in the CF exchange.
http://www.adobe.com/cfusio...
This allows users to jump to pages in between first, last and next. It is very easy to deploy and reproduce on list pages through out your application.
Hi Ray
Many thanks for your help with this one.
You mention in your conclusion that "This link doesn't handle other URL variables"
Would you be able to point me in the direction of one of your other "blog entries" that would cover this, or be able to post an example that covers multiple URL variables?
I would like to pass a URL variable to each page when a user clicks on the "Next Page" link.
Apologies for asking what may be a simple question, but I'm struggling at the momment.
Thanks for a great site and your help in the community. Its very much appreaciated.
Regards
Les
Additional variables would simply be passed along the URL. So if you were viewing a set of pages for category 1 (all products in category 1) which is based on url.category, then you simply assure all links add &category=#url.category# to them.
Hi Ray and thanks for the prompt help.
I had used the option you suggested but based on my line of code as shown below:
<cfset link = cgi.script_name & "?ShopTypeID=#URL.shoptypeid#&?start=" & (url.start + perpage)> (where i have added the variable to be passed on)
When the page is first run and say I have the default set to show 9 results out of a db of 10, the page shows 9 results as you would expect. But when I click on the "next page" link it returns those same 9 results instead of the next page with just the 1 result.
Any suggested would be greatly appreaciated.
Confused at this time in the morning:)
Les
Hey Leslie, looks like this may just be a type-o. In your code you have an extra '?' which may be breaking the link and not allowing the start variable to be passed and acknowledged by the script.
"?ShopTypeID=#URL.shoptypeid#&?start=" &
should be
"?ShopTypeID=#URL.shoptypeid#&start=" &
Try that and see if it ficxes your problem.
Hi Richard
Thanks for the suggestion above. It works perfectly.
I'm now off to the opticians:)
Thanks
Les
Awesome. Easy. Thanks.
<style type="text/css">
a { color: #444; font-size: 11px; text-decoration: none; }
a:hover { color: #777; text-decoration: underline; }
.pagination { color: #222; font-weight: bold; font-size: 12px; }
</style>
<cfparam name="url.startRow" default="1">
<cfparam name="url.rowsPerPage" default="10">
<cfset totalRecords = getInventory.recordcount>
<cfset totalPages = totalRecords / rowsPerPage>
<cfset endRow = (startRow + rowsPerPage) - 1>
<cfparam name="currentPage" default="1">
<!--- If the endrow is greater than the total, set the end row to to total --->
<cfif endRow GT totalRecords>
<cfset endRow = totalRecords>
</cfif>
<!--- Add an extra page if you have leftovers --->
<cfif (totalRecords MOD rowsPerPage) GT 0>
<cfset totalPages = totalPages + 1>
</cfif>
<!--- Display all of the pages --->
<cfif totalPages gte 2>
<cfloop from="1" to="#totalPages#" index="i">
<cfset startRow = ((i - 1) * rowsPerPage) + 1>
<cfif currentPage neq i>
<a href="foo.cfm?startRow=#startRow¤tPage=#i#">
<cfelse>
<span class="pagination">
</cfif>
#i#
<cfif currentPage neq i>
</a>
<cfelse>
</span>
</cfif>
</cfloop>
</cfif>
Works like a chime.
Thanks Ray.
Wow, It look very good.
I will post my code as the reference when time allows me. by the way, please go to http://newgugu.com/Software... to see my pagination sample.
I am using this method for judges to paginate though lists of poems in order to score them. There are 500+ poems to go through. Once the judge submits his score, I have been trying to use cflocation to send the judge from the scoring page back to the list of poems and return to the same spot he was. In other words if the judge is on ?start=301 and selects a poems, scores it, how can I use cflocation to send him back to the listing page with ?start=301 appended to the URL?
When you link to the judge page from your paging page (sorry, your page that has paging ;) pass along the current page #. When judging and you link back, just pass it back.
i have a problem when i want make the pagination, my query is built dinamically, so when i clic on next to reload my action form i lost the values, can you help me ??
<cfquery name="bepapf2" datasource="sibapro"
CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#" result="result">
SELECT LICITACION, ESTADO, LOCALIDAD, TIPO, DESCRIPCION, RUBRO, ESTUDIOS, PROYECTO,
CATALOGO_CONCEPTOS, MATRIZ_MUDA, ESPECIFICACIONES, RUTA
FROM catalogo
where DEPENDENCIA LIKE 'API VER'
<!---Buscamos por Tipo --->
<cfif FORM.Tipo IS NOT "">
AND Tipo LIKE '%#FORM.Tipo#%' <!---Aqui es donde yo le digo que tipo desde el form --->
</cfif>
<!---Buscamos por Rubro --->
<cfif FORM.Rubro IS NOT "">
and rubro like '%#FORM.Rubro#%'
</cfif>
<!---Buscamos por Subrubro --->
<cfif FORM.SubRubro IS NOT "">
and SubRubro like '%#FORM.SubRubro#%'
</cfif>
<!---Buscamos por anio --->
<cfif FORM.anio IS NOT "">
and fecha like '%#FORM.anio#%'
</cfif>
<!---Buscamos por Subrubro --->
<cfif FORM.Estado IS NOT "">
and Estado like '%#FORM.Estado#%'
</cfif>
<!---Nos traemos la ruta --->
</cfquery>
You will want to either store the values in the session scope or include them in the URL as well.
sorry Raymond im very new in CF, can u give me an example for pass the values and for a session scope ??
this is my url
<a href="form_action_veracruz.cfm?start=#Evaluate("start + disp")#">Next #next# records</a>
Are you familiar with how to create query strings, ie, the proper way to form a URL?
Are you familiar with ColdFusion's session scope?
Hi Raymond, sorry, im very busy with my job, i have to make to many different things, im returning to ColdFusion now and i will search information about URL and session Scope like u advise, thanks a lot.
Hi Raymond, i was studying your book "Adobe ColdFusion 9 Web Application Construction Kit" and i resolve my problem!! using the session variables, thanks a lot Raymond :)
Glad to help. To be clear though, the CFWACK is written by many people. :)
This solution is only really suitable for small recordsets though isn't it?.
If you, for example, wanted to return the results of a search on a large database where the search results could run into 1000's or 10000's then returning the entire recordset each time is not very efficient.
In such a case you're looking at limiting the records brought back by the database but, in order to get each 'page' of records you need to sort and get the top x etc. whilst still respecting the sort order of the records - i.e. you're criteria needs to pass the last/first value of each 'page' (depending on which way you are going) in order to get the next group.
Also means that giving a total number of pages and links to specific pages become difficult then since you'd have to return at least a count of the entire recordset initially in order to calculate them.
Yep. But to be clear, I don't expect someone to page through 100K records 10 at a time. If you use MySQL you can get a page at a time using limit, and with others you can at least get a MAX result set.
Hi Raymond, do you have an example of pagination, with number of page, total of pages, with next options ??
So I can tell you how to do total pages and show the current page. Not sure what you mean by 'next options'?
sorry Raymond my english its so bad, yeah i want that: total pages, the current page, "previous page" and "next page".
Thanks :)
Ok, I can do the first two. But my demo already allows you to go to the previous and next page. Are you not seeing that?
yes Raymond i see it, only i want to be more clear.
I'm not quite sure what to tell you then. If you want it more 'visible', then edit the HTML portion. The point of this blog entry was to focus on the basic mechanics of using ColdFusion to do the paging. Design issues are separate.
I do have your other two requests done - will blog it a bit later.
ok, thank you
Posted an update here: http://www.raymondcamden.co...
Ray, thanks for this big code. One question i have again. i have my query but this query is group by like this;
<cfoutput query="qry_Name" group="NameID" startrow="#url.start#" maxrows="#perpage#">
The "next page" presents me back the same rows?
It's got to be the grouping. I'm not sure you can combine the two here.
ok thanks so i look for a other solutions .. Ray thanks for all ideas and for all codes on your website! ;-)
I am new to cf,
what I did is :
<cfif ! IsDefined("URL.start") || #URL.start# eq "">
<cfset from="1">
<cfelse>
<cfset from=#URL.start#>
</cfif>
<cfset to=#from#+5>
<cfset toP="0">
<cfloop from="#from#" to="#to#" index="i">
<cfset toP=#toP#+1>
<cfoutput><a class="paging" id="pages#toP#" href="?start=#i#">#i# |</a></cfoutput>
</cfloop>
<cfset toP=0>
of <span class="maxPages"> </span>
<a id="NextPage" title="Next"> Next</a></span>
</div>
I tried with Jquery .. Code is similar to the code given below.
$(document).ready(function(){
$("#NextPage").click(function(){
$("#pages1").text("6 | ");
///$("#pages1").attr("href","?start=150");
$("#pages2").text("7 | ");
///$("#pages2").attr("href","?start=175");
$("#pages3").text("8 | ");
///$("#pages3").attr("href","?start=200");
$("#pages4").text("9 | ");
///$("#pages4").attr("href","?start=225");
$("#pages5").text("10 | ");
///$("#pages5").attr("href","?start=250");
});
});