Multiple database inserts with 1 form

You might try useing a <cfloop> or a counter (<cfset qestionNum = qestionNum + 1>) then use this questionNum to name the fields.

Are they going to be able to have different formats?
if so then you will need to send data about what format, multiple choice or text...
have hidden fields at the top of the form that sends the sid, and uid.  then you can use the sid, and uid over and over again in yourinsert statments.

I assume you have the servey questions in a table.  you will need to make sure they can't modify a survey after someone has taken it, because then the questions could be out of order, or different.

I would have completely different templates to use to allow the users to create questions, and add them to survey defenitions.
You might mark the survey definitions with complete flags, so they won't be taken till the user has entered them exactly as they want.

Hope that is what you were asking, if not please add more detail, and witch part you are on.
Cheers

The questions can be different formats but in all cases every question only has one answer so the format doesn't matter at that point. Currently I have this to display my questions but I don't know what to with it.
<CFOUTPUT QUERY="qlist">
<CFSET i = i+1>
              <TR>
                  <TD WIDTH="25"><DIV ALIGN="RIGHT">
                        <INPUT TYPE="hidden" NAME="qnumber" VALUE="#i#">
                        <INPUT TYPE="HIDDEN" NAME="qid" VALUE="#qid#">
                        <INPUT TYPE="checkbox" NAME="checkbox#i#" VALUE="checkbox">
                    </DIV></TD>
                  <TD WIDTH="79">#qtype#</TD>
                  <TD NOWRAP>#qquestion#</TD>
              </TR>
</CFOUTPUT>

Also, yes I have a questions table (questiontbl) and a survey table (surveytbl) and an answer table (answertbl) as well as other tables to hold user information and what not...
The questiontbl has qid (question ID) and qtype (for the type) and qquestion (for the actuall question) as well as other fields related to question specifics to facilitate different question types... multiple choice, range questions and so on.
The surveytbl has sid, surveyname, questid (relational to questiontbl.qid), deptid (so I know who created it), and surveydate
The answertbl has aid, qid (for the question it is the answer to, relational to questiontbl.qid), answer, adate, and alumniid (the person who is answering the question).

I don't know how to process multiple "records" on one submission. I think that is my problem here.
I do realize that I could have them submit the questions one at a time but I think this is a better way of doing it.

try sending the number of questions, then loop through a cfloop that number of times with a insert each time.

If I am displaying 20 questions for instance and they only want 5 questions on the survey how do I know they only selected 5 questions?

Moderator, can you close this question and restore my point?  I was able to figure it out on my own.

shanksr,

We can close the question, but your points will be refunded only if you post the solution that you used

When I created the survey, I displayed all the questions with a CFOUTPUT and with a check box <input type="checkbox" name="question" value="#questionID#>. Then when they submit the questions for the survey all the question ID's are put into one field like this qid="3,5,12,32". Then when I want to display the survey I would loop through the qid field to display the list of questions. Then I display the questions I set the answer <input type="(depends on the question)" name="answer#qid#"> the value will be what the user chooses. This then gets submitted to the database, however it is not that easy to dynamically submit a list like this to a database. What I had to do is use a ColdFusion fuction named evaluate, the whole submission thing looks like this.
<CFSET myList = #form.questionlist#>
        <CFLOOP INDEX="i" LIST="#myList#" DELIMITERS=",">
      <CFIF isdefined("Form.answer#i#")>
                    <CFSET answer = evaluate("form.answer#i#")>
                     <CFSET alumniid = cookie.person.id>
            <CFSET qid = i>
            <CFSET sid = #form.sid#>
            <CFQUERY NAME="addanswer" DATASOURCE="#database#">
                  INSERT INTO answertbl (qid, answer, alumniid, asurveyid)
                  VALUES ('#qid#', '#answer#', '#alumniid#', '#sid#')
            </CFQUERY>
        </CFIF>
          </CFLOOP>

That is it!  The real tricky part was the evaluate fuction which I found here on Experts Exchange!!!! You just have to know exactly what to look for (this is usually the problem).

Thanks to everyone who helped.

Thanks for posting your solution, as a note although it is not the best way to be doing things.

Really what you should be doing instead of evaluate is the following:

Instead of <CFSET answer = evaluate("form.answer#i#")>

Use <CFSET answer = Form['answer' & i]>

This will be significantly more efficient .

Also anywhere that you have # inside a cftag (other than an attribute i.e. inside double quotes) it should be removed.

SO
<CFSET myList = #form.questionlist#>  should be <CFSET myList = form.questionlist>
<CFSET sid = #form.sid#> should be <CFSET sid = form.sid>

BUT this one should keep the # <CFQUERY NAME="addanswer" DATASOURCE="#database#">

Finally you shol\uld use cfqueryparams when inserting into the databse.

THese things will make your code more secure and run faster and more efficiently.


To get your points refunded you need to post here:

https://www.experts-exchange.com/Community_Support/