Strange query loop behavior

[mattquantic]

Hi. I have either come accross something strange, or am being stupid. (tired)

The following code created two queries and then loops through them.

It outputs the following text:
test0: GetData.id=1
test1: GetData.id=1 GetAct.contactid=1 fakeid=1
test2: GetData.id=1 GetAct.contactid=1 fakeid=1
test3: GetData.id=1 GetAct.contactid=1 fakeid=1
test4: GetData.id=1 GetAct.contactid=1 fakeid=1
test5: GetData.id=1 GetAct.contactid=1 fakeid=1

test0: GetData.id=2
test1: GetData.id=1 GetAct.contactid=2 fakeid=2
test2: GetData.id=1 GetAct.contactid=2 fakeid=2
test3: GetData.id=1 GetAct.contactid=2 fakeid=2
test4: GetData.id=1 GetAct.contactid=2 fakeid=2
test5: GetData.id=1 GetAct.contactid=2 fakeid=2

The problem I'm having is that lines test1: GetData.id should be the same as test0: GetData.id and the same value as 'fakeid'.

If you have the time, please see for yourself on your local - or if you're a CF wizard, just look at the code and use the server in your head ... )

Thanks if you have the time.

Matt

<cfset Contact = queryNew("id")> 
<cfset Activities = queryNew("id,contactid")> 

<!--- populate contacts --->    
<cfloop from="1" to="2" index="thisItem">
   <cfset queryAddRow(Contact)>            
   <cfset querySetCell(Contact, "id", thisItem)> 
   
   <!--- populate activities ---> 
   <cfloop from="1" to="5" index="thisActiviyItem">
      <cfset queryAddRow(Activities)>             
      <cfset querySetCell(Activities, "id", thisActiviyItem)> 
      <cfset querySetCell(Activities, "contactid", thisItem)>          
   </cfloop>          
</cfloop>

<cfquery name="GetData" dbtype="query">
   select *
   from contact
</cfquery>     

<cfoutput>

   <cfloop query="GetData">
      
      <br><br>test0: GetData.id=#GetData.id#
      <cfset fakeid = GetData.id>
      
      <cfquery name="GetAct" dbtype="query">
         select *
         from Activities
         where contactid = #GetData.id#
      </cfquery>        
      
      <cfloop query="GetAct">
         <br>test#GetAct.currentrow#: GetData.id=#GetData.id# GetAct.contactid=#GetAct.contactid# fakeid=#fakeid#
      </cfloop>      
      
   </cfloop>

</cfoutput>

 

[Sarky78]

it's one of these stupid things with CF. although you have the proper scope for the variable, it has got confused and output the wrong one, within the second query loop. It seems to do this all the time with query loops, and changing the name of the fields doesn;t seem to help either. If you set a temp variable after your "<cfloop query="GetData">" and then use that within the as your output it works. so your code would loop like this:

<cfloop query="GetData">
      <cfset tempID = getData.ID>
      <br><br>test0: GetData.id=#tempID #
      <cfset fakeid = GetData.id>
      
      <cfquery name="GetAct" dbtype="query">
         select *
         from Activities
         where contactid = #tempID #
      </cfquery>        
      
      <cfloop query="GetAct">
         <br>test#GetAct.currentrow#: GetData.id=#tempID# GetAct.contactid=#GetAct.contactid# fakeid=#fakeid#
      </cfloop>      
      
   </cfloop>

Hope this helps!

Tony

 

[mattquantic]

Hi Tony. Thanks for your time.

I agree. (that's what my fakeid bit is)

It is an anoying scope thing. At least I know know about it, and that it does behave consiatantly.

Cheers.

Matt

 

[stevelionbird]

To avoid the scope issues that Tony mentioned, and to avoid temp variables (I just have a thing for them /OCD) I use the following .. works the same as Tony's but with my OCD added.

<cfoutput>

   <cfloop from="1" to="#GetData.recordCount#" index="i">
      
      <br><br>test0: GetData.id=#GetData.id[i]#
      <cfset fakeid = GetData.id[i]>
      
      <cfquery name="GetAct" dbtype="query">
         select *
         from Activities
         where contactid = #GetData.id[i]#
      </cfquery>        
      
      <cfloop query="GetAct">
         <br>test#GetAct.currentrow#: GetData.id=#GetData.id[i]# GetAct.contactid=#GetAct.contactid# fakeid=#fakeid#
      </cfloop>      
      
   </cfloop>

</cfoutput>

-Steve