Thanks for this - I had to do some CF recordset pagination and I had about 60 minutes to put it together. I found this example and was able to implement and customize it almost immediately!
By: Jim Summer
<!---set dsnless conn string --->
<cfset MyConnectionString = "Driver={Microsoft Access Driver
(*.mdb)};Dbq=ten.mdb;DefaultDir=D:\inetpub\data\;Uid=Admin;Pwd=Password;">
<!--- end set dsnless conn string --->
<!--- set page number based on the querystring, if it is not defined we take it for granted the user has just entered and set the default page number to 1 --->
<cfif (isdefined("url.pagenumber"))>
<cfparam name="pagenum"
default="#url.pagenumber#">
<cfelse>
<cfparam name="pagenum"
default="1">
</cfif>
<!--- end set page number --->
<!--- a standard DB query to get the records --->
<cfquery name="contacts"
connectstring="#MyConnectionString#"
dbtype="dynamic">
SELECT ctcID, ctcFname, ctcLname, ctcPhone1, ctcPhone2, ctcPhone3
FROM tblCtcs
WHERE (ctcInfo=Yes)
ORDER BY ctcLname, ctcFname ASC
</cfquery>
<!--- end DB query --->
<!--- here we process the number of records to show per page based on what the user wants
(querystring), if there is no selection made by the user, we set and show 5 records per page --->
<cfif (isdefined("url.nrecords"))>
<cfset MaxRows_contacts="#url.nrecords#">
<cfelse>
<cfset MaxRows_contacts="5">
</cfif>
<!--- end records per page if statements --->
<!--- here we determine the total number of records that exist, then do some multiplication then division to determine the total number of pages that will exist for this query, based on the number of records per page the user decides... clear as mud huh? --->
<cfset StartRow_contacts=Min((pagenum-1)*MaxRows_contacts+1,Max(contacts.RecordCount,1))>
<cfset EndRow_contacts=Min(StartRow_contacts+MaxRows_contacts-1,contacts.RecordCount)>
<cfset TotalPages_contacts=Ceiling(contacts.RecordCount/MaxRows_contacts)>
<!--- end number of records/pages code --->
<!--- here is the beginning of the HTML --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/1999/REC-html401-19991224/loose.dtd">
<html>
<head>
<title>Summer Family Website</title>
<meta http-equiv="expires"
content="-1">
<meta http-equiv="pragma"
CONTENT="no-cache">
<meta name="author" content="James H. Summer,
Jr.">
<meta name="ROBOTS"
content="ALL">
<meta name="description"
content="Jim Summer James Summer Sonny Summer Diane Summer Leena Summer Jake Summer Bolles School Kentwood High
School">
<meta name="KEYWORDS"
content="Jim Summer James Summer Sonny Summer Diane Summer Leena Summer Jake Summer Bolles School Kentwood High
School">
<script language="javascript"
src="j/o.js"></script>
<!--<style type="text/css" media="all">@import
"ncb.css";</style>-->
<script language="javascript"
src="j/rc.js"></script>
<!--- this js is for the drop down box in the html page --->
<script language="JavaScript">
<!--
function slappy() {
document.changy.submit();
}
//-->
</script>
<!--- end drop down box js --->
<link rel="stylesheet"
type="text/css"
media="print"
href="c/print.css" />
</head>
<body>
<div id="logo"><img
src="i/famlogo5.gif"
border="0"
height="72"
width="542" /></div>
<div id="nv">
<!--custom tag for navigation include -->
<cf_leftnav>
<!--/custom tag for nav inc--></div>
<div id="content"><div
id="c"><script
language="javascript"
src="j/d.js"></script></div>
<h1>Contact Information</h1>
<p>Click on the person's name to view their full contact information.</p>
<!--- this drop down box determines the number of records per page to show the user --->
<form name="changy"
action="ctcs.cfm?lite=ctcs<cfif
(isdefined("url.pagenumber"))>&pagenumber=#url.pagenumber#</cfif>"
method="get">
<p>Show me: <select
name="nrecords"
onChange="slappy();">
<cfoutput>
<cfif (isdefined("url.nrecords"))><option
value="#url.nrecords#"> #url.nrecords# Records</option>
<cfelse>
<option value="">Number of Records</option>
</cfif>
</cfoutput>
<option value="1"> 1 Record</option>
<option value="2"> 2 Records</option>
<option value="3"> 3 Records</option>
<option value="4"> 4 Records</option>
<option value="5"> 5 Records</option>
<option value="10">10 Records</option>
<option value="15">15 Records</option>
<option value="20">20 Records</option>
<option value="25">25 Records</option>
</select> per page.</p>
</form>
<!--- end drop down box --->
<!--- here we are showing the user how many pages this recordset consists of, based on their number of records per page choice --->
<cfoutput><p class="normplus">Found
<b>#TotalPages_contacts#</b> pages of records. Now viewing page
<strong>#pagenum#</strong>.</p></cfoutput>
<!--- end showing number of pages --->
<!--- what I am doing here is setting a variable called counter to either 1 or 2 and creating a style for the div based on that variable, it will return div class="userlist1" or div class="userlist2" which displays alternating colored rows. You see if it reaches 3 it is reset to 1 and starts over, never displaying a 3 --->
<cfset counter="1">
<cfoutput query="contacts"
startRow="#StartRow_contacts#"
maxRows="#MaxRows_contacts#">
<cfif counter is "3">
<cfset counter="1">
</cfif>
<!--- end setting the counter now spit out the html with it's unique divs --->
<!--- alternating colored html rows display --->
<div class="userlist#counter#">
<dl>
<dt><a onFocus="blur();"
href="ctcs_full.cfm?lite=ctcs&display=#ctcID#"
title="Click here for #Trim(contacts.ctcFname)#'s
info.">#Trim(contacts.ctcFname)# #Trim(contacts.ctcLname)#</a></dt>
<dd><strong>Main Phone:</strong> #Trim(contacts.ctcPhone1)#</dd>
</dl>
</div>
<cfset counter="#NumberFormat((counter) +
1)#">
</cfoutput>
<!--- end alternating colored html rows display --->
<!--- ok here is a cool part, we determine what page we are on, if there is another page, and if there is a previous page.... the NEXT or BACK buttons are displayed according to if the sections return a true or not --->
<cfoutput>
<cfif (isdefined("pagenum")) AND
((#pagenum#) GT 1) AND ((#pagenum#) LT (#TotalPages_contacts#))>
<p class="centaur">«-<a
onFocus="blur();"
href="ctcs.cfm?lite=ctcs&pagenum=#NumberFormat((pagenum) -1)#<cfif
(isdefined("url.nrecords"))>&nrecords=#url.nrecords#</cfif><cfif
(isdefined("url.pagenumber"))>&pagenumber=#url.pagenumber#</cfif>">Back</a> :: <a
onFocus="blur();" href="ctcs.cfm?lite=ctcs&pagenum=#NumberFormat((pagenum) + 1)#<cfif
(isdefined("url.nrecords"))>&nrecords=#url.nrecords#</cfif><cfif
(isdefined("url.pagenumber"))>&pagenumber=#url.pagenumber#</cfif>">Next</a>-»</p>
</cfif>
<cfif (isdefined("pagenum")) AND
((#pagenum#) EQ 1) AND ((#pagenum#) LT (#TotalPages_contacts#))>
<p class="centaur"><a
onFocus="blur();"
href="ctcs.cfm?lite=ctcs&pagenum=#NumberFormat((pagenum) + 1)#<cfif
(isdefined("url.nrecords"))>&nrecords=#url.nrecords#</cfif><cfif
(isdefined("url.pagenumber"))>&pagenumber=#url.pagenumber#</cfif>">Next</a>-»</p>
</cfif>
<cfif (isdefined("pagenum")) AND ((#pagenum#) GTE 2) AND ((#pagenum#) EQ (#TotalPages_contacts#))>
<p class="centaur">«-<a
onFocus="blur();"
href="ctcs.cfm?lite=ctcs&pagenum=#NumberFormat((pagenum) -1)#<cfif (isdefined("url.nrecords"))>&nrecords=#url.nrecords#</cfif><cfif (isdefined("url.pagenumber"))>&pagenumber=#url.pagenumber#</cfif>">Back</a></p>
</cfif>
</cfoutput>
<!--- end the NEXT and BACK code --->
</div>
<div id="footer"><!--custom tag footer nav inc-->
<cf_footnav>
<!--/custom tag footer nav inc--></div>
</body>
</html>
<!--- flush the db connection below --->
<cfset cfusion_dbconnections_flush()>
About This Tutorial
Author: Jim Summer
Total Views: 70,938
Submission Date: December 09, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 5
Skill Level: Advanced
Platforms Tested: CF5Total Views: 70,938
Submission Date: December 09, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 5
Discuss This Tutorial
-
-
Keep query results as SESSION variable and it will help.
-
This code has the same classic problem. The entire recorset is returned on EVERY page. Have fun when you get 10K + records returned on EVERY page!
Advertisement
