Go to next record

[red_evolve]

I know that after doing a query, I can use cfoutput to output the results of the query by applying the query attribute like the one below:-

<cfquery name="query1" datasource="mySource">
select * from users
</cfquery>
<cfoutput query="query1">
#username#
</cfoutput>

Is there any way that I could jump to the next row of the results without using cfoutput ? Something like using a cfloop to loop from "1" to query1.RecordCount. And inside the cfloop, I'll do a "next record" thingy to jump to the next row. Is there any tag that does this ?

[WrkrAnt]

1. Here's one way (from CFMX manual, pages 468-469):

<CFQUERY DATASOURCE="your_db" NAME="GetUser_ID">
SELECT username, user_ID FROM users
</CFQUERY>
<cfparam NAME ="MaxRows" default="1">
<cfparam NAME ="StartRow" default="1">
<CFOUTPUT QUERY="GetUser_ID" startrow="#StartRow#" maxrows="#MaxRows#">
#GetUser_ID.currentRow# --- #user_ID# --- #username#<BR>
</CFOUTPUT>
<CFIF (StartRow+MaxRows) LTE GetUser_ID.recordcount>
<a href="your_filename.cfm?StartRow=<cfoutput>#evaluate(StartRow+MaxRows)#</cfoutput>">Next username </a>
</CFIF><br>
<a href="your_filename.cfm?StartRow=1">Reset counter</a>

(too easy, so I decided to fill it out - just copy & paste; change MaxRows to output more than 1 record at a time)

Here's another way (maybe it's more than you need):
1. I assume that each user will have an unique user_ID and as you said, a query to get the records (user_ID, username).

2. Write all the user_IDs into a list :
<cfset user_List = "#ValueList(Getuser_ID.user_ID)#">

3. Check if #user_ID# exists:
<cfif ParameterExists(user_ID)><cfset Position = #ListFind(user_list, user_ID)#><cfelse><cfset Position = #ListFirst(user_list)#</cfif>

4. Check if Recordcount is greater than 1: <cfif ListLen(user_list) GT 1>
<cfif #user_ID# EQ #ListFirst(user_list)#>
<cfset Back_user_ID = #ListLast(user_list)#>
<cfset BackPosition = #ListFind(user_list, Back_user_ID)#>
<Cfset ForwardPosition = #Position# + 1>
<cfset Forward_user_ID = #ListGetAt(user_list, ForwardPosition)#>
<cfelseif #user_ID# EQ #ListLast(user_list)#>
<cfset BackPosition = #Position# - 1>
<cfset Back_user_ID = #ListGetAt(user_list, BackPosition)#>
<cfset ForwardPosition = 1>
<cfset Forward_user_ID = #ListFirst(user_list)#>
<cfelse>
<cfset BackPosition = #Position# - 1>
<cfset Back_user_ID = #ListGetAt(user_list, BackPosition)#>
<cfset ForwardPosition = #Position# + 1>
<cfset Forward_user_ID = #ListGetAt(user_list, ForwardPosition)#>
</cfif>
<cfelse>
<cfset Forward_user_ID = #user_ID#>
<cfset Back_user_ID = #user_ID#>
<Cfset BackPosition = 1>
<cfset ForwardPosition = 1>
</cfif>

5. Now you have the current (from step 3.), back and forward positions.
<a href="your_filename.cfm?&user_ID=#Back_user_ID# Previous record</a>
#queryname.user_ID# ------ #queryname.username#
<a href="your_filename.cfm?&user_ID=#Forward_user_ID# Next record</a>

6. Not sure what you meant by not using cfoutput ... if this doesn't help sorry I wasted your time. I haven't attempted a smoke test so please no screaming - just trying to help during my lunch hour.

[red_evolve]

Hi, thanks for your reply.
I've figured out the solution. But, thanks a lot for the effort.
It'll serve as a good alternative. Thank you again.

[Crash97]

Hi, thanks for your reply.
I've figured out the solution. But, thanks a lot for the effort.
It'll serve as a good alternative. Thank you again.

What was the solution you found? I am currently trying to figure out how to do exactly what you were asking. I need to be able to advance to the next record in the recordset without using <CFOUTPUT> Is there a way to advance to the next record within the recordset within <CFscript>?

[red_evolve]

Okay. I did something like this:-

<cfquery name="query1" datasource="mySource">
select * from users
</cfquery>
<cfset temp = ValueList(query1.username)>

<cfloop from="1" to=#ListLen(temp, ",")# index="i">
  <!-- do something -->
</cfloop>

<!-- or u could access any index in the list [i]temp[/i] provided that the index is bounded within the length of the list -->
<cfoutput>#ListGetAt(temp, 2, ",")#</cfoutput>
<!-- this will print out the second username in the list -->

Hope that helps.

[Crash97]

Thanks! That gave me a good idea of what to do!

[red_evolve]

No problem. ;)