Keep Coding, Nathan Stanford President/CEO C.F. Concepts, Inc. www.cftipsplus.com

If you have suggestions for articles send them to us. If you would like to write for cftipsplus.com send us an email to:

admin@cftipsplus.com

IF YOU WANT TO BE AN AUTHOR SEND IN YOUR COLDFUSION TIPS.

Remember this is a great way to get your name known in the ColdFusion Community.

=============================================================== If you have any suggestions please email me at cftips@nsnd.com. ===============================================================

=============================================================== II. ColdFusion In Context: Page Forward and Back By R. Martin Ladner martin.ladner@knology.net

===============================================================

Suppose some of your users have 5000 records that pertain to them and others have only 200. If you want to let them page through their records, you have two problems: they won't all find the same page size appropriate - imagine wading through 5000 records 50 at a time - and you don't want 5000 records returned to ColdFusion's memory just so you can display perhaps 200 of them at a time. The user with only 200 records probably has a dial-up connection and won't thank you for dumping all 200 to the screen at once.

Here's a technique that lets users specify the page size they want and lets them page backward and forward through their records without forcing ColdFusion to manipulate more records than it needs. Along the way, you'll set default parameters and vary those parameters depending on direction of travel and on the data returned by the query. To test it, you can copy the whole thing into a page called "page.cfm", create a dozen or so records, and run the result.

Set Default Parameters You'll need to keep track of a few parameters to make this work: the direction, first item currently displayed, last item currently displayed, and page size. If you're going forwards, you'll want your next starting point to be the last item displayed. If you're going backwards, you'll want your next starting point to be the first item displayed. The page size is set very small in this example so you won't have to enter hundreds of records to see what this code does.

<cfif isDefined("form.Direction")> <cfif form.Direction is "next"> <cfset StartItem="#form.LastItem#"> <cfelse> <cfset StartItem="#form.FirstItem#"> </cfif> <cfset Direction="#form.Direction#"> <cfset PageSize=#form.PageSize#> <cfelse> <cfset StartItem=""> <cfset Direction="next"> <cfset PageSize=3> </cfif>

Get Undisplayed Starting Point if Backing Up If you're backing up, the starting point you need is not on the screen. Therefore, you'll need to perform a query that's not displayed. The "getBack" query returns the "sort" element (the ItemName) from each of the first N rows in descending order. Looping through to the end of this descending query yields the new FirstItem to use as the next starting point when you display the records in ascending order. The "extra" set statement for StartItem is there so StartItem will be defined if no records are returned.

<cfif Direction is not "next"> <cfquery name="getBack" datasource="myDB"> select top #PageSize#, ItemName from item where ItemName < '#StartItem#' order by ItemName desc </cfquery> <cfset StartItem="">

<cfloop query="getBack"> <cfset FirstItem="#ItemName#"> </cfloop>

<cfset StartItem="#FirstItem#">

</cfif>

Query and Display a Page of Records The query and display set FirstItem and LastItem as well as retrieve and display records. The getItems query displays the first N records whose value for ItemName is greater than the starting point. FirstItem is set to the first ItemName in the query; LastItem is set to the last item in the query. The "extra" set statements handle the situation where the query brings back no records.

<cfquery name="getItems" datasource="myDB"> select top #PageSize# * from item where ItemName > '#StartItem#' order by ItemName </cfquery>

<cfset FirstItem=""> <cfset FirstItem="#getItems.ItemName#">

<cfset LastItem="zzzzz"> <cfoutput query="getItems">#ItemName#<br> <cfset LastItem="#ItemName#"> </cfoutput>

Get Navigation Instructions The show form asks the user for the direction of travel and number of records to display. Each time it's submitted, an appropriate page of records is returned. The cfoutput tag is there so that regular form controls can handle ColdFusion variables; it sends the form to the browser as if you had typed it with hard-coded values. The form let's the user select direction and page size and passes FirstItem and LastItem so they will be available at the top of this page when the form is submitted.

<form name="show" action="page.cfm" method="post"> <cfoutput> <select name="Direction"> <cfif Direction is "next"> <option value="next" selected>next <option value="previous">previous <cfelse> <option value="next">next <option value="previous" selected>previous </cfif> </select> <input type="hidden" name="FirstItem" value="#FirstItem#"> <input type="hidden" name="LastItem" value="#LastItem#"> <input type="text" name="PageSize" value="#PageSize#" size="3" maxlength="3"> <input type="submit" name="x" value="Submit"> </cfoutput> </form>

Extend the Idea To make this example complete, add one more "where" statement to the queries so they only include records belonging to the user running the query. You might also tackle the single-row overlap that occurs in this example when you're going backwards. The technique is good enough to use as it stands, but it would be nice to know why it does what it does.

=Marty=

============== Publisher and Creator: Nathan Stanford, admin@cftipsplus.com C.F. Concepts, Inc. http://www.cftipsplus.com ============== Macromedia and ColdFusion are U.S. registered trademarks.

============== Copyright (c) 2000 - 2001 C.F. Concepts, Inc. CFTIPSPLUS.COM and NSND.COM Permission is granted to circulate this publication via MANUAL forwarding by email to friends provided that the text is forwarded in its entirety and no fee is charged. ===============================================================

============== To unsubscribe: unsubscribe-cftips@nsnd.com

To subscribe: subscribe-cftips@nsnd.com ===============================================================

==^================================================================ EASY UNSUBSCRIBE click here: http://topica.com/u/?aVxjC7.aVG5kO Or send an email To: cftips-unsubscribe@topica.com This email was sent to: cftipsplus@tallylist.com

T O P I C A -- Learn More. Surf Less. Newsletters, Tips and Discussions on Topics You Choose. http://www.topica.com/partner/tag01 ==^================================================================