Creating Next-n Records Interfaces

Sooner or later, you will run into a situation in which you need to build what we call a next-n interface. A next-n interface is used in any Web page that lets the user view a portion of a large number of recordssay, 10 or 20 at a time. Such interfaces are common on search-engine Web sites, which might have 1,000 records to look through. Instead of showing you all 1,000 records at once, the page provides buttons or links labeled Next and Back that let you move through them in more reasonable chunks.

Advantages of Next-n Interfaces

This type of interface has a number of advantages:

• Familiarity. Because next-n interfaces are so common, many users expect them whenever they are presented with a large number of records. If they see a ton of records without such an interface, your application might appear unfinished.

• Performance. Next-n interfaces put an upper boundary on the size of the generated HTML, so pages that use them usually are easier on both the browser machine and the ColdFusion server.

• Readability. Most importantly, next-n interfaces usually enable the user to more easily find the information they are looking for: reading a small page is faster than reading a large one.

When to Create a Next-n Interface

It will usually be obvious when you need to add a next-n interface to a particular display page. Other times, though, it becomes evident only over time, as the number of records in the database grows. A year after an application is deployed, what was once a nice, compact data-display page may have ballooned to the point of being slow and unmanageable. So, consider creating some variation on the next-n interface presented in this chapter whenever you think the user might sometime need to look at a large number of records.

TIP

You might come up with an internal policy stipulating that whenever a user will be presented with more than 50 records, a next-n interface should be implemented. You might pick a larger cutoff point if your users have fast connection speeds, or a smaller one if many users will connect via slow modems.

Creating the Basic Interface

Say you have been asked to create a simple expense report area for Orange Whip Studio's intranet. You have only been told to create a page in which employees can review all expenses, but folks in the accounting department say they usually want to see the most recent expenses. You decide to display the expense records in reverse order (the most recent expense first), with a next-10 interface. This way, users can see the new expenses right away, and page through the older records ten at a time.

This section presents four versions of a typical next-n interface, each version a bit more sophisticated than the one before it.

Limiting the Number of Records Shown

A number of approaches can be taken to create a next-n interface. Listing 24.1 demonstrates a simple, effective technique you can easily adapt to your needs.

The code relies on a URL parameter named startRow, which tells the template which records to display. The first time the page is displayed, startRow defaults to 1, which causes rows 110 to be displayed. When the user clicks the Next button, startRow is passed as 11, so rows 1120 are displayed. The user can continue to click Next (or Back) to move through all the records.

NOTE

Before this listing will work, the APPLICATION.dataSource variable needs to be set in your Application.cfc file, as shown in Listing 24.2. Listing 24.3, later in this chapter, also must be in place.

Listing 24.1. NextN1.cfmA Simple Next-n Interface

 <!---   Filename: NextN1.cfm  Created by: Nate Weiss (NMW)  Purpose: Displays Next N record-navigation interface  Please Note Includes NextNIncludeBackNext.cfm template ---> <!--- Retrieve expense records from database ---> <cfquery name="getExp" datasource="#APPLICATION.DataSource#">  SELECT   f.FilmID, f.MovieTitle,   e.Description, e.ExpenseAmount, e.ExpenseDate  FROM   Expenses e INNER JOIN Films f  ON e.FilmID = f.FilmID  ORDER BY   e.ExpenseDate DESC </cfquery> <!--- Number of rows to display per Next/Back page ---> <cfset rowsPerPage = 10> <!--- What row to start at? Assume first by default ---> <cfparam name="URL.startRow" default="1" type="numeric"> <!--- We know the total number of rows from query ---> <cfset totalRows = getExp.recordCount> <!--- Last row is 10 rows past the starting row, or ---> <!--- total number of query rows, whichever is less ---> <cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)> <!--- Next button goes to 1 past current end row ---> <cfset startRowNext = endRow + 1> <!--- Back button goes back N rows from start row ---> <cfset startRowBack = URL.startRow - rowsPerPage> <html> <head><title>Expense Browser</title></head> <body> <cfoutput><h2>#APPLICATION.companyName# Expense Report</h2></cfoutput> <table width="600" border="0" cellSpacing="0" cellPadding="1" cols="3">  <!--- Row at top of table, above column headers --->  <tr>  <td colSpan="2">  <!--- Message about which rows are being displayed --->  <cfoutput>  Displaying <b>#URL.startRow#</b> to <b>#endRow#</b>  of <b>#totalRows#</b> Records<br>  </cfoutput>  </td>  <td></td>  <td align="right">  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </td>  </tr>  <!--- Row for column headers --->  <tr>  <th width="100">Date</th>  <th width="250">Film</th>  <th width="150">Expense</th>  <th width="100">Amount</th>  </tr>    <!--- For each query row that should be shown now --->  <cfloop query="getExp" startRow="#URL.startRow#" endRow="#endRow#">  <cfoutput>  <tr valign="baseline">  <td width="100">#lsDateFormat(ExpenseDate)#</td>  <td width="250">#MovieTitle#</td>  <td width="150"><em>#Description#</em></td>  <td width="100">#lsCurrencyFormat(ExpenseAmount)#</td>  </tr>  </cfoutput>  </cfloop>    <!--- Row at bottom of table, after rows of data --->  <tr>  <td colSpan="4" align="right">  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </td>  </tr> </table> </body> </html> 

NOTE

This listing relies on the startRow and endRow attributes for the <cfloop> tag. See Chapter 9, "CFML Basics," and Appendix B, "ColdFusion Tag Reference," for detailed information about <cfloop>.

First, a query named getExp is run, which retrieves all expense records from the Expenses table, along with the associated MovieTitle for each expense. The records are returned in reverse date order (most recent expenses first). Next, a variable called rowsPerPage is set to the number of rows that should be displayed to the user at one time. You can adjust this value to 20, 50, or whatever you feel is appropriate.

TIP

You could set the rowsPerPage variable in Application.cfc if you wanted to use the same value in a number of different next-n interfaces throughout your application.

The URL.startRow parameter is established via the <cfparam> tag and given a default value of 1 if it isn't supplied in the URL. Then, a totalRows variable is set to the number of rows returned by the getExp query.

TIP

Sometimes it's worth setting a variable just to keep your code clear. In this template, you could skip the <cfset> for the totalRows variable and use getExp.recordCount in its place throughout the rest of the code. But the name of the totalRows variable helps make the role of the value easier to understand, and the extra line of code entails virtually no performance penalty.

Next, a variable called endRow is calculated, which determines the row that should be the last to appear on a given page. In general, the endRow is simply rowsPerPage past the startRow. However, the endRow should never go past the total number of rows in the query, so the min function is used to ensure that the value is never greater than totalRows. This becomes important when the user reaches the last page of search results. The URL.startRow and endRow values are passed to the startRow and endRow attributes of the <cfloop> that displays the expense records, effectively throttling the display so it shows only the appropriate records for the current page.

startRowNext and startRowBack represent what the new startRow value should be if the user clicks the Next or Back link. If the user clicks Next, the page is reloaded at one row past the current endRow. If the user clicks Back, the display moves back by the value stored in rowsPerPage (which is 10 in this example).

After this small set of variables has been calculated, the rest of the template is simple. An HTML table is used to display the expense results. The first row of the table displays a message about which rows are currently being shown. It also displays Next and Back links, as appropriate, by including the NextNIncludeBackNext.cfm template (see Listing 24.3). The next row of the table displays some simple column headings. Then the <cfloop> tag is used to output a table row for each record returned by the getExp query, but only for the rows from URL.startRow tHRough endRow. Finally, the last row of the HTML table repeats the same Next and Back links under the expense records, using an identical <cfinclude> tag.

For now, don't worry about the fact that the query must be rerun each time the user clicks the Next or Back link. ColdFusion's query-caching feature can be used to ensure that your database isn't queried unnecessarily. See Chapter 25, "Improving Performance," for details.

The Application.cfc file shown in Listing 24.2 establishes the APPLICATION.DataSource and APPLICATION.CompanyName variables used in Listing 24.1. Because they are set in the special APPLICATION scope, these variables are available for use within any of this folder's templates, including any custom tags (see Chapter 23, "Building Reusable Components"). This is an excellent way to establish global settings for an application, such as data source names, and is used in most of the Application.cfc templates in the second half of this book. In addition, session management is turned on, which is needed by some of the later examples in this chapter. See Chapter 20, "Working with Sessions," for more information about session management and session variables.

Listing 24.2. Application.cfcProviding Application Settings

 <!---   Filename: Application.cfc  Created by: Raymond Camden ([e-mail])  Please Note Executes for every page request ---> <cfcomponent output="false">   <!--- Name the application. --->   <cfset this.name="OrangeWhipSite">   <!--- Turn on session management. --->   <cfset this.sessionManagement=true>   <cffunction name="onApplicationStart" output="false" returnType="void">     <!--- Any variables set here can be used by all our pages --->     <cfset APPLICATION.dataSource = "ows">     <cfset APPLICATION.companyName = "Orange Whip Studios">   </cffunction> </cfcomponent> 

Adding Next and Back Buttons

Listing 24.3 provides the code that includes the Back and Next links above and below the expense records. The idea is simply to show Back and Next links when appropriate. The Back link should be shown whenever the startRowBack value is greater than 0, which should always be the case unless the user is looking at the first page of records. The Next link should be shown as long as the startRowNext value is not after the last row of the query, which would be the case only when the user is at the last page of records.

Listing 24.3. NextNIncludeBackNext.cfmBack and Next Buttons

 <!---   Filename: NextNIncludeBackNext.cfm  Created by: Nate Weiss (NMW)  Purpose: Displays Back and Next links for record navigation  Please Note Included by the NextN.cfm templates in this folder ---> <!--- Provide Next/Back links ---> <cfoutput>    <!--- Show link for Back, if appropriate --->  <cfif startRowBack gt 0>  <a href="#CGI.script_name#?startRow=#startRowBack#">  <img src="/books/2/448/1/html/2/../images/BrowseBack.gif" width="40" height="16"   alt="Back #rowsPerPage# Records" border="0"></a>  </cfif>    <!--- Show link for Next, if appropriate --->  <cfif startRowNext lte totalRows>  <a href="#CGI.script_name#?startRow=#startRowNext#">  <img src="/books/2/448/1/html/2/../images/BrowseNext.gif" width="40" height="16"   alt="Next #rowsPerPage# Records" border="0"></a>  </cfif> </cfoutput> 

As you can see, the Next and Back links always reload the current page, passing the appropriate startRow parameter in the URL. Now the user can navigate through the all the query's records in digestible groups of 10. Figure 24.1 shows what the results look like in a browser.

Figure 24.1. Creating a simple Next 10 interface for your users is easy.

NOTE

Because the CGI.script_name variable is used for the Back and Next links, this code continues to provide the correct links even if you change the filename for Listing 24.1. If you find this confusing, you could replace the CGI.script_name with the name of the template the user will be accessing (in this case, NextN1.cfm). See Appendix D, "Special ColdFusion Variables and Result Codes," for more information about this handy CGI variable.

Alternating Row Colors for Readability

Listing 24.4 is a revised version of Listing 24.1. This version adds some basic formatting via CSS syntax and presents the rows of data in alternating colors, as shown in Figure 24.2.

Listing 24.4. NextN2.cfmAdding CSS-Based Formatting

 <!---   Filename: NextN2.cfm  Created by: Nate Weiss (NMW)  Purpose: Displays Next N record-navigation interface  Please Note Includes NextNIncludeBackNext.cfm template ---> <!--- Retrieve expense records from database ---> <cfquery name="getExp" datasource="#APPLICATION.DataSource#">  SELECT   f.FilmID, f.MovieTitle,   e.Description, e.ExpenseAmount, e.ExpenseDate  FROM   Expenses e INNER JOIN Films f  ON e.FilmID = f.FilmID  ORDER BY   e.ExpenseDate DESC </cfquery> <!--- Number of rows to display per Next/Back page ---> <cfset rowsPerPage = 10> <!--- What row to start at? Assume first by default ---> <cfparam name="URL.startRow" default="1" type="numeric"> <!--- We know the total number of rows from query ---> <cfset totalRows = getExp.recordCount> <!--- Last row is 10 rows past the starting row, or ---> <!--- total number of query rows, whichever is less ---> <cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)> <!--- Next button goes to 1 past current end row ---> <cfset startRowNext = endRow + 1> <!--- Back button goes back N rows from start row ---> <cfset startRowBack = URL.startRow - rowsPerPage> <html> <head><title>Expense Browser</title></head> <body> <cfoutput><h2>#APPLICATION.companyName# Expense Report</h2></cfoutput> <!--- Simple Style Sheet for formatting ---> <style>  th {font-family:sans-serif;font-size:smaller;  background:navy;color:white}  td {font-family:sans-serif;font-size:smaller}  td.DataA {background:silver;color:black}  td.DataB {background:lightgrey;color:black} </style> <table width="600" border="0" cellSpacing="0" cellPadding="1">  <!--- Row at top of table, above column headers --->  <tr>  <td width="500" colSpan="3">  <!--- Message about which rows are being displayed --->  <cfoutput>  Displaying <b>#URL.startRow#</b> to <b>#endRow#</b>  of <b>#totalRows#</b> Records<br>  </cfoutput>  </td>  <td align="right">  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </td>  </tr>  <!--- Row for column headers --->  <tr>  <th width="100">Date</th>  <th width="250">Film</th>  <th width="150">Expense</th>  <th width="100">Amount</th>  </tr>    <!--- For each query row that should be shown now --->  <cfloop query="getExp" startRow="#URL.startRow#" endRow="#endRow#">  <!--- Use class "DataA" or "DataB" for alternate rows --->  <cfset class = iif(getExp.currentRow mod 2 eq 0, "'DataA'", "'DataB'")>    <cfoutput>  <tr valign="baseline">  <td  width="100">#lsDateFormat(ExpenseDate)#</td>  <td  width="250">#MovieTitle#</td>  <td  width="150"><i>#Description#</i></td>  <td  width="100">#lsCurrencyFormat(ExpenseAmount)#</td>  </tr>  </cfoutput>  </cfloop>    <!--- Row at bottom of table, after rows of data --->  <tr>  <td align="right" colspan="4">  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </td>  </tr> </table> </body> </html> 

Figure 24.2. The background colors of table cells can be alternated to make the display easier to read.

Defining Styles

The <style> block in Listing 24.4 specifies that all <th> cells should be displayed with white lettering on a navy background. Also, two style classes for <td> cells are defined, called DataA and DataB. By displaying alternate rows with these two classes, the expenses are displayed with alternating background colors, as shown in Figure 24.2.

Inside the <cfloop> tag, the code alternates between the DataA and DataB style classes by using ColdFusion's mod operator. The mod operator returns the modulus of two numbers, which is the remainder left over when the first number is divided by the second. When the currentRow is an even number, dividing it by 2 results in a remainder of 0, so the class variable is set to DataA. Otherwise, class is set to DataB. The class variable is then used as the class attribute for the <td> cells that display each row of expenses. The result is the pleasant-looking rendition of the next-n interface shown in Figure 24.2.

TIP

The DataA and DataB style classes could vary in more than just background color. They could use different typefaces, font colors, character formatting, and so on. See a CSS reference for details.

If you don't want to use CSS-based formatting, you could use the iif() test in Listing 24.4 to switch between two color names instead of class names. You would then feed the result to the bgcolor attribute of the <td> tags, instead of the class attribute. This would ensure that the rows displayed with alternating colors, even for browsers that don't support CSS (CSS support appeared in version 4.0 of Internet Explorer and Netscape Communicator). Of course, you could also choose to alternate both the class and bgcolor values.

A Note on the Use of iif()

The line that sets the class attribute uses the iif() function, which enables you to choose between two expressions depending on a condition. The iif() function is comparable to the ? and : operators used in JavaScript and some other languages. The first parameter is the condition; the second determines what the result should be when the condition is true; and the third is what the result should be when condition is False.

When iif() is used to switch between two strings, as shown previously in Listing 24.4, the second and third parameters must have two sets of quotes, because they each will be evaluated as expressions. If the second parameter were written as "DataA" instead of "'DataA'", an error would result because ColdFusion would try to return the value of a variable called DataA, which doesn't exist. The inner set of single quotation marks tells ColdFusion that the literal string DataA should be returned. For details, see iif() in Appendix C, "ColdFusion Function Reference."

The iif() function is used here because it often improves code readability in cases such as this, due to its brevity. If you find it confusing, you can achieve the same result by replacing the single <cfset> line with this:

 <cfif getExp.currentRow mod 2 eq 0>  <cfset class = "DataA"> <cfelse>  <cfset class = "DataB"> </cfif> 

Letting the User Browse Page by Page

Many next-n interfaces that you see on the Web provide numbered page-by-page links in addition to the customary Back and Next links. If there are 50 records to display, and 10 records are shown per page, the user can use links labeled 15 to jump to a particular set of 10 records. This give the user a way to move through the records quickly, and the collection of clickable page numbers serves as a visual cue that provides a sense of how many records there are to look through.

For clarity, the page-by-page links are implemented in a separate file called NextNIncludePageLinks.cfm. Listing 24.5 shows the code for this new file.

Listing 24.5. NextNIncludePageLinks.cfmPage-by-Page Links

 <!---   Filename: NextNIncludePageLinks.cfm  Created by: Nate Weiss (NMW)  Purpose: Displays Page 1, Page 2... links for record navigation  Please Note Included by the NextN.cfm templates in this folder ---> <!--- Simple "Page" counter, starting at first "Page" ---> <cfset thisPage = 1> <!--- Loop thru row numbers, in increments of RowsPerPage ---> <cfloop from="1" to="#totalRows#" step="#rowsPerPage#" index="pageRow">    <!--- Detect whether this "Page" currently being viewed --->  <cfset isCurrentPage = (pageRow gte URL.startRow) and (pageRow lte endRow)>    <!--- If this "Page" is current page, show without link --->  <cfif isCurrentPage>  <cfoutput><b>#thisPage#</b></cfoutput>   <!--- Otherwise, show with link so user can go to page --->   <cfelse>  <cfoutput>  <a href="#CGI.script_name#?startRow=#pageRow#">#thisPage#</a>  </cfoutput>   </cfif>  <!--- Increment ThisPage variable --->  <cfset thisPage = thisPage + 1> </cfloop> 

Like the Back and Next code shown in Listing 24.3, this template is responsible for generating a number of links that reload the current template, passing the appropriate startRow parameter in the URL.

First, a variable named thisPage is set to 1. This variable changes incrementally as each page-by-page link is displayed. Next, a <cfloop> tag is used to create each page-by-page link. Because the step attribute is set to the value of rowsPerPage, the pageRow variable rises in increments of 10 for each iteration of the loop, until it exceeds totalRows. So, the first time through the loop, thisPage and pageRow are both 1. The second time through the loop, thisPage is 2 and pageRow is 11, and so on.

The next <cfset> determines whether the user is already looking at the page of results currently being considered by the loop. If the current value of pageRow is between the startRow and endRow values (see Listing 24.4), isCurrentPage is true. Now the page number can be displayed by outputting the value of thisPage. If thisPage is the page currently being viewed, it is shown in boldface. If not, the page number is presented as a link to the appropriate page by passing the value of pageRow in the URL as the startRow parameter. Now the user can see where they are in the records by looking for the boldface number, and can jump to other pages by clicking the other numbers, as shown in Figure 24.3.

Figure 24.3. For easy navigation, the completed interface includes Back, Next, page- by-page, and Show All links.

Now that the code has been written, it can be included in the next-n interface with a simple <cfinclude> tag:

 <!--- Shortcut links for Pages of search results ---> Page <cfinclude template="NextNIncludePageLinks.cfm"> 

The NextN3.cfm template (Listing 24.6, on this book's CD-ROM) builds on the previous version (see Listing 24.4) by adding this <cfinclude> tag in the appropriate place. The code is otherwise unchanged. You can also see this <cfinclude> tag in the next version of this template (see Listing 24.7).

Adding Show All and Filter Options

Although next-n interfaces are great for keeping your pages from getting too large to navigate comfortably, your users sometimes might need a way to see all the records at once (for instance, when they need to print a hard copy). Therefore, it's worth considering the addition of a Show All link, which essentially serves to override the next-n interface if the user so desires.

Listing 24.7 is the feature-complete version of the next-n interface, which now includes a Show All option as well as the page-by-page navigation included in the previous version. As you can see, only a few lines of new code were necessary to put the Show All option into place.

This version of the template also gives the user a way to filter the records being displayed in the next-n interface. The user is able to filter the records by movie name, the text of the expense description, or by expense date. In general, this is a simple matter of including a Filter form on the page, and then querying the database dynamically based on the user's entries. You saw all this in Chapter 12, "ColdFusion Forms."

The only trick is the fact that this next-n page will be reloaded over and over as the user navigates through the records. The user shouldn't have to keep reentering their filter criteria, so session variables will be used to remember the filter throughout their interaction with the interface. Luckily, ColdFusion's session variables make it easy to provide this basic usability feature. Three session variables are used to remember the filter: SESSION.expenseReport.userFilter, SESSION.expense Report.dateFrom, and SESSION.expenseReport.dateThru.

At the top of the template, <cfparam> tags are used to initialize these variables to empty strings. Next, a simple <cfif> tests whether the user is currently submitting the Filter form. If so, their submission is saved in the three session variables. These session variables are then used in the page's <cfquery> to filter the records according to the user's wishes. The session variables are also used to prepopulate the three <cfinput> tags within the Filter form each time the page is reloaded, giving the user an easy way to experiment with various filters.

Listing 24.7. NextN4.cfmAdding a Way to View All Records at Once

 <!---   Filename: NextN4.cfm  Created by: Nate Weiss (NMW)  Purpose: Displays Next N record-navigation interface  Please Note Includes NextNIncludeBackNext.cfm and NextNIncludePageLinks.cfm ---> <!--- Maintain ExpenseReport filtering variables at session level ---> <cfparam name="SESSION.expenseReport.userFilter" type="string" default=""> <cfparam name="SESSION.expenseReport.dateFrom" type="string" default=""> <cfparam name="SESSION.expenseReport.dateThru" type="string" default=""> <!--- If the user is submitting the "filter" form, ---> <!--- we'll make their submission be the filter for rest of session ---> <cfif isDefined("FORM.userFilter")>  <cfset SESSION.expenseReport.userFilter = FORM.userFilter>  <cfset SESSION.expenseReport.dateFrom = FORM.dateFrom>  <cfset SESSION.expenseReport.dateThru = FORM.dateThru> </cfif> <!--- Retrieve expense records from database ---> <cfquery name="getExp" datasource="#APPLICATION.dataSource#">  SELECT   f.FilmID, f.MovieTitle,   e.Description, e.ExpenseAmount, e.ExpenseDate  FROM   Expenses e INNER JOIN Films f  ON e.FilmID = f.FilmID  WHERE   0=0   <!--- If the user provided a filter string, --->   <!--- show only matching films and/or expenses --->  <cfif SESSION.expenseReport.userFilter is not "">  AND (f.MovieTitle LIKE '%#SESSION.expenseReport.userFilter#%' OR  e.Description LIKE '%#SESSION.expenseReport.userFilter#%')  </cfif>   <!--- Also filter on From date, if provided --->  <cfif isDate(SESSION.expenseReport.dateFrom)>  AND e.ExpenseDate >= #CreateODBCDate(SESSION.expenseReport.dateFrom)#  </cfif>  <!--- Also filter on Through date, if provided --->  <cfif isDate(SESSION.expenseReport.dateThru)>  AND e.ExpenseDate <= #CreateODBCDate(SESSION.expenseReport.dateThru)#  </cfif>  ORDER BY   e.ExpenseDate DESC </cfquery> <!--- Number of rows to display per Next/Back page ---> <cfset rowsPerPage = 10> <!--- What row to start at? Assume first by default ---> <cfparam name="URL.startRow" default="1" type="numeric"> <!--- Allow for Show All parameter in the URL ---> <cfparam name="URL.showAll" type="boolean" default="No"> <!--- We know the total number of rows from query ---> <cfset totalRows = getExp.recordCount> <!--- Show all on page if ShowAll passed in URL ---> <cfif URL.showAll>  <cfset rowsPerPage = totalRows> </cfif> <!--- Last row is 10 rows past the starting row, or ---> <!--- total number of query rows, whichever is less ---> <cfset endRow = min(URL.startRow + rowsPerPage - 1, totalRows)> <!--- Next button goes to 1 past current end row ---> <cfset startRowNext = endRow + 1> <!--- Back button goes back N rows from start row ---> <cfset startRowBack = URL.startRow - rowsPerPage> <!--- Page Title ---> <html> <head><title>Expense Browser</title></head> <body> <cfoutput><h2>#APPLICATION.companyName# Expense Report</h2></cfoutput> <!--- Simple style sheet for formatting ---> <style>  form {font-family:sans-serif;font-size:smaller;}  th {font-family:sans-serif;font-size:smaller;  background:navy;color:white}  td {font-family:sans-serif;font-size:smaller}  td.DataA {background:silver;color:black}  td.DataB {background:lightgrey;color:black} </style> <!--- Simple form to allow user to filter results ---> <cfform action="#CGI.script_name#" method="post">  <!--- Filter string --->  <b>Filter:</b>  <cfinput  type="text"  name="userFilter"  value="#SESSION.expenseReport.userFilter#"  size="15">    <!--- From date --->   &nbsp;  <b>Dates:</b> from  <cfinput  type="text"  name="dateFrom"  value="#SESSION.expenseReport.dateFrom#"  size="9"  validate="date"  message="Please enter a valid date, or leave it blank.">    <!--- Through date --->  through  <cfinput  type="text"  name="dateThru"  value="#SESSION.expenseReport.dateThru#"  size="9"  validate="date"  message="Please enter a valid date, or leave it blank.">     <!--- Submit button to activate/change/clear filter --->   <input   type="submit"  value="Apply">  </cfform> <table width="600" border="0" cellSpacing="0" cellPadding="1">  <!--- Row at top of table, above column headers --->  <tr>  <td width="500" colspan="3">  <!--- Message about which rows are being displayed --->  <cfoutput>  Displaying <b>#URL.startRow#</b> to <b>#endRow#</b>  of <b>#totalRows#</b> Records<br>  </cfoutput>  </td>  <td width="100" align="right">  <cfif not URL.showAll>  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </cfif>  </td>  </tr>  <!--- Row for column headers --->  <tr>  <th width="100">Date</th>  <th width="250">Film</th>  <th width="150">Expense</th>  <th width="100">Amount</th>  </tr>    <!--- For each query row that should be shown now --->  <cfloop query="getExp" startRow="#URL.startRow#" endrow="#endRow#">  <!--- Use class "DataA" or "DataB" for alternate rows --->  <cfset class = iif(getExp.currentRow mod 2 eq 0, "'DataA'", "'DataB'")>    <cfoutput>  <tr valign="baseline">  <td  width="100">#lsDateFormat(ExpenseDate)#</td>  <td  width="250">#MovieTitle#</td>  <td  width="150"><i>#Description#</i></td>  <td  width="100">#lsCurrencyFormat(ExpenseAmount)#</td>  </tr>  </cfoutput>  </cfloop>    <!--- Row at bottom of table, after rows of data --->  <tr>  <td width="500" colSpan="3">  <cfif not URL.showAll and totalRows gt rowsPerPage>  <!--- Shortcut links for "Pages" of search results --->  Page <cfinclude template="NextNIncludePageLinks.cfm">  <!--- Show All link --->  <cfoutput>  <a href="#CGI.script_name#?&showAll=Yes">Show All</a>  </cfoutput>  </cfif>   </td>  <td width="100" align="right">  <cfif not URL.showAll>  <!--- Provide Next/Back links --->  <cfinclude template="NextNIncludeBackNext.cfm">  </cfif>  </td>  </tr> </table> </body> </html> 

Aside from adding the filter form and accompanying session variables, not too much has changed in this version of the template. Near the top, a new URL parameter called showAll is introduced and given a default value of No. Two lines later, a simple <cfif> test is used to set the rowsPerPage variable to the value of totalRows if URL.showAll is true. If the page is accessed with showAll=Yes in the URL, it displays all the records in one large group.

The only other difference is the addition of a few <cfif> tests throughout, so the Back, Next, and page-by-page links aren't shown when in Show All mode. The final results are shown in Figure 24.4.

Figure 24.4. Users can apply filters and move back and next within the filtered results.

NOTE

For now, don't worry about the fact that the query must be rerun each time the user clicks Next, Back, Show All, or one of the numbered page links. ColdFusion's query-caching feature can be used to ensure that your database is not hit too hard. See Chapter 25, "Improving Performance," for details.