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

    Questions - Multiple Inserts

    Merle_Hanson Level 1

      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 Level 4

          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 Level 1

            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 Level 1

              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 Level 1

                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 Level 5

                  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.