?

Log in

No account? Create an account
coldfusion_mx
..::.:.:. ..: .........: .:::..:::.
January 2010
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

boxing_cat [userpic]
Recordset Pager

Assume that you query a database and you need to output records on your .cfm template. If number of records is greater than the number of records you plan to output on your page, you have to slice you output. There are some issues associated with that process. First, how to escape querying the database every time you you want to output a part of the recordset? Second, how to create a convenient navigation menu to access blocks of records?

This code might solve these two issues. Once you query the dataset you keep this qery object in SESSION variable. The rest is self-explanatory. 



<!-- PART I. PAGER HEADER -->

<!-- Recordset pager custom tag -->


<cfif Not ISDefined("SESSION.qSet")>


<cfquery name="qSet" datasource="Maeep">
select * from attendees
</cfquery>


<cfset SESSION.qSet = qSet>


<cfelse>


<cfset qSet = SESSION.qSet>


</cfif>


<!-- outPage variable holds a coldfusion page name in which you want to output your recordset -->
<cfset outPage = "pager.cfm">


<!-- nRec - number of records in the dataset -->
<cfset n = qSet.recordcount>


<!-- nRecBlock - number of records in a block. This variable limits the number of outputed records on the page -->
<cfset nRecBlock = 25>


<!-- nBlock - total number of blocks in the recordset -->
<cfset nBlock = ceiling(n/nRecBlock)>


<!-- K - maximum number of segments displayed in the navigation bar -->
<cfset k = 10>


<!-- kReal - real number of segments displayed in the navigation bar -->
<cfset kReal = nBlock/k>


<!-- adjustment of kReal if k < 1 -->
<cfif kReal lt 1>
<cfset kReal = 1>
<cfelse>
<cfset kReal = ceiling(kReal)>
</cfif>


<!-- URL parammeter - start segment number-->
<cfparam name = "URL.sBlock" type="numeric" default="1">
<cfset sBlock = URL.sBlock>


<!-- URL parammeter - start query row -->
<cfparam name = "URL.sRow" type="numeric" default="1">
<cfset sRow = URL.sRow>
<cfset eRow = URL.sRow+(nRecBlock-1)>


<!-- We are ready to generate loop -->
<cfoutput>


<!-- Block counter b -->
<cfset b = (sBlock-1)*k+1>


<!-- We have to determine how many blocks in the current segment -->
<cfset s = nBlock-sBlock*k>
<cfif s lt 0>
<cfset k = k +s>
</cfif>


<br>
<br>


<!-- PART II. PAGER NAVIGATION BAR -->


<!-- Previous records if any -->
<cfif (sBlock gt 1)>
<a href="#outPage#?sBlock=#sBlock-1#&sRow=#(sBlock-2)*k*nRecBlock+1#">Previous</a>
</cfif>
<!-- Here we generate blocks -->
<cfloop index="j" from="1" to="#k#" >
|
<a href="#outPage#?sRow=#(b-1)*nRecBlock+1#&sBlock=#sBlock#">
<!-- Looking for the current block to make it bold-->
<cfif URL.sRow is (b-1)*nRecBlock+1>
<b>#b#</b>
<cfelse>
#b#
</cfif>
</a>
|
<cfset b = b+1>
</cfloop>
<!-- Next records if any -->
<cfif (kReal neq 1) and (sBlock lt kReal)>
<a href="#outPage#?sBlock=#sBlock+1#&sRow=#sBlock*k*nRecBlock+1#">Next</a>
</cfif>



<!-- PART III. PAGER TABULAR OUTPUT -->


<table width="640" border="0" cellspacing="2" cellpadding="2">
<cfloop query="qSet" startrow="#sRow#" endrow="#eRow#">
<tr>
<td bgcolor="F8F8F8">Record #qSet.currentrow#</td>
</tr>
</cfloop>
</table>
</cfoutput>
 


Copyright boxing_cat