Web Biz

    So, you’ve got a large amount of tabulated data to display, so you decided to use paging to improve the page load time and the user experience.  In CF its really easy to implement paging using the power of the cfquery tag along with the cfloop/cfoutput tag with the specified query attribute.  So, you start coding, and you probable do something like this (this is not what I believe to be efficient paging in CF, rather an example of common paging that is not efficient):

^?View Code COLDFUSION

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<cfparam name="url.maxrows" default="25" /> <cfparam name="url.startrow" default="1" /> <cfparam name="url.sort" default="lastname, firstname" /> <cfparam name="url.orderby" default="ASC" />   <cfquery name="allpages" maxrows="#url.maxrows#"> SELECT firstname,lastname,department FROM pages ORDER BY #url.orderby# </cfquery>   <table border="0" width="100%" cellpadding="2" cellspacing="0" class="tableresults"> <tr class="resultgrid"> <th>Name</th> <th>Department</th> </tr> <cfloop query="allpages" startrow="#url.startrow#"> <tr <cfif not currentrow MOD 2>class="altRow"</cfif>> <td>#allpages.lastname#, #allpages.firstname#</td> <td>#allpages.department#</td> </tr> </cfloop> <tr> <td colspan="3" id="recordnav"> <cfif allpages.recordcount GT (url.startrow+url.maxrows-1)> <div style="float:right;padding:0px;"> <a href="#cgi.script_name#?startrow=#url.startrow+url.maxrows#">NEXT ></a> </div> </cfif> <cfif url.startrow NEQ 1> <a href="#cgi.script_name#?startrow=#url.startrow-url.maxrows#">< PREVIOUS</a> </cfif> </td> </tr> </table>

    As ColdFusion developers we have an arsenal of tools that make paging through a recordset very simple and easy to implement.  The question becomes though, is this the most effective and efficient method for paging?  Well, probably not.  Our query is still pulling all the records in the table and returning it to CF.  If our table has hundreds, thousands, or more records this could take a lot more time and processing.  Especially since we really only need 10, 20,50, etc… records at a time.

    A more efficient method of paging is letting the database work for you, that is what its there for!  If you’re using MySQL, then this is really easy and straight-forward.  MySQL provides the limit clause the select statement that gives us the ability to specify both a limit to the number of records being returns, as well as the ability to specify the offset of the records:

^?View Code COLDFUSION

1 2 3 4

SELECT firstname,lastname,department FROM pages ORDER BY #url.sort# #url.dir# LIMIT #url.maxrows#<cfif url.startrow gt 1>, #url.startrow#</cfif>

    As a reference, the syntax of the LIMIT clause in MySQL is:    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    MS SQL makes it a bit more difficult for us (go figure).  MS SQL does provide the ability limit the number of records returned by using the TOP function, but doesn’t give us the ability to specify the offset.  However, this is quite easy accomplish using a subquery:

^?View Code COLDFUSION

1 2 3 4 5

SELECT TOP #url.maxrows# firstname,lastname,department FROM pages WHERE 1=1 <cfif url.startrow gt 1>AND id NOT IN (SELECT TOP #url.startrow# id FROM users ORDER BY #url.sort# #url.dir#)</cfif> ORDER BY #url.sort# #url.dir#

Update: 

    Below is an example what I believe to be efficient paging in ColdFusion:

^?View Code COLDFUSION

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

<cfparam name="request.dsn" default="mydsn"> <cfparam name="url.maxrows" default="25" /> <cfparam name="url.startrow" default="1" /> <cfparam name="url.sort" default="lastname, firstname" /> <cfparam name="url.dir" default="ASC" />   <!--- Here I am just inserting a bunch of test users in the users table <cfloop from="97" to="121" index="i"> <cfquery name="insertusers" datasource="#request.dsn#"> INSERT INTO users VALUES ('John','Doe #chr(i)#'); </cfquery> </cfloop> --->     <!---For MS SQL---> <!--- <cfquery name="users" datasource="#request.dsn#"> SELECT TOP #url.maxrows# firstname,lastname FROM users WHERE 1=1 <cfif url.startrow gt 1>id NOT IN (SELECT TOP #url.startrow# id FROM users ORDER BY #url.sort# #url.dir#)</cfif> ORDER BY #url.sort# #url.dir# </cfquery> --->   <!---For MySQL---> <cfquery name="users" datasource="#request.dsn#"> SELECT firstname,lastname FROM users ORDER BY #url.sort# #url.dir# LIMIT #url.maxrows#<cfif url.startrow gt 1>, #url.startrow#</cfif> </cfquery> <cfdump var="#users#">   <table border="0" width="100%" cellpadding="2" cellspacing="0" class="tableresults"> <tr> <th>First name</th> <th>Last name</th> </tr> <cfloop query="users"> <tr <cfif not users.currentrow MOD 2>class="altRow"</cfif>> <td><cfoutput>#users.lastname#</cfoutput></td> <td><cfoutput>#users.firstname#</cfoutput></td> </tr> </cfloop> <tr> <td colspan="2" id="recordnav"> <cfif users.recordcount gte (url.startrow+url.maxrows-1)> <div style="float:right;padding:0px;"> <a href="<cfoutput>#cgi.script_name#?startrow=#url.startrow+url.maxrows#</cfoutput>">NEXT ></a> </div> </cfif> <cfif url.startrow neq 1> <a href="<cfoutput>#cgi.script_name#?startrow=#url.startrow-url.maxrows#</cfoutput>">< PREVIOUS</a> </cfif> </td> </tr> </table>

    If you have any questions or problems, just leave a comment below….  You can also check out the demo of the code above.