Monday, October 16, 2006

CFOUTPUT: Query within A Query

I know that 100% of the people who read this blog regularly have absolutely no interest in this post at all. The reason I am writing is because I think that this needs to be a queryable question in Google since it took me quite a while to figure out how to do it.

So on with the explanation for those who come here one day via Google...

I was using a CFMAIL tag to send a query driven data set to an end user in a report at work. The dataset was a list of requisitions. I had an additional dataset that needed to be in the post as well. I needed to include Notes about the delivery within the email detailing the requisitions.

Coldfusion kept throwing an error because I had a query driven <cfoutput> inside of an existing query driven <cfoutput> since <cfmail> is functionally a query driven <cfoutput> I had used the group function to ensure the data would be output only once, but still the coldfusion server did not like the request.

In order to output my data I would need to use a <cfloop> and output a specific row from a query on each loop through the data. My problem was that documentation for displaying a specific query row was nonexistent in the LiveDocs and negligible on the web. I did find some old posts but their instructions did not work in Coldfusion 7.

So after trial and error using the limited old sources I had, I discovered the correct syntax was #queryname.querycolumn[row]#. I am sure this is not news at all to a more experienced programmer but it was HUGE for me since it was not well documented.

In the end my query driven output within a query driven output looked like this:

<cfif #queryname.recordcount# gt 0>
<cfset currentrecord=" 1">
<cfloop index="currentrecord" from="1" to="#queryname.recordcount#">
<cfset currentrecord= #currentrecord# + 1>

I hope this helps save you the time I spent!


Susie said...

WOW! Thanks. I was just getting ready to do that very thing and this sure made it all make sense to me.

Randy said...

All you had to do is IM me...

I use the syntax all the time...

Kevin J. Bowman said...

I try not to IM you until I have exhausted attempting to figure it out for myself. I did figure it out.... It just took me a while. It does SUCK that it is so poorly documented!