Charles Charles - 4 years ago 89
MySQL Question

Coldfusion loop over insert query values

I am trying to loop over an insert query. I'm looping over the values to the return results are within a list.

<cfquery datasource="#OLMSdatasourceWrite#" result="myResult">
INSERT INTO OLMS_Data_RatioScenarios
(
OLMS_Account_ID,
OLMS_RatioScenario_Name
)
VALUES
(
<cfloop list="#AccountListWithSettings#" index="CurrentAccount">
(<cfqueryparam cfsqltype="cf_sql_numeric" value="#CurrentAccount#" maxlength="255">, <cfqueryparam cfsqltype="cf_sql_clob" value="#requestBody.value#" maxlength="255">)
<cfif CurrentAccount GT 1>
,
</cfif>
</cfloop>
)
</cfquery>

<cfoutput>Inserted ID is: #myResult.generatedkey#</cfoutput>


The problem im running into here is that it keeps putting a "," after the final iteration

VALUES ( ( (param 1) , (param 2) ) , ( (param 3) , (param 4) ) , ( (param 5) , (param 6) ) , )


I need help figureing out the cfif statement to prevent this(NOTE: if i put the cfif above the query params it started the loop out like (,(param 1),(param 2),

Answer Source

You need to track the number of items in the list and check that the current item's position is less than the length of the list:

<cfset numItems = ListLen(AccountListWithSettings)>
<cfset i = 1>

<cfloop list="#AccountListWithSettings#" index="CurrentAccount">
  <!--- Stuff inside of loop --->

  <!--- ...and then --->
  <cfif i lt numItems>
    ,
  </cfif>

  <cfset i++>
</cfloop>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download