Creating NextPrev Link in Cold Fusion - Calculate the Offset and Limit Rows

(Page 2 of 3 )

Following is the code for splitting the page numbers and finding the offset values for each link. I have written comments for each and every line so you should be able to understand the logic clearly. 


<!--- set how many records you want to display per page ---> 
<cfset Result_Per_Page="10"> 
 
<!--- get the total record count from q_fetch query --->
<cfset Total_Records="#q_fetch.recordcount#"> 
 
<!--- set the default value for the offset record set number ---> 
<cfparam name="URL.offset" default="0"> 
 
<!--- the limit result set(i.e., end row) ---> 
<cfset limit=URL.offset+Result_Per_Page> 
 
<!--- page results start from? ---> 
<cfset start_result=URL.offset+1> 
 
<!--- showing results 1 - 10 of total record --->
<cfoutput>Showing results #start_result# - 
<cfif limit GT Total_Records> #Total_Records# <cfelse> #limit# </cfif> of #Total_Records# 
</cfoutput> 
 
<!--- make sure that the initial start row is starting from 1 ---> 
<cfset URL.offset=URL.offset+1> 
 
<!--- if the record is their more than one page so show the navigation bar ---> 
<cfif Total_Records GT Result_Per_Page> 
<br> 
 
<!--- Create Previous Link ---> 
<cfif URL.offset GT Result_Per_Page> 
<!--- Previous Link Offset ---> 
<cfset prev_link=URL.offset-Result_Per_Page-1> 
<cfoutput><a href="#cgi.script_name#?offset=#prev_link#">PREV</a></cfoutput> 
</cfif> 
 
<!--- Find out how many pages are there for display  ---> 
<cfset Total_Pages=ceiling(Total_Records/Result_Per_Page)> 
 
<!--- now loop it for navigation page numbers ---> 
<cfloop index="i" from="1" to="#Total_Pages#"> 
<cfset j=i-1> 
<!--- create offset value for page numbers ---> 
<cfset offset_value=j*Result_Per_Page> 
 
<!--- deactivate the link if the page number is current page ---> 
<cfif offset_value EQ URL.offset-1 > 
<cfoutput>#i#</cfoutput> 
<cfelse> 
<cfoutput><a href="#cgi.script_name#?offset=#offset_value#">#i#</a></cfoutput> 
</cfif> 
</cfloop> 
 
<!--- create Next Link ---> 
<cfif limit LT Total_Records> 
<!--- Next Link Offset ---> 
<cfset next_link=URL.offset+Result_Per_Page-1> 
<cfoutput><a href="#cgi.script_name#?offset=#next_link#">NEXT</a></cfoutput> 
</cfif> 
</cfif> 


In the above code I used the ceiling function to calculate the Total number of pages. Ceiling function returns the closest integer to the passed value. Also, I used a cgi.script_name variable to pass the current Cold Fusion page name.

Seeing the Results

Now we need to print the query results on the screen.


<!--- display the result on the screen ---> 
<cfloop query="q_fetch" startrow="#URL.offset#" endrow="#limit#"> 
<cfoutput>#id# - #name# - #dept#</cfoutput> <br>
</cfloop> 


<cfloop> tag calls the query 'q_fetch' and limits the query result based on the values passed through offset and limit variables.

Next: Pass More Parameters to Create/Prev Link >>
 

More ColdFusion Articles
More By Shiju Rajan

Comments On: Creating NextPrev Link in Cold Fusion

>>> Be the FIRST to comment on this article!