CODEWITHSUNDEEP
coldfusionsql-insertcfloop
Visual
Visual

Reputation: 33

insert into table by looping over structure

I am trying to insert into the code but it is inserting value in every row, rather than question value in question and answer value in answer:

<cfset StructDelete(structform,'title')>
<cfset StructDelete(structform,'mode')>
<cfset StructDelete(structform,'formsubmission')>
<cfset StructDelete(structform,'file_upload')>
<cfset StructDelete(structform,'czContainer_czMore_txtCount')>
<CFSET StructDelete(structform,'action')>
<CFLOOP collection="#structform#" index="whichPair">
    <cfset Questions = "question" & structform[whichPair]>
    <cfset answer = "answer" & structform[whichpair]>
    <cfquery name="insertData" datasource="aas">
        insert into faqsquestions(question,answer,createdon,faqID) 
        values(<cfqueryparam cfsqltype="cf_sql_varchar" value="#Right(questions, Len(questions)-8)#">,
        <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#Right(answer, Len(answer)-8)#">,
        <cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDate(now())#">,
        <cfqueryparam cfsqltype="cf_sql_integer" value="#getLastID#">)
    </cfquery>
</CFLOOP>

http://prntscr.com/lntu2l

Upvotes: 3

Views: 343

Answers (1)

SOS
SOS

Reputation: 6550

That's the wrong type of loop for what you're trying to do. The reason is a structure loop iterates once - for each field. When what you want is to loop once - for each pair of fields.

A simple option is add a hidden field to your form, containing the total number of pairs.

<input type="hidden" name="NumberOfQuestions" value="#TheTotalNumberHere#">

Then use the total number with a from and to loop. On each iteration, extract the current value of the question and answer fields, and use them in your query:

<cfloop from="1" to="#FORM.NumberOfQuestions#" index="pairNum">
    <cfset question = FORM["question"& pairNum]>  
    <cfset answer = FORM["answer"& pairNum]>  

    <cfquery ...>
        INSERT INTO faqsQuestions(question,answer,createdon,faqID) 
        VALUES (
            <cfqueryparam cfsqltype="cf_sql_varchar" value="#question#">
            ,  <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#answer#">
            ,  <cfqueryparam cfsqltype="cf_sql_date" value="#now()#">
            ,  <cfqueryparam cfsqltype="cf_sql_integer" value="#getLastID#">
        )
    </cfquery>
</cfloop>

Upvotes: 2

Related Questions