Solved

Loop Within Loop

Posted on 2004-08-25
12
757 Views
Last Modified: 2013-12-24
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="AustralianBridalDirectory">
      select            top 3 *
      from            syState
      order by      StateCode
</cfquery>

<cfquery name="GetCategory" datasource="AustralianBridalDirectory">
      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.
0
Comment
Question by:Plucka
  • 3
  • 3
  • 2
  • +4
12 Comments
 
LVL 17

Expert Comment

by:Tacobell777
ID: 11899144
how about

<cfoutput query="GetState">
     <cfloop query="GetCategory">
           #GetState.StateCode# - #GetCategory.CategoryName#<br>
     </cfloop>
     <br>
</cfoutput>

I believe its a bug..
0
 
LVL 18

Author Comment

by:Plucka
ID: 11899200
Same problem
0
 
LVL 9

Expert Comment

by:shooksm
ID: 11899225
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:jyokum
ID: 11899245
here's a workaround

<cfoutput query="GetState">
      <cfset thisStateCode=GetState.StateCode>
      <cfloop query="GetCategory">
            #thisStateCode# - #GetCategory.CategoryName#<br>
      </cfloop>
      <br>
</cfoutput>
0
 
LVL 12

Expert Comment

by:jyokum
ID: 11899251
oops... yeah, what shooksm said
0
 
LVL 18

Author Comment

by:Plucka
ID: 11899258
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.
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 500 total points
ID: 11899310
Actually, here is another workaround.  You can also do it without setting for another variable.

Try this :

<cfoutput query="GetState">
     <cfloop query="GetCategory">
           #GetState.StateCode[GetState.currentrow]# - #CategoryName#<br>
     </cfloop>
     <br>
</cfoutput>


Regards!
eNTRANCE2002 :-)
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11899430
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
0
 
LVL 9

Expert Comment

by:CFDevHead
ID: 11899532
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="AustralianBridalDirectory">
     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="AustralianBridalDirectory">
     select          top 3 *
     from          vtVotingCategory
</cfquery>
<cfquery name="GetState" datasource="AustralianBridalDirectory">
     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.StateCode>
     <cfloop query="GetCategory">
          #tempStateCode# - #GetCategory.CategoryName#<br>
     </cfloop>
     <cfset tempStateCode=''>
     <br>
</cfoutput>

Just thought Iwould give my two cents.

P.S I hope this made sense
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 11900362
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#" index="i1">
<cfloop from="1" to="#GetCategory.recordCount#" index="i2">
#GetState..tempStateCode[ variables.i1 ]# - #GetCategory.CategoryName[ variables.i2 ]#<br>
</cfloop>
</cfloop>

if this is not the answer, then blame it on the beer ;-))
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 11900442
;-) just read the whole thread - just ignore me... See that people already gave the same answer ;-))
0
 
LVL 18

Author Comment

by:Plucka
ID: 11908527
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.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question