Solved

Loop Within Loop

Posted on 2004-08-25
12
764 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 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