Posted in ColdFusion | Posted on 02-20-2009 | 4,209 views
Tony "I Suck at Math" Weeg asked me this over IM and I thought it would be fun to share. (And I know Tony, so don't think I'm giving him too much crap - my math is pretty rusty as well!) Basically, he had a large query (cue that's what she said joke) and wanted to show every nth row. This can be done easily enough using a simple MOD operation. The MOD operation does division on two numbers and returns the remainder. So if you want to show every 5 rows, you want to know when: Current Row divided by 5 has a remainder of 0. Here is a quick example:
2<cfloop index="x" from="1" to="342">
3 <cfset queryAddRow(people)>
4 <cfset querySetCell(people, "name", "Person #x#")>
5</cfloop>
6
7<cfdump var="#people#" top="10">
This creates my fake query with 342 rows. I did a dump just to be sure it was that big.
I made my 'every N' logic dynamic using a variable:
2<cfloop index="x" from="1" to="#people.recordCount#">
3 <cfif x mod nth is 0>
4 <cfoutput>#people.name[x]#<br/></cfoutput>
5 </cfif>
6</cfloop>
And that's it. I've used code like this before with tables where you want to show N people per row. In general it is pretty easy, but you normally want to have a 'complete' last row. This involves recognizing where you ended and adding some blank cells to finish the table.
As an example:
2 <tr>
3 <cfloop query="people">
4 <cfoutput><td>#name#</td></cfoutput>
5 <cfif currentRow mod nth is 0>
6 </tr>
7 <cfif currentRow neq recordCount>
8 <tr>
9 </cfif>
10 </cfif>
11 </cfloop>
12 <!--- remainder? --->
13 <cfif people.recordCount mod nth neq 0>
14 <cfset padding = nth - (people.recordCount mod nth)>
15 <cfoutput>#repeatString("<td> </td>", padding)#</cfoutput>
16 </tr>
17 </cfif>
18</table>
After each cell I see if I'm at the end of my row, and if so, add a closing tr. If I'm not totally done, start a new row. After the loop I see if I had a remainder and if so, padd the table with some empty cells and a closing tr.
<cfif i mod 2>
</cfif>
and showing every other row. the problem i had was how to show every 5th row... and once ray clued me into the necessity of adding "eq 0" to the mix, all was well, thanks again ray!!
so the final version ended up as:
<cfif ((i mod 5) = 0)>
</cfif>
because as ray explained, mod basically means, gimme the remainder
Just curious... I like the implementation here, but would the step attribute for the loop tag work also for this situation?
<cfloop index="x" from="1" to="#people.recordCount#" step="6">
Timothy and Chris schooled us Tony. ;)
Although you have to modify your FROM:
<cfloop index="x" from="#nth#" to="#people.recordCount#" step="#nth#">
<cfoutput>#people.name[x]#<br/></cfoutput>
</cfloop>
If you don't start with Nth, then your values are off. So instead of 5,10,15 for counting by 5s, you get 1,6, 11.
Have I lost the respect of the CF world now? ;)
select *
from lcdsmetrics
where dateEntered between '02/19/2009' and '02/20/2009'
and (id % 5 = 0)
order by dateEntered
which would give you essentially the same thing as step="5" would but it would bring less records back from the server therefore decreasing the payload from the outset.
@Chris - yep - you would never do this over a billion records. But for a 'reasonable' size I think it is fine.
http://www.iknowkungfoo.com/blog/index.cfm/2009/2/...
To get every N records starting with record X, just replace 5 with N and 0 with X. (Assuming X < N; for X = N, use 0 for X as Adrian did in the example. If X > N, then you'll miss at least one record you wanted.)
[Add Comment] [Subscribe to Comments]