5 Replies Latest reply: Oct 15, 2010 11:46 AM by Merle_Hanson RSS

    Questions - Multiple Inserts

    Merle_Hanson

      I am making a question form - which has multiple questions - which would then

      insert multiple answers (yes/no/na) and notes into a table.

       

      I'm at a loss on how to do it - and having a brain fart...

       

      Here is the loop to create the questions... Radio button for answer...

       

      <cfloop query=Q StartRow=1 EndROW="#Column1NumRows#">
      <tr>
      <td valign=top>
      <cfoutput>
      #qid#.
      </cfoutput>
      </td>
      <td width=300 valign=top>
      <cfoutput>
      #question#
      <cfif len(notes1)><br>#notes1#: <cfinput type="text" name="notes1" value="" size=10></cfif>   
      <cfif len(notes2)><br>#notes2#: <cfinput type="text" name="notes2" value="" size=10></cfif>
      </cfoutput>
      </td>
      <td align=center> Yes<br><cfinput type="Radio" name="answer#qid#" value="Yes"> </td>
      <td align=center> No<br><cfinput type="Radio" name="answer#qid#" value="No"> </td>
      <td align=center> N/A<br><cfinput type="Radio" name="answer#qid#" value="N/A"> </td>
      </tr>
      <tr height=2 bgcolor=grey><td colspan=5></td></tr>
      </cfloop>

      But I am having trouble inserting into the answer table..

       

      I'm assuming I need to loop thru things...

      I also need to attribute this to a single user...

       

      Any help is appreciated...

        • 1. Re: Questions - Multiple Inserts
          Owain North Community Member

          Put underscores into your form field names on the radio buttons. Once you've submitted your form, you'll have a whole load of form fields named something like this:

           

          answer_1 = Y

          answer_2 = N

          answer_3 = Y

          answer_4 = N/A

          etc

           

          So, you need to loop through all your form fields and do an insert.

           

          <cfloop collection="#form#" item="key">

            <cfif find("answer_", key)>

              <cfset ThisQuestion = listLast(key, '_')  />

              <cfset ThisAnswer = form[key] />

              <cfquery...>

                INSERT INTO answers (....)

                VALUES ( <cfqueryparam ThisQuestion />, <cfqueryparam ThisAnswer />) etc

              </cfquery>

            </cfif>

          </cfloop>

           

          Not the most efficient, but that's probably not the major concern here

          • 2. Re: Questions - Multiple Inserts
            Merle_Hanson

            Not sure that will work...

            As the answer is associate with a question ID called QID...

            Not associated to Answer_ID...

            As with Answer ID - I think I'd have to add that field into the database repetitively...

             

            So form has:

             

            ie: Name:   Bob

            ie: Question1 (QID): Are u home?  Answer: Yes

            Question2 (QID) Are u using a computer? Answer: Yes

            Question3 (QID) Are u from TimbukToo?  Answer: No           Note3 - USA

             

            And also be able to associate a note to it as well...

             

            etc...

             

            So the QID is a table field, as is the answer...

             

            QID   Answer   Note

            1          Yes

            2          Yes

            3          No        USA

             

            Thanx for any help...

            I'll continue looking around...

            • 3. Re: Questions - Multiple Inserts
              Merle_Hanson

              What is tripping me up - is naming the radio buttons - as they have to be named differently - so not to get the same answer for each question...

               

              Here is my coding so far...

               

              <cfquery name="Q" datasource="FPB">
              select * from Question
              </cfquery>

               

              <cfform action="form.cfm?src=ins">

              <table border=0 bordercolor=black>
              <tr height=2 bgcolor=grey><td colspan=5></td></tr>
              <cfloop query=Q StartRow=1 EndROW="3">
              <tr>
              <td valign=top>
              <cfoutput>
              #qid#.
              </cfoutput>
              </td>
              <td width=300 valign=top>
              <cfoutput>
              #question#
              <cfif len(notes1)><br>#notes1#: <cfinput type="text" name="notes1" value="" size=10></cfif>   
              <cfif len(notes2)><br>#notes2#: <cfinput type="text" name="notes2" value="" size=10></cfif>
              </cfoutput>
              </td>
              <td align=center> Yes<br><cfinput type="Radio" name="answer_#qid#" value="Yes"> </td>
              <td align=center> No<br><cfinput type="Radio" name="answer_#qid#" value="No"> </td>
              <td align=center> N/A<br><cfinput type="Radio" name="answer_#qid#" value="N/A"> </td>
              </tr>
              <tr height=2 bgcolor=grey><td colspan=5></td></tr>
              </cfloop>
              </table>

              <input type="submit" value="Complete">
              <input type="hidden" name="inspid" value="12">

              </cfform>

               


              <cfif src is "ins">

               

              <cfloop from="1" to="3" index="n">
              <cfoutput>

               

              <cfset qid = "#variables.n#"> #qid# (test output)
              <cfset answer = "#answer_#&#variables.n#"> #answer# (test output)

               

              (Just trying simple insert for now)

              <cfinsert tablename="answer" datasource="fpb">

               

              </cfoutput>
              </cfloop>

               

              </cfif>

              • 4. Re: Questions - Multiple Inserts
                Merle_Hanson

                Progress...

                This is inserting correctly for now...

                Time to amp it up a bit with notes etc...

                 

                <cfif src is "ins">

                <cfloop from="1" to="3" index="n">
                <cfoutput>

                <!--- first we set up field names to match what the form should be passing. --->
                <cfset newqid = "#variables.n#"> #newqid# (test output)
                <cfset newanswer = "answer_"&#variables.n#> #newanswer# (test output)
                <br>

                <!--- then we get whatever values are in those field names and set them as vars --->
                <cfset qid = "#Evaluate(variables.newqid)#"> #qid# (test output)
                <cfset answer = "#Evaluate(variables.newanswer)#"> #answer#

                <cfquery name="insert" datasource="fpb">

                        INSERT INTO answer (qid, answer)

                        VALUES (#qid#, '#answer#');

                </cfquery>


                </cfoutput>
                </cfloop>

                </cfif>

                • 5. Re: Questions - Multiple Inserts
                  Dan Bracuk Community Member

                  The general idea will work.  However, the most important part, as usual, is making sure that your database schema is optimal for what you are trying to accomplish.