Skip to main content
I
IronwoodElectronics
Inspiring
February 1, 2018
Answered

Pagination Links

  • February 1, 2018
  • 1 reply
  • 1876 views

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?

This topic has been closed for replies.
Correct answer IronwoodElectronics

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,

^ _ ^


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>

1 reply

WolfShade
WolfShade
Legend
February 1, 2018

The first one.

V/r,

^ _ ^

I
IronwoodElectronicsAuthor
Inspiring
February 1, 2018

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>
I
IronwoodElectronicsAuthorCorrect answer
Inspiring
February 2, 2018

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,

^ _ ^


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>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices