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 !
<!------------------------------------------------------------------------------------------------------------->

All ColdFusion Tutorials By Author: Anang A Phatak
  • Basic Calculator
    This is a tutorial to build a simple calculator for your web pages. You should get it pretty easily as you browse through the code.
    Author: Anang A Phatak
    Views: 20,206
    Posted Date: Tuesday, October 14, 2003
  • An E - Rolodex System
    Quick and easy way to sort and list your contacts by last names, in two really really simple steps. Almost everybody who uses a data-driven website has a database containing some information about the users. I had a database table called "contact" in which user's first name, last name, address, zip, email, phone, fax.... everything was listed. If you do have something like that and wish to draw and group your contacts by say last names, maybe you will find this tutorial useful.
    Author: Anang A Phatak
    Views: 19,257
    Posted Date: Friday, November 7, 2003
  • A plot to plot a line
    I had no work one day due to a worm attack on our servers, thus a plot to plot a line on a graph was hatched in my empty mind. These files show you, how to plot a line using no database, no java, no long wait times for aplet loading, just 3 tools, Loop, table and text.
    Author: Anang A Phatak
    Views: 17,387
    Posted Date: Thursday, May 6, 2004
  • Automatic Form Generator
    This is not a tutorial as such, more like an application that you can put in a directory. It could boring if you use the CF editor, to pick "cfform" fill its attributes, then pick "cfinputs" one by one. fill out those attributes... one by one, then change tabs and pick the "submit" button... so on and so forth. Even if you code in a note pad, it might get lengthy to code individual element. Wouldn't it be nicer to code all these elements at once, then just copy the code and paste it in your editor?
    Author: Anang A Phatak
    Views: 20,506
    Posted Date: Thursday, May 20, 2004
  • A random password generator
    RANDOM PASSWORD GENERATOR SCRIPT ! I know there is a random password tutorial here already. This is just another way to do the same. I think this is a little easier to understand. Refresh it to generate a new password string everytime !
    Author: Anang A Phatak
    Views: 22,822
    Posted Date: Monday, May 24, 2004
  • Advanced Calculator
    I have posted a "Basic Calculator" tutorial here. That was more like a representation of how you would calculate with a paper and a pencil. You provide INPUT A then a MATHEMATICAL OPERATION like a "+" or a "-" and then an INPUT B. This is more a represntation of how you would use a regular hand-held calculator complete with buttons for NUMBERS, OPERATIONS and CLEAR TEXT.
    Author: Anang A Phatak
    Views: 17,869
    Posted Date: Friday, June 18, 2004
  • Automatically Query To CFM
    This is a custom tag application. The cf_QueryRender custom tag takes your query arguments and gives you a final page table and all...
    Author: Anang A Phatak
    Views: 24,169
    Posted Date: Friday, October 29, 2004
  • A Mp3 Streaming Server
    This is a small application that shows you how to create an MP3 streaming server.
    Author: Anang A Phatak
    Views: 25,180
    Posted Date: Monday, November 8, 2004
  • Breaking down your query results into pages (Paging Tutorial)
    I havent 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 ?
    Author: Anang A Phatak
    Views: 29,599
    Posted Date: Tuesday, November 16, 2004
  • A DataSet just like VB.Net
    This tutorial shows you how to create a "dataset" just like the one in VB.Net In VB.Net you would create a dataset with "edit" button in an extra column. Once you click "edit", you get an option to "update", "delete" or "cancel edit mode" This is just like a cfgrid tag. Although a cfgrid tag lets you bulk insert, bulk update or bulk delete, the dataset object does it one by one. But cfgrid is slower, and may give users Java errors, depending on their browser settings.
    Author: Anang A Phatak
    Views: 32,273
    Posted Date: Wednesday, November 17, 2004
  • Dynamic time and date for your pages
    Have you seen the "www.EasyCFM" page closely? On the main page, top right, there is a place for time, and top left a place for day-date. Ever wonder how Pablo does it ? This is not a ColdFusion tutorial. Its JavaScript.
    Author: Anang A Phatak
    Views: 20,249
    Posted Date: Wednesday, January 5, 2005
  • ColdFusion MX 6.1 Installation on Linux (Ubuntu -- Hoary Hedgehog)
    I have tried hoards of websites on how to install coldfusion on Fedora Core 3 with apache webserver. For some reason the connectors always failed. I had "Ubuntu" on my laptop, basically because "acpi" suspend/hibernate actually works. I decided I might try to install CF there to find out what was going wrong. Surprisingly everything worked like a charm. Make sure you use "apt-get install apache2" before you try this. BEST OF LUCK ....
    Author: Anang A Phatak
    Views: 22,648
    Posted Date: Tuesday, May 10, 2005
  • Dynamic textbox and progress bar for your pages
    The principle of this tutorial is similar to "Dynamic time and date for your pages" tutorial. Except that this one generates messages, and that one updated time. Read on, you will get the hang of it....
    Author: Anang A Phatak
    Views: 24,840
    Posted Date: Thursday, May 19, 2005
  • Getting ColdFusion Studio for Linux
    I like using HomeSite+ for windows, and I am getting used to Dreamweaver Mx. But I still need something just as good for Linux. For some reason, I couldnt get "wine and Dreamweaver Mx" to work. So I "Googled" a bit and stumbled upon Eclipse and cfEclipse. Here is how to set it up.
    Author: Anang A Phatak
    Views: 22,885
    Posted Date: Wednesday, May 25, 2005
  • Breaking down your query results into pages (Paging Tutorial) Part-II
    This is an extension to my last tutorial "Breaking down your query results into pages (Paging Tutorial)" which is posted here on www.easycfm.com In the last tutorial, you could retrieve a dataset with a , then use a technique to seperate the results over several pages. It simply ; - took the total "recordCount" - divided that with the "number of records per page" - then displayed the number of pages at the bottom of the table. This is a little more sophisticated than that. Read on...
    Author: Anang A Phatak
    Views: 18,105
    Posted Date: Wednesday, January 11, 2006
Download the EasyCFM.COM Browser Toolbar!