Having some trouble understanding loops

Question

I created this for some reason neither one of the queries are being updated

<cfloop index="i"  from="1" to="#ArrayLen(location)#">

    <cfif location[i] NEQ "" AND #locationID# EQ "" >

        <cfquery Name="UpdateAddActivity" DATASOURCE="#DS#">        
            INSERT INTO tblProjectLocations
            (   
                projectID,
                locationID
            )
            VALUES
            (   
                #ProjectName#,
                #location[i]#
            )
        </cfquery>

    </cfif>

    <cfif location[i] EQ "" AND #locationID# NEQ "" >

        <cfquery Name="UpdateAddActivity" DATASOURCE="#DS#">        
            DELETE FROM tblProjectLocations
            WHERE locationID = #locationID# AND projectID = #ProjectName# 
        </cfquery>              

    </cfif>

</cfloop>

Am I looping correctly? It doesn't seem like to me that the accumulator is ever going to be updated but loops are done this way every place that I've looked.

Answer 1

When in doubt, look at your data.

<cfoutput>

<cfloop index="i"  from="1" to="#ArrayLen(location)#">
 i is #i# <br>

<cfif location[i] NEQ "" AND locationID EQ "" >
 true location is #location[i]# <br>
<cfelse>
false location [i] is is #location[i]# and 
locationid is #locationID# <br>

</cfif>

<cfif location[i] EQ "" AND locationID NEQ "" >
 same as above
</cfif>

</cfloop>
</cfoutput>

Then you'll know why you are not getting the results you expect.

Answer 2

Your cfloop tag is fine - you only need index/from/to attributes for a basic loop.

The index variable is incremented (and the loop re-processed) at the position of the closing tag. Or to put it another way, the body code is executed once for each index value between from and to (inclusive).

For information, you can change the default increment (of 1) by specifying the step attribute (though that obviously doesn't make sense for an array loop).

When your code isn't performing as expected, you can debug it with the dump tag:

<cfloop ... >
    ... 
    <cfdump var=#locationID# abort />
    ...
</cfloop>

The abort attribute will stop processing - the loop will not iterate and the current page content will be returned (it's shorthand for specifying cfabort tag separately.

You can use multiple dumps, and the label attribute to help identify which is which, but obviously if using abort attribute make sure only the last one has it.

As has been mentioned locationID isn't defined in the snippet you've provided, so may be the issue.

Sometimes spaces can cause issues - you may want to use the trim function to ensure you're dealing with empty strings (though blindly wrapping trim functions everywhere is ugly - always try if possible avoid introducing spaces).

Shortcut Array Looping

The from/to loop you've got there is only one type of cfloop - there are others.

Specifically, when you don't need the numeric index, there is a shorthand array loop:

<cfloop index="CurLocation" array=#Location# >
    ...
</cfloop>

Which is equivalent to:

<cfloop index="i" from=1 to=#ArrayLen(Location)# >
    <cfset CurLocation = Location[i] />
    ...
</cfloop>

But without the unused i variable. (If you need the i variable, stick to from/to.)

Note that inside a function you should almost always write index="local.i" and index="local.CurLocation" to ensure the variables are appropriately scoped. This isn't unique to loops - it applies to any tags that create variables. You can also do <cfset var i = 0 /> prior to the loop to do the same thing.

Unrelated Issues

There are a couple of other issues with your code.

Most importantly, the code you're showing is potentially at risk of SQL injection. You should almost never write SQL with bare hashes in, and instead parameterise your queries - using cfqueryparam tag - to solve this. (In the situations where you can't use parameters (e.g. within ORDER BY) make sure you have appropriately sanitized any dynamic text.

Less important -- it doesn't change how the code works, but does betray a lack of experience and understanding -- are the superfluous hashes around locationID. A simplified explanation is that you generally only need #s inside strings (i.e. where the contents would otherwise be treated as text, rather than being a variable's value.)

Answer 3

As long as there are items in your location array, the loop will run and the CFLoop tag will take care of incrementing i.

What I'd guess is happening is that you are checking two conditions within the loop and if neither match, no code will run. You're handling these:

location[i] NEQ "" AND #locationID# EQ ""
location[i] EQ "" AND #locationID# NEQ ""

but not these:

location[i] EQ "" AND #locationID# EQ ""
location[i] NEQ "" AND #locationID# NEQ ""

Could that be it?