Outputting records in sequence

[LionelR1]

I have records in my DB that I would like to rotate and display one at a time each time the query is run.  (in sequence, NOT random).

I can't seem to wrap my head around a simple solution for this. Can anyone throw me a bone?

[Steve Sommers · Accepted solution]

Store a counter somewhere, depending on if you want it in sequence at the application scope level across user sessions or at the session scope layer where each user session starts at 1, 2, 3...

Then use modulo arithmetic on the counter and total number of records to pick the record to display.

SESSION Example:

<cfparam name="session.myCounter" default="0" />

<cfquery name="myQuery" ...>

            select someColumnName... from someTable

            ...

            order by someColumnName

</cfquery>

<cfif myQuery.recordCount>

            <cfset variables.myRecordID = session.myCounter % myQuery.recordCount />

            <cfset session.myCounter = session.myCounter + 1 />

            <cfoutput query="myQuery" maxrows="1" startrow="#variables.myRecordID+1#">

                        <!--- +1 because modulo is zero based, CF queries are one based --->

                        #someColumnName#...

            </cfoutput>

</cfif>

[Steve Sommers]

If you change this to the application scope, I would put a cflock block around the myRecordID assignment and the myCounter increment. If you leave it in the session scope it is not needed unless you are using CF7 or earlier.