Highlighted

Pagination Links

Community Beginner ,
Feb 01, 2018

Copy link to clipboard

Copied

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?

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>

Views

401

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Pagination Links

Community Beginner ,
Feb 01, 2018

Copy link to clipboard

Copied

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?

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>

Views

402

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 01, 2018 0
LEGEND ,
Feb 01, 2018

Copy link to clipboard

Copied

The first one.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 01, 2018 0
Community Beginner ,
Feb 01, 2018

Copy link to clipboard

Copied

Thanks.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 01, 2018 0
Community Beginner ,
Feb 01, 2018

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 01, 2018 0
LEGEND ,
Feb 01, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 01, 2018 0
Community Beginner ,
Feb 02, 2018

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2018 0
LEGEND ,
Feb 02, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2018 1
Adobe Community Professional ,
Feb 02, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2018 0
LEGEND ,
Feb 02, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 02, 2018 0