Loop Within Loop
One of my developers told me there is a bug with Coldfusion with loops within loops when looping on querys. In disbelief I tested it and he is correct. Here is a simple example.
I'm just pulling out 3 states and 3 categories, and want to loop through each category for each state, but the state never changes. I can access the changed state within the outer loop but not within the inner loop.
<cfquery name="GetState" datasource="AustralianBrid
select top 3 *
from syState
order by StateCode
</cfquery>
<cfquery name="GetCategory" datasource="AustralianBrid
select top 3 *
from vtVotingCategory
</cfquery>
<cfoutput query="GetState">
<cfloop query="GetCategory">
#GetState.StateCode# - #CategoryName#<br>
</cfloop>
<br>
</cfoutput>
Here's the output.
ACT - Accessories, Lingerie & Shoes
ACT - Bomboniere
ACT - Bridal Clothing
ACT - Accessories, Lingerie & Shoes
ACT - Bomboniere
ACT - Bridal Clothing
ACT - Accessories, Lingerie & Shoes
ACT - Bomboniere
ACT - Bridal Clothing
I want to know if this is a known bug / feature? And how you guys handle this, I know I could do this in one query, but I want to know how when needed I loop over two queries within each other.
I'm just pulling out 3 states and 3 categories, and want to loop through each category for each state, but the state never changes. I can access the changed state within the outer loop but not within the inner loop.
<cfquery name="GetState" datasource="AustralianBrid
select top 3 *
from syState
order by StateCode
</cfquery>
<cfquery name="GetCategory" datasource="AustralianBrid
select top 3 *
from vtVotingCategory
</cfquery>
<cfoutput query="GetState">
<cfloop query="GetCategory">
#GetState.StateCode# - #CategoryName#<br>
</cfloop>
<br>
</cfoutput>
Here's the output.
ACT - Accessories, Lingerie & Shoes
ACT - Bomboniere
ACT - Bridal Clothing
ACT - Accessories, Lingerie & Shoes
ACT - Bomboniere
ACT - Bridal Clothing
ACT - Accessories, Lingerie & Shoes
ACT - Bomboniere
ACT - Bridal Clothing
I want to know if this is a known bug / feature? And how you guys handle this, I know I could do this in one query, but I want to know how when needed I loop over two queries within each other.
ASKER
Same problem
Known bug. We train our developers to set variables to hold the outside loop data:
<cfoutput query="GetState">
<cfset VARIABLES.StateCode = GetSate.StateCode>
<cfloop query="GetCategory">
#VARIABLES.StateCode# - #CategoryName#<br>
</cfloop>
<br>
</cfoutput>
The other option is to use the queries as arrays and make sure you use a unique index for each loop.
<cfoutput query="GetState">
<cfset VARIABLES.StateCode = GetSate.StateCode>
<cfloop query="GetCategory">
#VARIABLES.StateCode# - #CategoryName#<br>
</cfloop>
<br>
</cfoutput>
The other option is to use the queries as arrays and make sure you use a unique index for each loop.
here's a workaround
<cfoutput query="GetState">
<cfset thisStateCode=GetState.Sta
<cfloop query="GetCategory">
#thisStateCode# - #GetCategory.CategoryName#
</cfloop>
<br>
</cfoutput>
<cfoutput query="GetState">
<cfset thisStateCode=GetState.Sta
<cfloop query="GetCategory">
#thisStateCode# - #GetCategory.CategoryName#
</cfloop>
<br>
</cfoutput>
oops... yeah, what shooksm said
ASKER
Yeah,
That's what we currently do.
Don't quite understand why there is an issue though, doing the same thing on an index loop works fine.
That's what we currently do.
Don't quite understand why there is an issue though, doing the same thing on an index loop works fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Plucka,
many many many thanks for bringing this on desk. I had same problem two weeks ago and was lost like anything. We end up doing some SQL things and DBA handled things differently and gave one column which have things to look up. I was just denying that I am doing something wrong and my team was just speachless.
I thank you shooksm, jyokum and enterance also for providing this workaround. I will have to save this thread for future use.
Thank you all.
Regards,
---Pinal
many many many thanks for bringing this on desk. I had same problem two weeks ago and was lost like anything. We end up doing some SQL things and DBA handled things differently and gave one column which have things to look up. I was just denying that I am doing something wrong and my team was just speachless.
I thank you shooksm, jyokum and enterance also for providing this workaround. I will have to save this thread for future use.
Thank you all.
Regards,
---Pinal
I don't belive this is a BUG. If you think about it makes sense example if you write a query
<cfquery name="GetState" datasource="AustralianBrid
select top 3 *
from syState
order by StateCode
</cfquery>
and then reference a field from the query like so
<cfoutput>
#GetState.StateCode#
<cfoutput>
you will get the first record.
so if you write this
<cfquery name="GetCategory" datasource="AustralianBrid
select top 3 *
from vtVotingCategory
</cfquery>
<cfquery name="GetState" datasource="AustralianBrid
select top 3 *
from syState
order by StateCode
</cfquery>
<cfoutput query="GetState">
<cfloop query="GetCategory">
#GetState.StateCode# - #CategoryName#<br>
</cfloop>
<br>
</cfoutput>
you should get the same record everytime when calling the field like this GetState.StateCode to keep CF Consistent
so te correct way to do what you want to do. Should be something like this
<cfoutput query="GetState">
<cfset tempStateCode=GetState.Sta
<cfloop query="GetCategory">
#tempStateCode# - #GetCategory.CategoryName#
</cfloop>
<cfset tempStateCode=''>
<br>
</cfoutput>
Just thought Iwould give my two cents.
P.S I hope this made sense
<cfquery name="GetState" datasource="AustralianBrid
select top 3 *
from syState
order by StateCode
</cfquery>
and then reference a field from the query like so
<cfoutput>
#GetState.StateCode#
<cfoutput>
you will get the first record.
so if you write this
<cfquery name="GetCategory" datasource="AustralianBrid
select top 3 *
from vtVotingCategory
</cfquery>
<cfquery name="GetState" datasource="AustralianBrid
select top 3 *
from syState
order by StateCode
</cfquery>
<cfoutput query="GetState">
<cfloop query="GetCategory">
#GetState.StateCode# - #CategoryName#<br>
</cfloop>
<br>
</cfoutput>
you should get the same record everytime when calling the field like this GetState.StateCode to keep CF Consistent
so te correct way to do what you want to do. Should be something like this
<cfoutput query="GetState">
<cfset tempStateCode=GetState.Sta
<cfloop query="GetCategory">
#tempStateCode# - #GetCategory.CategoryName#
</cfloop>
<cfset tempStateCode=''>
<br>
</cfoutput>
Just thought Iwould give my two cents.
P.S I hope this made sense
I think I know the answer, I believe I implemented this (but not sure anymore ;-)
You need to use the index, i.e.
<cfloop from="1" to="#GetState.recordCount#
<cfloop from="1" to="#GetCategory.recordCou
#GetState..tempStateCode[ variables.i1 ]# - #GetCategory.CategoryName[
</cfloop>
</cfloop>
if this is not the answer, then blame it on the beer ;-))
You need to use the index, i.e.
<cfloop from="1" to="#GetState.recordCount#
<cfloop from="1" to="#GetCategory.recordCou
#GetState..tempStateCode[ variables.i1 ]# - #GetCategory.CategoryName[
</cfloop>
</cfloop>
if this is not the answer, then blame it on the beer ;-))
;-) just read the whole thread - just ignore me... See that people already gave the same answer ;-))
ASKER
I think it is a bug, as it works differently to other types of loops.
I think the solution posted by entrance is very simple, clean and nice. This is the method we will be using.
I think the solution posted by entrance is very simple, clean and nice. This is the method we will be using.
<cfoutput query="GetState">
<cfloop query="GetCategory">
#GetState.StateCode# - #GetCategory.CategoryName#
</cfloop>
<br>
</cfoutput>
I believe its a bug..