Pagination Links

[IronwoodElectronics]

Hi. How are most pagination links set up? Are they dynamic on the same page that just reloads the content from the database? Or are they linked to multiple pages such as page 1, 2, 3, etc. with the list of products or whatever you would have on your page?

[WolfShade]

The first one.

V/r,

^ _ ^

[IronwoodElectronics]

Thanks.

[IronwoodElectronics]

How do you set this up? I tried the code below, but it only displays the first set of items on the page when it's first loaded. When I click next, it doesn't load the next set of items. What am I doing wrong?

1. <cfparam name="url.page" default="1"> 
2.  
3.  
4. <cfset page_links_shown = 5> 
5.  
6.  
7. <cfset records_per_page = 5> 
8.  
9.  
10. <cfset start_record = url.page * records_per_page - records_per_page> 
11.  
12.  
13. <cfquery name="get_count" datasource="#application.DataSource#"> 
14. SELECT COUNT(id) AS records 
15. FROM pages_names 
16. </cfquery> 
17.  
18.  
19. <cfquery name="get_names" datasource="#application.DataSource#" result="get_data" maxrows="5"> 
20. SELECT names 
21. FROM pages_names 
22. ORDER BY id DESC 
23.  
24.  
25. </cfquery> 
26.  
27.  
28. <cfset total_pages = ceiling(get_count.records / records_per_page)> 
29.  
30.  
31.  
32.  
33.  
34.  
35.  
36.  
37. <cfoutput> 
38.  
39.  
40. <cfloop query="get_names"> 
41. #start_record + currentrow#. #names#<br/> 
42. </cfloop> 
43. <hr> 
44.  
45.  
46. <cfif url.page EQ 1> 
47. Prev Page 
48. <cfelse> 
49. <a href="pages.cfm?page=#url.page-1#">Prev Page</a> 
50. </cfif> 
51. <hr> 
52.  
53.  
54. <cfif url.page * records_per_page LT get_count.records> 
55. <a href="pages.cfm?page=#url.page+1#">Next Page</a> 
56. <cfelse> 
57. Next Page 
58. </cfif> 
59. <hr> 
60.  
61.  
62. <cfparam name="start_page" default="1"> 
63.  
64.  
65. <cfparam name="show_pages" default="#min(page_links_shown,total_pages)#"> 
66.  
67.  
68. <cfif url.page + int(show_pages / 2) - 1 GTE total_pages> 
69. <cfset start_page = total_pages - show_pages + 1> 
70. <cfelseif url.page + 1 GT show_pages> 
71. <cfset start_page = url.page - int(show_pages / 2)> 
72. </cfif> 
73.  
74.  
75. <cfset end_page = start_page + show_pages - 1> 
76.  
77.  
78. <cfloop from="#start_page#" to="#end_page#" index="i"> 
79. <cfif url.page EQ i> 
80. #i# 
81. <cfelse> 
82. <a href="pages.cfm?page=#i#">#i#</a> 
83. </cfif> 
84. </cfloop> 
85. <hr> 
86.  
87.  
88. <a href="pages.cfm?page=1">First Page</a> 
89. <hr> 
90.  
91.  
92. <a href="pages.cfm?page=#total_pages#">Last Page</a> 
93. <hr> 
94.  
95.  
96. </cfoutput>

[WolfShade]

From what I can see, your query is getting the top 5 records on every page load, and the output looks like you're showing the top 5 records on every page load.  You should use a conditional to get records starting at a certain point.

I don't know what flavor of SQL you are using, there are different ways of doing it.  Also, I have no idea how your data is aggregated.  If ID is strictly an integer, and increases automatically (ie, 1,2,3,4,5), then it would be simple:

SELECT top 5 names 
FROM pages_names  
WHERE ID >= {start row} AND ID <= {end row}
ORDER BY id DESC 

Or some such.

HTH,

^ _ ^

[IronwoodElectronics · Accepted solution]

Thanks WolfShade. I finally got this working. Here's my updated working code:

<cfset page_links_shown = 5>

<cfset records_per_page = 5>

<cfset start_record = url.page * records_per_page - records_per_page + 1>

<cfset endrecord=start_record+records_per_page-1>

<cfquery name="get_count" datasource="#application.DataSource#">

SELECT COUNT(id) AS records

FROM pages_names

</cfquery>

<cfquery name="get_names" datasource="#application.DataSource#" result="get_data">

SELECT Press_Date

FROM pages_names

ORDER BY id DESC

</cfquery>

<cfset total_pages = ceiling(get_count.records / records_per_page)>

<cfoutput>

<cfloop query="get_names" startrow="#start_record#" endrow="#endrecord#">

#currentrow#. #Press_Date#<br/>

</cfloop>

<hr>

<cfif url.page EQ 1>

Prev Page

<cfelse>

<a href="pages.cfm?page=#url.page-1#">Prev Page</a>

</cfif>

<hr>

<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>

<hr>

<cfparam name="start_page" default="1">

<cfparam name="show_pages" default="#min(page_links_shown,total_pages)#">

<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>

<cfset end_page = start_page + show_pages - 1>

<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>

<hr>

<a href="pages.cfm?page=1">First Page</a>

<hr>

<a href="pages.cfm?page=#total_pages#">Last Page</a>

<hr>

</cfoutput>

[WolfShade]

Hi, Ironwood,

The problem with this approach is that you are getting EVERY record in the table, every page load, even though you are only displaying 5 of them at a time.

While this isn't a problem if the table holds only 100 records, once you start dealing with tables that have hundreds of thousands, or millions, of records, this is untenable.  Basically a bad idea.  You really should provide the start and end parameters to the query and filter on the database side.

V/r,

^ _ ^

[Dave Watts]

Or, assuming there aren't that many records, run the query once and cache it. Even if it's a small query, it still takes time to run.

Dave Watts, CTO, Fig Leaf Software

[WolfShade]

But, if you cache the query (and assuming that there is a CMS involved), then you should also remember to run <cfobjectcache action="clear" /> after any CMS updates/deletes/inserts, so that the updated/deleted/added information will be freshly queried on the next access of the page.

I can't tell you the number of times I flipped out because I forgot I was caching a query, performed a CMS update on information, and couldn't figure out why the new information wasn't appearing.  **facepalm**  "D'oh!" 

V/r,

^ _ ^