We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Next Previous Paging

andrewstone2004
on
Medium Priority
814 Views
Last Modified: 2013-12-24
Hi all,

I am currently working on a simple search page with a next previous interface and want to limit the amount of numbers in my navigation that are displayed on output. At the moment all the navigation numbers are displayed at the bottom i.e [PREVIOUS] 1 2 3 4 5 6 7 8 9 10 11 12 [NEXT] with the current page being displayed as 1 2 3 [4] 5 6 7. What I am trying to do is limit this so if there are 20 pages of 5 (<cfset RowsPerPage = 5>) being displayed - 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 I want to control how many bottom numbers to display at any one time and for the next link to cycle through them - [PREVIOUS] 1 2 3 4 [5] [NEXT]........... [PREVIOUS] 10 11 12 13 [14] [NEXT] etc etc. My current code is shown below.

Thanks,

Andy


<cfquery name="Search" datasource="myDatasource">
      
SELECT Name
FROM myTable
WHERE 0=0

ORDER BY
tableColumn ASC
      
</cfquery>

<cfset RowsPerPage = 5>

<cfparam name="URL.StartRow" default="1" type="numeric">

<cfparam name="URL.ShowAll" TYPE="boolean" DEFAULT="No">

<cfset TotalRows = Search.RecordCount>

<cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>

<cfset StartRowNext = EndRow + 1>

<cfset StartRowBack = URL.StartRow - RowsPerPage>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<cfloop query="Search" startrow="#URL.StartRow#" endrow="#EndRow#">
<cfoutput>
#Search.Name#<br />
</cfoutput>
</cfloop>


<cfoutput>
<cfif StartRowBack GT 0>
<a href="#CGI.SCRIPT_NAME#?StartRow=#StartRowBack#">[<< BACK]</a>
</cfif>
</cfoutput>

<cfset ThisPage = 1>
<cfloop from="1" to="#TotalRows#" step="#RowsPerPage#" index="PageRow">
<cfset IsCurrentPage = (PageRow GTE URL.StartRow) AND (PageRow LTE EndRow)>
<cfif IsCurrentPage>
<cfoutput>[#ThisPage#]</cfoutput>

<cfelse>
<cfoutput>
<a href="#CGI.SCRIPT_NAME#?StartRow=#PageRow#">#ThisPage#</a>
</cfoutput>  
</cfif>

<cfset ThisPage = ThisPage + 1>
</cfloop>

<cfoutput>
<cfif StartRowNext LTE TotalRows>
<a class="s11-c6-b" href="#CGI.SCRIPT_NAME#?StartRow=#StartRowNext#">[NEXT >>]</a>
</cfif>
</cfoutput>

</body>
</html>
Comment
Watch Question

Commented:
I wrote some code to do this :

First in your page set up something like this:

<!--- Set up defaults so we can display results in Next-N interface --->
      <cfparam name="URL.StartRow" type="numeric" default="1">
      <cfparam name="URL.ShowAll" type="boolean" default="No">
      <cfset TotalRows = myquery.RecordCount>
      <cfif URL.ShowAll>
            <cfset RowsPerPage = TotalRows>
      <cfelse>
            <cfset RowsPerPage = 30>
      </cfif>
      <cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
      <!--- Set up the start row for next and back buttons --->
      <cfset NextStart = EndRow + 1>
      <cfset BackStart = URL.StartRow - RowsPerPage>

Then where you want the interface do something like this :

<cfoutput>
<table border="0" cellpadding="2" cellspacing="0" width="100%" class="description">
<tr>
      <td align="left">
            <cfif TotalRows GT 0>Page</cfif>
            <cfset TotalPages = TotalRows \ RowsPerPage>
            <cfset currentPage = ((StartRow - 1) \ RowsPerPage) + 1>
            <cfif TotalRows mod RowsPerPage NEQ 0><cfset TotalPages = TotalPages + 1></cfif><!--- Add partial last page --->
            <cfif TotalPages GT 20><!--- More than 20 pages of results - determine which links to show --->
                  <cfset StartPage = Max(1, currentPage - 5)><!--- Show links for upto 4 pages before current page --->
                  <cfset EndPage = Min(StartPage + 19, TotalPages)>
            <cfelse>
                  <cfset StartPage = 1>
                  <cfset EndPage = TotalPages>
            </cfif>
            <cfif StartPage GT 1>...</cfif>
            <cfloop index="Pagenum" from="#StartPage#" to="#EndPage#">
                  <!--- If Pagenum is current page then show without link --->
                  <cfif Pagenum EQ currentPage>
                        <b>#Pagenum#</b>
                  <cfelse>
                        <cfset Row = ((Pagenum - 1) * RowsPerPage) + 1>
                        <a href="mypage.cfm?StartRow=#Row#">#Pagenum#</a>
                  </cfif>
            </cfloop>
            <cfif EndPage LT TotalPages>...</cfif>
      </td>
      <td align="right">
            Displaying <b><cfif TotalRows GT 0>#StartRow#<cfelse>0</cfif></b> to <b>#EndRow#</b> of <b>#TotalRows#</b>
      </td>
</tr>
</table>
</cfoutput>

Notice that I add ... so that they know that there are more pages in that direction...

Commented:
Just adjust the numbers to customize to how many results you want displayed.

The above will always show the current page as centered in the range of available pages if there are enough next/previous page numbers for this to work.

Commented:
In my example above I have 20 page links show at a time (because I usually have thousands of records and with 30 records per page that is still a LOT of pages)

Actually it isn't centered either in the exmaple I posted (sorry - forgot which file I grabbed from).  It shows upto 4 pages before the current page and then shows the current page and then the remaining 15 links.

Hope that helps.

Author

Commented:
Thanks for the quick replies guys.

I have combined and slightly modified the code that mrichmon posted and have a few problems/questions:

When the query returns an odd number of records it always takes one off the first page of results - e.g... it will say showing records 1-4 of 133 but only show 3 records on the first page instead of 4.

Also the paging works so that it will always display the current page in the middle.... [BACK] 8 9 10 [11] 12 13 [NEXT] but when I get to the last page..... [BACK] 20 21 [22] 23 24 25 and click on page 25 as in this example it will only display 3 numbers [BACK] 23 24 [25]. Is there any way to always make it display 7 links such as [BACK] 20 21 22 23 24 [25] when I'm on the last page.

My final question is how to get first and last page link functionality into this...[FIRST][BACK] 20 21 [22] 23 24 25 [NEXT][LAST]

Thanks, my current code is posted below.....points increased too

<cfparam name="SESSION.ProjectSearch.Keyword" type="string" default="">
<cfparam name="SESSION.ProjectSearch.Project" type="string" default="">
<cfparam name="SESSION.ProjectSearch.Location" type="string" default="">
<cfparam name="SESSION.ProjectSearch.Filter" type="string" default="">
      
<!--- If user is submitting the form --->
<!--- Make their submission be the filter for rest of session --->
<cfif IsDefined("FORM.Project")>
<cfset SESSION.ProjectSearch.Keyword = FORM.Keyword>
<cfset SESSION.ProjectSearch.Project = FORM.Project>
<cfset SESSION.ProjectSearch.Location = FORM.Location>
<cfset SESSION.ProjectSearch.Filter = FORM.Filter>
</cfif>

<!--- Projects from DB --->
<cfquery name="myquery" datasource="myDatasourceName">
      
SELECT *
FROM tableName
WHERE 0=0
      
<cfif SESSION.ProjectSearch.Filter IS NOT "">
AND Format LIKE '#SESSION.ProjectSearch.Filter#'
</cfif>
      
<!---Filter on from keyword, if it is provided --->
<cfif SESSION.ProjectSearch.Keyword IS NOT "">
AND BookedBy LIKE '#SESSION.ProjectSearch.Keyword#%'

</cfif>
      
<!--- Also filter on project, if it is provided --->
<cfif SESSION.ProjectSearch.Project IS NOT "">
AND OnBehalfOf LIKE '#SESSION.ProjectSearch.Project#%'
</cfif>
      
<!--- Filter again on location, if it is provided --->
<cfif SESSION.ProjectSearch.Location IS NOT "">
AND Content LIKE '#SESSION.ProjectSearch.Location#%'
</cfif>
      
ORDER BY
columnName ASC
      
</cfquery>
      
<cfform action="#CGI.SCRIPT_NAME#" method="post">
<table width="700" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="searchbox" valign="top">
<h3>Project:</h3>
<cfinput style="width: 135px;" name="Project" value="#SESSION.ProjectSearch.Project#"  type="text">
<h3>Location:</h3>
<cfinput style="width: 135px;" name="Location" value="#SESSION.ProjectSearch.Location#" type="text">
<h3>Keyword:</h3>
<cfinput style="width: 135px;" name="Keyword" value="#SESSION.ProjectSearch.Keyword#" type="text">
<h3>Filter:</h3>
<select style="width: 135px; margin-bottom: 20px;" name="Filter">
<option value="">Show All Projects</option>
<option value="option">option</option>
<option value="option">option</option>
<option value="option">option</option>
<option value="option">option</option>
<option value="option">option</option>
</select>
      
<div class="projectsearchbutton">
<input type="image" border="0" src="../images/common/buttons/go_a.gif">
</div>
</td>
</tr>
</table>
</cfform>
      
<!--- Set up defaults so we can display results in Next-N interface --->
<cfparam name="URL.StartRow" type="numeric" default="1">
<cfparam name="URL.ShowAll" type="boolean" default="No">
<cfset TotalRows = myquery.RecordCount>
<cfif URL.ShowAll>
<cfset RowsPerPage = TotalRows>
<cfelse>
<cfset RowsPerPage = 4>
</cfif>
<cfset EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>
<!--- Set up the start row for next and back buttons --->
<cfset NextStart = EndRow + 1>
<cfset BackStart = URL.StartRow - RowsPerPage>

<cfloop query="myquery" startrow="#URL.StartRow#" endrow="#EndRow#">
<cfoutput>
<p>#myquery.columnNameOutput#</p>
</cfoutput>
</cfloop>


<cfoutput>
<table border="0" cellpadding="2" cellspacing="0" width="100%" class="description">
<tr>
<td align="left">
<p>Displaying <b><cfif TotalRows GT 0>#StartRow#<cfelse>0</cfif></b> to <b>#EndRow#</b> of <b>#TotalRows#</b></p>
<cfif TotalRows GT 0>Page</cfif>
<cfset TotalPages = TotalRows \ RowsPerPage>
<cfset currentPage = ((StartRow - 1) \ RowsPerPage) + 1>
<cfif TotalRows mod RowsPerPage NEQ 0><cfset TotalPages = TotalPages + 1></cfif><!--- Add partial last page --->
<cfif TotalPages GT 10><!--- More than 20 pages of results - determine which links to show --->
<cfset StartPage = Max(1, currentPage - 2)><!--- Show links for upto 4 pages before current page --->
<cfset EndPage = Min(StartPage + 4, TotalPages)>
<cfelse>
<cfset StartPage = 1>
<cfset EndPage = TotalPages>
</cfif>
<cfif StartPage GT 1><a href="Paging.cfm?StartRow=#BackStart#">[Back]</a></cfif>
<cfloop index="Pagenum" from="#StartPage#" to="#EndPage#">
<!--- If Pagenum is current page then show without link --->
<cfif Pagenum EQ currentPage>
<b>[#Pagenum#]</b>
<cfelse>
<cfset Row = ((Pagenum - 1) * RowsPerPage) + 1>
<a href="Paging.cfm?StartRow=#Row#">#Pagenum#</a>
</cfif>
</cfloop>
<cfif EndPage LT TotalPages><a href="Paging.cfm?StartRow=#NextStart#">[NEXT]</a></cfif>
</td>
</tr>
</table>
</cfoutput>

Commented:
What you need to do to show 7 records every time is that you need to do a test right after you set StartPage and EndPage.  If EndPage - StartPage is less than 7 then you need to adjust StartPage so that the difference is 7.


As for the first and last links that is easy.

The first link should just always go to page #1

The last link should always go to Page #TotalPages

Commented:
Just a note this would be done by making the first link have StartRow = 1 and the last link having StartRow = (TotalPages-1)*RowsPerPage + 1

(so with 4 records per page and 10 pages then this would be 37 since records 37-40 would be on the last page)

Commented:
As for the only displaying 3 records problem, what happens if you specifically send in StartRow=1 into the URL in that case?

Author

Commented:
It still displays one less than it should on the first page no matter how many rows per page and in StartRow=1 into the URL. Is there any chance of you posting some code up for me to take a look at, I'm fairly new to this kinda stuff in Coldfusion?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
That seems to work fine, must have been my code!

Thanks for all the help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.