insert into table by looping over structure

Question

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>

can anyone tell what i am doing wrong here, i know i am using question as a static value just inside the loop as cfset and doing a right to remove that question variable which makes no sense but i will remove it when i am finished fixing my code questions and answers are like this:

http://prntscr.com/lntu2l

How are you getting this data? Are both `question` and `answer` being passed as form fields? There may be simpler ways to insert a question,and answer into a database from form inputs. — Shawn, Nov 29 '18 at 18:41
Also, you'd be much better off normalizing your data. Have a table for questions and another table for answers with a relation to the question that was asked. Which brings me back to the question of is this for the creation of the questions and answers or is this inserting answers to a series of questions? — Shawn, Nov 29 '18 at 18:44
Ah, Google to the rescue. I'm guessing you're using the czMore JQuery plugin (https://github.com/cozeit/czMore), correct? So is this adding another answer for a question? Is your posted image a dump of the structure you get from the form? What is actually passed through the `form` scope? — Shawn, Nov 29 '18 at 18:58

score 2 · Accepted Answer · edited Nov 28 '18 at 06:04

2

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>

edited Nov 28 '18 at 06:04

rrk

15,677
4
29
45

answered Nov 28 '18 at 01:49

SOS

6,430
2
11
29

1

I see that `now()` was changed. IMHO, if that is going to be changed, it should be replaced with `getDate()` purely on the SQL side. – James A Mohler Nov 28 '18 at 16:04
1

@JamesMohler - Just curious, but why? Since they are using DATE, the CreateODBCDate was removed because it is redundant. Though if the CF server is the same as the db server, agreed it could go either way. – SOS Nov 28 '18 at 16:10
CF `now()` vs SQL `getDate()` is almost a philosophical debate. Using SQL to create the date will use significantly fewer resources than the application, but since they are technically different dates (and can easily differ), your application's needs will determine the most appropriate one to use. Unless you track that date in UTC, then the date would be the same in both and I would definitely go with SQL. – Shawn Nov 29 '18 at 18:26
@Shawn - Why do you say signifigantly more? Never really delved into the differences much, as the UTC issue was more important to me. – SOS Nov 30 '18 at 04:55
@Ageax I guess "significantly" is kind of relative, but for `now()`, CF has to generate the value, then it has to do it's parameterizing magic (including its own validation), then it has to pass that value to the query, then SQL has to take that value from CF and do it's magic to validate the datatype and insert that value. With `getDate()`, SQL just calculates the value itself and inserts the data. – Shawn Nov 30 '18 at 12:21

insert into table by looping over structure

1 Answers1