title: sql syntax error while cflooping in cfquery INSERT INTO
body: <p>CF8 and MySQL5, for loop within INSERT INTO.</p>
  
  <p>I am getting form data and trying to optimize an insert statement to loop within the cfquery. 
  The best case scenario would be that the loop is just around the VALUES in order to have a single INSERT, but I had issues in trying to ID the second iteration and put a comma at the beginning of the first INSERT VALUES.
  So I went with looping over the entire INSERT. At least that's within the same cfquery. My problem is that I can't get the syntax correct for the 2nd (and beyond) iterations of the loop. It works fine for the first one, and works fine if I take the loop out.</p>
  
  <p>The error I get is: </p>
  
  <pre><code>sql syntax error near 'INSERT INTO table(wID,session,xNameVar,xID,set1rt,set2rt,set3rt,set4rt,set5rt,set'
  </code></pre>
  
  <p>Here is the code I use code:</p>
  
  <pre><code>&lt;cfif structKeyExists(form, "x1s1xID")&gt;&lt;!--- verifies if there is data in field 1 for a specific form section---&gt;
    &lt;cfquery name="qCreateXdata" datasource="#application.datasource#"&gt;
      &lt;cfloop from="1" to="#form.sessions#" index="i"&gt;&lt;!--- form.sessions is a variable for the number of times to loop ---&gt;
        &lt;cfif structKeyExists(form, "x1s#i#xID")&gt;&lt;!--- a little redundant but used for other iterations of loop otherwise get a variable not defined error ---&gt;
        INSERT INTO table
          (wID,
          session,
          xNameVar,
          xID,
          set1rt,
          set2rt,
          set3rt,
          set4rt,
          set5rt,
          set6rt)
        VALUES
          (#variables.wID#,
          #wCreateFormData["session" &amp; i]#,
          '#wCreateFormData["x1s" &amp; i &amp; "xNameVar"]#',
          #wCreateFormData["x1s" &amp; i &amp; "xid"]#,
          &lt;!--- the below values are not required in form so need to be null integers instead of empty strings if not set in form ---&gt;
          &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x1set1reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x1set1r" &amp; i]))#" /&gt;,
          &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x1set2reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x1set2r" &amp; i]))#" /&gt;,
          &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x1set3reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x1set3r" &amp; i]))#" /&gt;,
          &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x1set4reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x1set4r" &amp; i]))#" /&gt;,
          &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x1set5reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x1set5r" &amp; i]))#" /&gt;,
          &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x1set6reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x1set6r" &amp; i]))#" /&gt;
          )
          &lt;cfif structKeyExists(form, "x2s#i#xID")&gt;
              ,(#variables.wID#,
              #wCreateFormData["session" &amp; i]#,
              '#wCreateFormData["x2s" &amp; i &amp; "xNameVar"]#',
              #wCreateFormData["x2s" &amp; i &amp; "xid"]#,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x2set1reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x2set1r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x2set2reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x2set2r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x2set3reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x2set3r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x2set4reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x2set4r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x2set5reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x2set5r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x2set6reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x2set6r" &amp; i]))#" /&gt;
              )
          &lt;/cfif&gt;
          &lt;cfif structKeyExists(form, "x3s#i#xID")&gt;
              ,(#variables.wID#,
              #wCreateFormData["session" &amp; i]#,
              '#wCreateFormData["x3s" &amp; i &amp; "xNameVar"]#',
              #wCreateFormData["x3s" &amp; i &amp; "xid"]#,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x3set1reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x3set1r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x3set2reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x3set2r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x3set3reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x3set3r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x3set4reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x3set4r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x3set5reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x3set5r" &amp; i]))#" /&gt;,
              &lt;cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#trim(wCreateFormData["x3set6reps" &amp; i])#" maxlength="3" null="#NOT len(trim(wCreateFormData["x3set6r" &amp; i]))#" /&gt;
              )
          &lt;/cfif&gt;
  
          &lt;!---... you get the idea.....there are 12 total cfif blocks like this ---&gt;
          &lt;!--- then I end with a ';' in order to close this INSERT statement and go back to the start of the loop for #form.sessions# number of times ---&gt;
  
          ;
        &lt;/cfif&gt;
      &lt;/cfloop&gt;
    &lt;/cfquery&gt;
  &lt;/cfif&gt;
  </code></pre>