• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

Multiple database inserts with 1 form

I am setting up a survey web site where one group of people can create survey questions and then select which questions to put on their survey. I want to be able to show all of their questions on one page with check boxes (already done) and then submit all of the questions they selected to the survey table. So there would be one record submitted for every question they select
The survey table is named surveytbl and it has the following fields: sid (survey id an autonumber field), surveyname, questid (question ID), deptid (department ID), surveydate.  I use #database# to dynamicaly identify my database.
I also want to do the same thing when people fill out the survey it will show all the questions for the survey on one page but when the questions get answered each answer will get put into a table with the question ID, the user ID, the survey ID and of course the answer.
I think the same soulution with work for both I just don't know where to start.  I use Coldfusion and html, I don't really use javascript or anything like that.
0
shanksr
Asked:
shanksr
  • 5
  • 2
  • 2
  • +1
1 Solution
 
thack111Commented:
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
0
 
shanksrAuthor Commented:
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>
0
 
shanksrAuthor Commented:
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.
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
thack111Commented:
try sending the number of questions, then loop through a cfloop that number of times with a insert each time.
0
 
shanksrAuthor Commented:
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?
0
 
shanksrAuthor Commented:
Moderator, can you close this question and restore my point?  I was able to figure it out on my own.
0
 
mrichmonCommented:
shanksr,

We can close the question, but your points will be refunded only if you post the solution that you used
0
 
shanksrAuthor Commented:
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.
0
 
mrichmonCommented:
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:

http://www.experts-exchange.com/Community_Support/
0
 
CetusMODCommented:
PAQ'd, 200 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now