Loop over the cfquery tag values

1
votes

I have the following query where I loop over a list of values but I am getting an error at the last comma: 

<cfquery datasource="#application.dsn#">
    INSERT INTO #session.tablename# ( #lFields# )
    VALUES (
        <cfloop list="#lFields#" index="kk">
            <cfqueryparam value="#TRIM(sVideoGame['#kk#'])#" cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['#kk#']))#" />,
        </cfloop>
    )                       
</cfquery>

Problem occurs with the last comma. I tried setting up a counter before the cfloop, setting it 0 and incrementing to 1 inside the cfloop. However, I am not sure how to remove the last comma based upon some condition check.

2
coldfusioncfquery

2 Answers

2
votes

In order to keep track of the position, you will need a from/to loop instead of a list loop. Then add a comma after you pass the first query parameter.

For ColdFusion 2016+ it can be done using the "item" and "index" attributes:

...
<cfloop list="#yourListVariable#" item="keyName" index="position">

    <!--- if we've passed the first parameter, add a comma --->
    <cfif position gt 1>,</cfif>

    <cfqueryparam value="#TRIM(sVideoGame[ keyName ])#" 
        cfsqltype="cf_sql_varchar" 
        null="#NOT LEN(sVideoGame[keyName])#" />
</cfloop>
...

CF11 and earlier require a little more work. To simplify the code, I would recommend converting the list to an array:

<cfset keyArray = listToArray(yourListVariable)>
...
<cfloop from="1" to="#arrayLen(keyArray)#" index="position">

    <!--- if we've passed the first parameter, add a comma --->
    <cfif position gt 1>,</cfif>

    <cfqueryparam value="#TRIM(sVideoGame[ keyArray[position] ])#" 
            cfsqltype="cf_sql_varchar" 
            null="#NOT LEN(sVideoGame[ keyArray[position] ])#" />
</cfloop>
...

Side note, I noticed the query uses dynamic table and column names. Be sure those values are NOT user supplied, or the query is vulnerable to sql injection.

0
votes

If your only question is how to deal with the last comma, then you can do it with

<cfset listCount = 1>
<cfloop list="#lFields#" index="kk">
<cfqueryparam value="#TRIM(sVideoGame['#kk#'])#" 
cfsqltype="cf_sql_varchar" null="#NOT LEN(TRIM(sVideoGame['#kk#']))#" />
<cfif listLen(lFields) is not listCount>,</cfif>
<cfset listCount = listCount+1>
</cfloop>