Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

Step 1: Calculate StartRow and EndRow

This is a simple calculation but something we will want to reuse so I wrapped it up in its own function. The math is pretty self explanatory.

Note: This calculation is for SQLServer where we will start row numbering at one. If you use MySQL you would start numbering at zero.

<cffunction name="calcPageRows" access="public" output="false" returntype="struct">
      <cfargument name="page" type="numeric" required="true">
      <cfargument name="pagesize" type="numeric" required="true">
      <cfset var rowStruct = structNew()>
      <cfset rowStruct.startRow = ( * arguments.pageSize)-(arguments.pageSize - 1)>
      <cfset rowStruct.endRow = ( * arguments.pageSize)>

      <cfreturn rowStruct />

Step 2 & 3: Get The Data From The Database Plus Total Row Count

Now that we know the startrow and endrow we can query the database for the records based on the ordering, any optional filtering and return just the desired range.

For simplicities sake I am going to swap in the stored procedure from my previous blog entry on Stored Proc Paging. This stored proc returns both the desired records plus the total number of matching records, but you could just as easily use two cfquery's to accomplish the same goal.

<cfstoredproc procedure="dbo.getUsers_paged">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.sortCol#">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#arguments.sortDir#">
      <cfprocparam cfsqltype="cf_sql_integer" value="#arguments.startRow#">
      <cfprocparam cfsqltype="cf_sql_integer" value="#arguments.endRow#">
      <cfprocresult name="returnQuery">

Step 4: Format The Return Data For CFGrid

The last operation we need to perform is presenting the data to the cfgrid in a structure it can recognize. I use the term structure intentionally because that is exactly what it expects.

The queryConvertForGrid() function returns a struct with two keys TotalRowCount and query. Its pretty obvious that TotalRowCount key is just the total number of records that exist in the database for the filter criteria and the query is the subset of those records needed for the current page number.

So all we need to do is replicate this structure.

<cffunction name="queryToCFGrid" access="public" output="false" returntype="struct">
      <cfargument name="theQuery" required="true" type="query">
            This function operates on the basis that
            the query contains a column [totalRows] where
            the total number of matching records is noted

      <cfset var returnStruct = structNew()>      
      <cfset returnStruct.totalRowCount = theQuery.totalRows>
      <cfset returnStruct.query = theQuery>
      <cfreturn returnStruct />
1 2 3

Related Blog Postings

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.


Archives Blog Listing

Tag Listing

Learn CF In A Week


Fork me on GitHub