looping over a query object

[fu-meng]

hi. i have a method with a query. this query returns a series of ids ( strings
). within this same method i want to loop over the query object and populate a
structure. it doesn't seem to be working. here's my query: <cffunction
name='getAggregateInfo' access='remote'> <cfquery name='getAll'
datasource='movieDB'> SELECT movie_id FROM movies </cfquery> <cfoutput
query='getAll'> // here i invoke some other methods and initialize a
structure and populate it with results from the methods </cfoutput> when i
remove the <cfoutput query='getAll'> everything works -- meaning, i can invoke
my other methods and populate my structure -- but obvioulsy i'm only getting
the first result of my query object. what is wrong with the way i'm looping
over my query object? thanks. -- fumeng.

[blewis]

First, just a suggestion: When you are really just looping, not outputing, use
CFLOOP, not CFOUTPUT. It shouldn't make a difference, but reads better.

When you say it doesn't appear to be working, are you getting a specific
error? Can you post the code inside your loop? It may shed some light on the
issue.

[fu-meng]

hi bryan. thanks for responding to my post so quickly -- really appreciate
that. i hear you on using cfloop over cfoutput with a query attribute. the more
i look at it, the more i'm thinking that the loop is correct but the way i'm
populating my structure is not. and the error, well, i'm not getting a specific
error. what's happening is that the results i'm returning back to flash contain
empty data - that's how i know it's not working. here's the code: <cfquery
name='getAll' datasource='moviesDB'> SELECT movie_id FROM movies </cfquery>
<cfoutput query='getAll'> <!--- INVOKE AGGREGATE FUNCTIONS ---> <cfset
visits_total = '#getSessions( getAll.movie_id, '', '#today_fmt#',
'#today_fmt#', 'counter')#'> <cfset time_total = '#getMovieInfo(
getAll.productorial_id, 'totaltime' )#'> <cfset time_view_avg =
'#AverageTimeSpent( getAll.movie_id, '', '#today_fmt#', '#today_fmt#' )#'>
<!--- STRUCTURE ---> <cfscript> inventory = StructNew();
inventory['visits_total'] = '#visits_total#'; inventory['time_total'] =
'#time_total#'; inventory['time_view_avg'] = '#time_view_avg#';
</cfscript> </cfoutput> <cfreturn '#inventory#'> again -- thanks for
taking a look at this, i really appreciate it. best, fumeng.

[blewis]

Well, to aid in debugging, you may want to pull this out of the component for
now and just test each piece. Are you sure that you getSession(),
getMovieInfo(), and AverageTimeSpent() functions are returning data properly?
You may want to check this by simply outputting their values to the screen and
verifying.

You said that you are getting data from your first query object, but actually
the data returned will be from the last. Because it loops over the entrie
query, it will just keep overwriting the values in the struct until it gets to
the end, so you end up with a struct with the last record's data.

Another coding suggestion, kill the #s. You don't need them inside
assignments:

<!--- STRUCTURE --->
<cfscript>
inventory = StructNew();
inventory["visits_total"] = visits_total;
inventory["time_total"] = time_total;
inventory["time_view_avg"] = time_view_avg;
</cfscript>

</cfoutput>

<cfreturn inventory>

[fu-meng]

hi bryan: again, thank you for your help and the coding tips. perhaps it is
obvious that i'm fairly new to coldfusion and don't quite have my bearings yet.
in regards to your question, yes, i am sure that getSessions(),
getMovieInfo(), and AverageTimeSpent() are returning data properly. this is
because i successfully use them in later parts of my application and they are
working just fine. also, the only time i got data returned from my query
object is when i removed my loop. my question now is how do i rewrite my
structure so that it will simply add each movie_id in its own index instead of
overwriting the previous value with each new iteration? thanks muchly. fumeng.

[fu-meng]

hi again. i'm making some progress here but it's still only returning me one
record. can you see where i might be going wrong? here's the code: <cfset
total_records=getAll.recordcount> <cfloop index='counter' from=1
to='#total_records#'> <cfoutput> <!--- INVOKE AGGREGATE FUNCTIONS --->
<cfset visits_total = '#getSessions( getAll.movie_id, '', '#today_fmt#',
'#today_fmt#', 'counter')#'> <cfset time_total = '#getProductorialInfo(
getAll.movie_id, 'totaltime' )#'> <cfset time_view_avg = '#AverageTimeSpent(
getAll.movie_id, '', '#today_fmt#', '#today_fmt#' )#'> <!--- STRUCTURE
---> <cfscript> inventory = StructNew();
inventory[counter]['visits_total'] = visits_total;
inventory[counter]['time_total'] = time_total;
inventory[counter]['time_view_avg'] = time_view_avg; </cfscript>
</cfoutput> </cfloop>

[fu-meng]

solved! a couple of things: i needed to declare the structure before the loop
and set the proper index for each movie_id when i invoke my methods inside the
loop. bryan, thanks for your help and for being a sounding board. i really
appreciate that and i'm psyched whenever i learn something new. here's the
completed complete code: <!--- VARIABLES ---> <cfset today = Now()> <cfset
today_fmt = DateFormat( today, 'yyyy-mm-dd' )> <cfset inventory = StructNew()>
<cfquery name='getAll' datasource='movieDB'> SELECT movie_id FROM
movieDB </cfquery> <cfset total_records=getAll.recordcount> <cfloop
index='counter' from=1 to='#total_records#'> <cfoutput> <!--- INVOKE
AGGREGATE FUNCTIONS ---> <cfset visits_total = '#getSessions(
getAll.movie_id[counter], '', '#today_fmt#', '#today_fmt#', 'counter')#'>
<cfset time_total = '#getProductorialInfo( getAll.movie_id[counter],
'totaltime' )#'> <cfset time_view_avg = '#AverageTimeSpent(
getAll.movie_id[counter], '', '#today_fmt#', '#today_fmt#' )#'> <!---
STRUCTURE ---> <cfscript> inventory[counter]['visits_total'] =
visits_total; inventory[counter]['time_total'] = time_total;
inventory[counter]['time_view_avg'] = time_view_avg; </cfscript>
</cfoutput> </cfloop> <cfreturn inventory>