With a little side of applesauce...

Monday, March 22, 2010

Coldfusion - access specific row in query object

I ran into an issue where:

<cfloop query="myquery">


was throwing a CFQUERY timeout. When I looked at the problem, I realized that CFLOOP must be holding open the query object, and then Coldfusion would throw the CFQUERY timeout error. To get around this, I decided to use CFLOOP, and then just iterate through the query object by referencing the rows. Here is how you do that:

<cfquery datasource="#Application.dsn#" name="notstudents">
select *
from people
where usertype != 'student'
</cfquery>

<cfloop from="1" to="#notstudents.Recordcount#" index="id">
<!--- grab all associated categories --->
<cfquery datasource="#Application.dsn#" name="categories">
select category
from categories
where userid = '#notstudents [ "myid" ][ id ]#'
</cfquery>
...
</cfloop>


"myid" is the field that I want to reference in the notstudents query, and "id" is the row. I like the array notation, though it was really Ben Nadel who helped me get to this point:
http://www.bennadel.com/blog/610-Nylon-Technology-Presentation-ColdFusion-Query-Object-Primer.htm

2 comments:

cliff.whitworth said...

Does Ben Nadel have a pic of you with him?

Shannon Eric Peevey said...

lol, Cliff. Not yet, he doesn't...