Solved

Next Previous Paging

Posted on 2004-03-30
11
742 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>
0
Comment
Question by:andrewstone2004
  • 7
  • 3
11 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 10713877
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10715701
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...
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10715718
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.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10715768
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.
0
 

Author Comment

by:andrewstone2004
ID: 10721982
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>
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 35

Expert Comment

by:mrichmon
ID: 10724963
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
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10725009
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)
0
 
LVL 35

Expert Comment

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

Author Comment

by:andrewstone2004
ID: 10726723
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?
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 150 total points
ID: 10727406
Well the code that I posted already is directly pulled from a page on my site that works.  SO it is most likely with one of the changes that you made when adapting it to your page.

But here is an actual page that works ...
__________________________________________________________________________________________

<cfquery datasource="#myDSN#" name="GetReservations">
SELECT * FROM mytable
</cfquery>
<!--- 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 = GetReservations.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>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head><title>Show Reservations</title>
<link rel="stylesheet" href="/StyleSheets/main.css" type="text/css">
</head>

<body>
<table border="0" cellpadding="2" cellspacing="0" width="100%" class="tsShadow">
      <tr bgcolor="#D2D4E3" class="description">
            <td style="border-bottom: solid 1px black" align="center"><b>Ceremony</b></td>
            <td style="border-bottom: solid 1px black" nowrap><b>Name</b></td>
            <td style="border-bottom: solid 1px black" nowrap><b>Email</b></td>
            <td style="border-bottom: solid 1px black" align="center" width="100" nowrap><b>Reservation #</b></td>
            <td style="border-bottom: solid 1px black" align="center" width="100"><b>Status</b></td>
      </tr>
      <cfloop query="GetReservations" startrow="#StartRow#" endrow="#EndRow#">
            <cfoutput>
                  <tr bgcolor="##<cfif currentRow mod 2 EQ 1>FFFFFF<cfelse>EBEDFE</cfif>" class="description">
                        <td align="center">#Ceremony# #CeremonyYear#</td>
                        <td>#FirstName# #LastName#</td>
                        <td nowrap><cfif Email NEQ ""><a href="mailto:#Email#">#Email#</a><cfelse>&nbsp;</cfif></td>
                        <td align="center">#NumberFormat(ReservationID, "0000")#</td>
                        <td align="center">
                              <cfswitch expression="#Status#">
                                    <cfcase value="1">
                                          <span class="green">Complete</span>
                                    </cfcase>
                                    <cfcase value="2">
                                          <span class="red">Cancelled</span>
                                    </cfcase>
                              </cfswitch>
                        </td>
                  </tr>
            </cfoutput>
      </cfloop>
</table>
<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="ViewReservations.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> Reservations
                  </td>
            </tr>
      </table>
</cfoutput>

</body>
</html>
0
 

Author Comment

by:andrewstone2004
ID: 10731409
That seems to work fine, must have been my code!

Thanks for all the help.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now