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

About This Tutorial
Author: Anang A Phatak
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5,CFMX
Total Views: 171,083
Submission Date: November 16, 2004
Last Update Date: June 05, 2009
All Tutorials By This Autor: 12
Discuss This Tutorial
  • Hi this tutorial is great. But I need ur help. I want to implement pagination using Flashforms is there any facility regarding this. Do you have any idea whether CF 7.0 have any such in-built facility for pagination. Please help me its really urgent for me to do this thank you

  • All this is fine. How would you break the results into multiple columns on the same page? Ex: records 1-50 in first column; 51-100 records in the second column and so on...

  • I tried the paging tutorial for my application but it doesn't work. I have 45 records so they are supposed to show on 3 pages...first 20, then second 20, and last 5. But all 3 pages look the same. They show lots of records (First 20 records repeated over and over). I don't know whether the tutorial codes conflict with my codes or not, here's the record layout table that i use:


    #artwork.artName#
    Price: #dollarFormat(artwork.price)#
    Sold Out!
    Can anyone help me with this? Thanks

  • this code returns the entire recordset per page? that will not work well after a few thousand rows have to get returned on every page, even though you are only showing some of them, they are in server memory.

  • Naim, I do not know if I have done the right thing by pasting the script here. But I was jut trying to help. I have used drop downs for sortiing by date ranges. You should be able to incorporate this concept in the tutorial above to get the desired effect. Hope it helps. Let me know if you have any trouble.... __________________________________________________________ strHistory = StructNew(); strHistory.1.Field = "Site"; strHistory.1.Display = "Site History"; strHistory.2.Field = "Building"; strHistory.2.Display = "Building History"; strHistory.3.Field = "Space"; strHistory.3.Display = "Space History"; /********************************/ ThisDate = DateFormat(Now(), 'MM/DD/YYYY'); EarliestDate = DateFormat('1-OCT-2004', 'MM/DD/YYYY'); LowerLimitMonth = DateAdd('m',-2, ThisDate); strDates = StructNew();

    if (IsDefined('Form.History')){ LastClicked = Form.History ; /********************************/ if (LastClicked eq strHistory[i]["Field"]) { RBCheck = "Yes"; } else { RBCheck = "No"; } /********************************/ } else { LastClicked = "" ; RBCheck = "No"; }
    View history of changes !
    #strHistory[i]["Display"]#
    Start Date:
    End Date:


    QUERY OUTPUT
    #Replace(Headers, "_", " ", "All")#
    #DateFormat(Values_Var, "MM/DD/YYYY HH:MM:SS")# #NumberFormat(Values_Var, '__,__,___')# #Values_Var#

    No records available

  • Hi what if i wanted the RecordsPerPage = (all the recorders in a month). and i want my pages to tavel from one month recordes to another????

  • Yup, adding the passed queried variable to the query string of the URL did the trick. I just had to rework my code and pages a little bit. Excellent resource. I'm using it in 3 main pages and on every backend admin page now :) Thanks for sharing!

  • Bob, I can think of two ways of doing this. One is appending the form variable to the query string in the url. For example if you are looking for form.first_name, then you would append like this; // this is true only on the first page if (IsDefined('form.first_name')) { var_first_name = form.first_name; } // this is true on the 2nd, 3rd, 4th page & so on ... else { var_first_name = url.first_name; } #page_number# Second way of doing it is storing the form variable in a session, cookie or application variable , and then clearing it when its use is finished. #page_number# if (cgi.script_name neq 'mydirectory/nextpage.cfm') { if (structkeyexists(Session, 'first_name')) { structdelete(session, 'first_name'); } } The only downside to using this is, that OnRequestEnd.cfm will process this block of code every time and check for the first condition. Sorry not to have responded before, I was travelling. Hope this helps and reaches you in time. Anang

  • Is there a way to pass a form value into the href link that displays the next index. My DatSet query is based on a selection from a previous form and when i click the link for the 2nd page the value #form.myvalue# does not get passed and results won't be shown because the query involved in this index is based on #form.myvalue#

  • Hi Jen, Anang, I prefered a different tweak for the same problem Just add the function ceiling Any it was a great help, both. LAG

Advertisement

Sponsored By...
Healing Touch Massage - Dripping Springs, Texas - Deep Tissue Massage and Swedish Massage Services just $39 for a 50 minute massage!