Skip to main content
A
Anonymous
June 25, 2010
Question

inserting into multiple tables using cfloop index

  • June 25, 2010
  • 1 reply
  • 1269 views

Please help ASAP. I AM TRYING TO FIGURE OUT HOW TO INSERT DATA INTO MULTIPLE TABLES OF AN ORACLE DB USING CFLOOP INDEX.

I have the following code provided to me by another developer.

<cfloop index="thefield" list="#form.fieldnames#">
    <cfset fields="#thefield# = #evaluate(thefield)#">
    <cfoutput>#fields#</cfoutput><br>
</cfloop>

This outputs the following:

RESULTS_ARRAY0=CORE DATA

RESULTS_ARRAY1=STATUS

RESULTS_ARRAY2=LOCATION

RESULTS_ARRAY3=RAW SCORE

RESULTS_ARRAY4=MAX SCORE

RESULTS_ARRAY5=MIN SCORE

RESULTS_ARRAY6=TIME

<---i need to insert this data into 1 table--->

RESULTS_ARRAY7=FAILED (status - array1 value)

RESULTS_ARRAY8=31 (location - array2 value)

RESULTS_ARRAY9=60 (raw score - array3 value)

RESULTS_ARRAY10=170 (max score - array4 value)

RESULTS_ARRAY11=0 (min score - array5 value)

RESULTS_ARRAY12=00:02:32 (time - array6 value)

RESULTS_ARRAY13=INTERACTION DATA

RESULTS_ARRAY14=DATE

RESULTS_ARRAY15=TIME

RESULTS_ARRAY16=INTERACTION ID

RESULTS_ARRAY17=OBJECTIVE ID

RESULTS_ARRAY18=INTERACTION TYPE

RESULTS_ARRAY19=CORRECT RESPONSE

RESULTS_ARRAY20=STUDENT RESPONSE

RESULTS_ARRAY21=RESULT

RESULTS_ARRAY22=WEIGHT

RESULTS_ARRAY23=LATENCY

<----i need to insert this data into another table --------------------------->

<----this set of data repeats 16 times with different values---->

RESULTS_ARRAY24=06/15/2010 (date - array14 value)

RESULTS_ARRAY25=10:53:14 (time - array15 value)

RESULTS_ARRAY26=INTERACTION 1240217 (interaction id - array16 value)

RESULTS_ARRAY27=QUIZ10030 (objective id - array 17 value)

RESULTS_ARRAY28=SEQUENCING (interaction type - array18 value)

RESULTS_ARRAY29=PRE-NEGOTIATION, PROCESS, CONSIDERATION, IMPLEMENTATION (correct response - array19 value)

RESULTS_ARRAY30=IMPLEMENTATION, PROCESS, PRE-NEGOTIATION, CONSIDERATION (student response - array20 value

RESULTS_ARRAY31=WRONG (result - array21 value)

RESULTS_ARRAY32=10 (weight - array22 value)

RESULTS_ARRAY33=00:00:03 (latency - array23 value)

<----starts next set of results---------------------------------------------->

RESULTS_ARRAY34=06/15/2010

RESULTS_ARRAY35=10:53:19

RESULTS_ARRAY36=INTERACTION 1240217

RESULTS_ARRAY37=QUIZ10030

RESULTS_ARRAY38=TRUE-FALSE

RESULTS_ARRAY39=TRUE

RESULTS_ARRAY40=TRUE

RESULTS_ARRAY41=CORRECT

RESULTS_ARRAY42=10

RESULTS_ARRAY43=00:00:02

<----next set will start here.....etc----->

RESULTS_ARRAY44=....

   UNTIL

RESULTS_ARRAY183=....

ANY SUGGESTIONS WILL BE GREATLY APPRECIATED!

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    June 25, 2010

    The easiest way is to give your form fields better names.  Are you able to do that?

    A
    Anonymous
    June 25, 2010

    Dan,

    I appreciate your looking at this issue. I had a long way of doing it, which was just inserting data using the form field names

    <cfquery name="insertCore" datasource="#dataSource#">
    insert into tblcore
    (corestatus, corelocation, corerawscore, coremaxscore, coreminscore, coretime)
    values
    ('#form.results_array7#', '#form.results_array8#', '#form.results_array9#', '#form.results_array10#', '#form.results_array11#', '#form.results_array12#')
    </cfquery>

    But I was hoping there was a shorter way of doing it. Actually the form data is coming from a CBT quiz using Adobe Captivate. The developer uses javascript to extract the results out of captivate once the user is finished. I have extracted the appropriate code out of the entire js script.

    START

    // // here we assign the split for the data, # mark.
    var results_array = stripQuotes.split("#");

    // assign variables
    var RawScore = results_array[9];
    var MaxScore = results_array[10];
    var MinScore = results_array[1];
    var Accuracy = RawScore/MaxScore;
    var ArrayLength = results_array.length;
        var LoopStart = 0;
    var LoopStop = ArrayLength;
    Accuracy = Accuracy*100;


      // stuff array values into hidden form fields
    document.write ("<form name='results' method='post' action='insert.cfm'>");
    document.write("<table border='0'>");

    for (ii=0; ii<ArrayLength; ii++) {
      // If we are in debug mode, write out the data to be submitted:
      if (debugMode == 1) {
       document.write("<tr>");
       document.write("<td>results_array[" + ii + "]</td>");
       document.write("<td><input type='text' readonly='readonly' name='results_array" + ii + "' value='" + results_array[ii] + "' /><br></td>");
       document.write("</tr>");
      } else if (debugMode == 0) {
       // we are not in debug, so use hidden fields to pass the data:
       document.write("<input type='hidden' readonly='readonly' name='results_array" + ii + "' value='" + results_array[ii] + "' />");
      } else {
       // something is broken
       document.write("<h1>error: debugMode not properly set</h1>");
      }
    }

    END

    insert.cfm is the file that includes

    <cfloop index="thefield" list="#form.fieldnames#">
        <cfset fields="#thefield# = #evaluate(thefield)#">
        <cfoutput> #fields# </cfoutput><br>
    </cfloop>

    * Instead of #fields#, would like to include insert statements.

    GravityNinja
    GravityNinja
    Participant
    June 25, 2010

    Not exactly the way I would go but give this a whirl:

    <cfparam name="topPart" default="" />
    <cfparam name="bottomPart" default="" />
    <cfloop list="#form.FieldNames#" index="thefield">
        <cfset yourCounter = replace(theField,"form.RESULTS_ARRAY","") />
    <cfif yourCounter GT 6>
      <cfset topPart = listAppend(topPart,theField) />
      <cfset bottomPart = listAppend(bottomPart,evaluate(theField)) />
      <cfif yourCounter EQ 23>
       <cfquery datasource="yourDSN">
        INSERT INTO yourTable
         (#topPart#)
        VALUES
         (#bottomPart#)
       </cfquery>
       <cfset topPart = "" />
       <cfset bottomPart = "" />
      <cfelseif yourCounter EQ 33 OR
                   yourCounter EQ 43 OR
                   yourCounter EQ 53 OR etc...>
        <cfquery datasource="yourDSN">
        INSERT INTO yourTable
         (#topPart#)
        VALUES
         (#bottomPart#)
       </cfquery>
       <cfset topPart = "" />
       <cfset bottomPart = "" />
      </cfif>
    </cfif>
    </cfloop>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices