ColdFusion In Context: Page Forward and Back

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 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 lets 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=