Solved

Loop Within Loop

Posted on 2004-08-25
12
776 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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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