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

[Owainnorth]

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

[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...

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

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

[Dan_Bracuk]

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.