Multiple database inserts with 1 form

Posted on 2004-11-18
Last Modified: 2013-12-24
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.
Question by:shanksr
    LVL 1

    Expert Comment

    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.

    Author Comment

    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>
                      <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">
                      <TD WIDTH="79">#qtype#</TD>
                      <TD NOWRAP>#qquestion#</TD>

    Author Comment

    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.
    LVL 1

    Expert Comment

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

    Author Comment

    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?

    Author Comment

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

    Expert Comment


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

    Author Comment

    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 =>
                <CFSET qid = i>
                <CFSET sid = #form.sid#>
                <CFQUERY NAME="addanswer" DATASOURCE="#database#">
                      INSERT INTO answertbl (qid, answer, alumniid, asurveyid)
                      VALUES ('#qid#', '#answer#', '#alumniid#', '#sid#')

    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.
    LVL 35

    Expert Comment

    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.

    <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:

    Accepted Solution

    PAQ'd, 200 points refunded.
    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Superior storage. Superior surveillance.

    WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

    This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
    In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now