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:
<cfset people = queryNew("name","varchar")>
<cfloop index="x" from="1" to="342">
<cfset queryAddRow(people)>
<cfset querySetCell(people, "name", "Person #x#")>
</cfloop>
<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:
<cfset nth = 6>
<cfloop index="x" from="1" to="#people.recordCount#">
<cfif x mod nth is 0>
<cfoutput>#people.name[x]#<br/></cfoutput>
</cfif>
</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:
<table border="1">
<tr>
<cfloop query="people">
<cfoutput><td>#name#</td></cfoutput>
<cfif currentRow mod nth is 0>
</tr>
<cfif currentRow neq recordCount>
<tr>
</cfif>
</cfif>
</cfloop>
<!--- remainder? --->
<cfif people.recordCount mod nth neq 0>
<cfset padding = nth - (people.recordCount mod nth)>
<cfoutput>#repeatString("<td> </td>", padding)#</cfoutput>
</tr>
</cfif>
</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.
Archived Comments
:) thanks for the help this morning ray, the truth of the MOD part is that i was thoroughly ok using this:
<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
I never knew it was the remainder
@Ray,
Just curious... I like the implementation here, but would the step attribute for the loop tag work also for this situation?
Why not use step?
<cfloop index="x" from="1" to="#people.recordCount#" step="6">
oh... I thought about it... only in the case of an index loop... so this solution is probably better as it works in more situations... never mind :)
-sigh-
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? ;)
sometimes we over think brother, its ok :) jedimaster status for you, and padawon status for me are still there
one other cool thing ive learned through this was the ability to filter and get the results you want through straight SQL. assuming there are no deleted rows that would throw off the results, you could write your query to do this:
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.
What if you were dealing with a ...very... large result set. Would it be possible (and would there be any benefit) to doing this in the actually query code, as opposed to the output code?
Haha ... Tony answered my question already.
@tony - that assumes continuous IDs though. If you delete anything that would break it.
@Chris - yep - you would never do this over a billion records. But for a 'reasonable' size I think it is fine.
yup and thats why i caveat'd that with the assumption of no deleted rows, but i bet you could #temp table that, and assign a new id field to count off of, and use that, right?
Serves me right for looking at your code and not actually _reading_.
In reply to this post, I just posted a SQL approach to this that doesn't rely on the primary ID:
http://www.iknowkungfoo.com...
And then you can take Adrian's method and modify the WHERE clause if you need to start at a specific record (as Ray mentions above with respect to the cfloop approach).
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.)