• Home
• Tutorials
• Forums
• Reference
• Developers
• Advertise
• About Us

Breaking down your query results into pages (Paging Tutorial)
<!------------------------------------------------------------------------------------------------------------->
I haven't come across a "paging" tutorial on this site. I know there are JavaScripts available that help you achieve this, and the DataSet object in VB.Net comes with paging. All you do is "enable paging". But how do you do it in ColdFusion ?
The following tutorial is a small script that lets you achieve it.

Begin with the usual cfquery, query a database, and retieve a dataset !
<!------------------------------------------------------------------------------------------------------------->
<cfquery name="DataSet" datasource="MyDSN">
    SELECT ColumnName1, ColumnName2, ColumnName3, ColumnName4
    FROM TableName1
</cfquery>
<!------------------------------------------------------------------------------------------------------------->
Set the default "pageindex" parameter equal to 0.
There is an obvious reason to setting this value to 0... you will see it as we procced.
This is the page that will have the first set of records.
<!------------------------------------------------------------------------------------------------------------->
<cfparam name="URL.PageIndex" default="0">
<!------------------------------------------------------------------------------------------------------------->
Now set the number of records you want to display per page. I have set mine to "20". You can let the user decide how many records he/she wants displayed using a user form or a url link.

1. Set "number of records per page"
2. Calculate the "number of pages" you will need.
This can be done by dividing "total records retireved by the query" by "Number of records per page".
Remember to subtract "1" This is because we set "Current Page Index" = 0 .... above
3. Set "StartRow" of records. This is why we set "Page Index = 0" When the page index changes, we can set the
StartRow = (PageIndex X RecordsPerPage) + 1. On the first page this value will be 1
On the second page, Page Index will be 1,
and StartRow value will be (1*20)+1 = 21 (If number of records per page = 20)
4. Set "EndRow" This is easy. StartRow+RecordsPerPage-1
So if your startrow = 21, and Number Of Records per page = 20, EndRow value = 21+20-1 = 40.

<!------------------------------------------------------------------------------------------------------------->
<cfset RecordsPerPage = 20>
<cfset TotalPages = (DataSet.Recordcount/RecordsPerPage)-1>
<cfset StartRow = (URL.PageIndex*RecordsPerPage)+1>
<cfset EndRow = StartRow+RecordsPerPage-1>
<!------------------------------------------------------------------------------------------------------------->
Render your HTML Tables with column headings
<!------------------------------------------------------------------------------------------------------------->
<table border=1>
   <tr>
      <th></th>
      <th>HEADING 1</th>
      <th>HEADING 2</th>
      <th>HEADING 3</th>
      <th>HEADING 4</th>
   </tr>
<!------------------------------------------------------------------------------------------------------------->
Here comes the part we slogged for !
Conceptually, all you want is;
Query.CurrentRow is greater than or equal to StartRow value, then display those records.
\\display records here\\
\\after processing one query loop\\
As long as Query.CurrentRow is less than EndRow, loop thru query ...
If Query.CurrentRow is equal to EndRow, abort loop with "cfbreak" tag.
<!------------------------------------------------------------------------------------------------------------->
<cfoutput>
   <cfloop query="DataSet">
   <cfif CurrentRow gte StartRow >
      <tr>
         <td>#CurrentRow#</td>
         <td>#ColumnName1#</td>
         <td>#ColumnName2#</td>
         <td>#ColumnName3#</td>
         <td>#ColumnName4#</td>
      </tr>
   </cfif>
   <cfif CurrentRow eq EndRow>
      <cfbreak>
   </cfif>
   </cfloop>
</cfoutput>
<!------------------------------------------------------------------------------------------------------------->
Here is the part where you display page number links. Since default page index which is also the page where you display the 1st set of records, is 0 lets loop from 0 to TotalPages value that we discovered above.
<!------------------------------------------------------------------------------------------------------------->
   <tr>
      <td colspan="4">
      <cfloop index="Pages" from="0" to="#TotalPages#">
      <cfoutput>
         |
         <cfset DisplayPgNo = Pages+1>
         <!------------------------------------------------------------------------------------------------------------->
         This is the part where you display links for all PageIndexes other than the one that you are on currently !!
         <!------------------------------------------------------------------------------------------------------------->
         <cfif URL.PageIndex eq pages>
            <strong>#DisplayPgNo#</strong>
         <cfelse>
            <a href="?PageIndex=#Pages#">#DisplayPgNo#</a>
         </cfif>
         |
      </cfoutput>
      </cfloop>
      </td>
   </tr>
</table>
<!------------------------------------------------------------------------------------------------------------->
Simple ?
Let me know if you think of improvements !
<!------------------------------------------------------------------------------------------------------------->