Ask a Jedi: Showing Every Nth Record

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>[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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA