Westside2004
asked on
Question and Answer system, help needed with insert
Hi,
I have this Question and Answer system I am building. All the questions are stored in the database. I also have an "Answer" table that stores the answer to each question and it also stores the "jobApplication_id" that the answer is associated with.
Answer table:
answerId (int, auto increment)
answer (text)
questionId(int)
jobApplication_id(int, FK)
When the form is submitted I see this when I dump the #FORM# scope:
<cfdump var="#form#">
-------------------------- ---------- ------
QUESTIONID1 1
QUESTIONID2 2
ANSWER1 answer to question 1
ANSWER2 answer question 2
NUMQUESTIONS 2
-------------------------- ---------- -------
In the actual HTML form, I loop over the questions and display them so a question_id is available. I then appended the "questionId" to the ncessary form fields to make them unique.
How can I loop over the formfields and insert the relevant answer to the question into the "Answer" table and also insert the associated questionId that goes with that answer. I seem to be having a problem with the relationship between the question and the answer.
Any help appreciated
-ws
I have this Question and Answer system I am building. All the questions are stored in the database. I also have an "Answer" table that stores the answer to each question and it also stores the "jobApplication_id" that the answer is associated with.
Answer table:
answerId (int, auto increment)
answer (text)
questionId(int)
jobApplication_id(int, FK)
When the form is submitted I see this when I dump the #FORM# scope:
<cfdump var="#form#">
--------------------------
QUESTIONID1 1
QUESTIONID2 2
ANSWER1 answer to question 1
ANSWER2 answer question 2
NUMQUESTIONS 2
--------------------------
In the actual HTML form, I loop over the questions and display them so a question_id is available. I then appended the "questionId" to the ncessary form fields to make them unique.
How can I loop over the formfields and insert the relevant answer to the question into the "Answer" table and also insert the associated questionId that goes with that answer. I seem to be having a problem with the relationship between the question and the answer.
Any help appreciated
-ws
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This goes to your design on the form generation side - my interpretation of your field names was that the QUESTIONID# field would hold the actual ID - but would be numbered based on its sequence in the output.
Lets say on the form page, you have a loop (or cfoutput) that creates the form:
<cfoutput query="myquery">
<input type="hidden" name="questionid#myquery.c urrentrow# " value="#myquery.questionid #" ...
<cfinput type="text" name="answer#myquery.curre ntrow#" ...
</cfoutput>
<input type="hidden" name="numquestions" value="#myquery.recordcoun t#" />
In this format, the fields will be named in a count order from 1,2,3 etc, because it is using the currentrow value. The value of the questionid1, questionid2, <etc> fields will contain the actual id's (5,9,500,etc).
Ben
www.ScheduleForce.net
Lets say on the form page, you have a loop (or cfoutput) that creates the form:
<cfoutput query="myquery">
<input type="hidden" name="questionid#myquery.c
<cfinput type="text" name="answer#myquery.curre
</cfoutput>
<input type="hidden" name="numquestions" value="#myquery.recordcoun
In this format, the fields will be named in a count order from 1,2,3 etc, because it is using the currentrow value. The value of the questionid1, questionid2, <etc> fields will contain the actual id's (5,9,500,etc).
Ben
www.ScheduleForce.net
ASKER
Yes, that was the problem in my form.
Thanks, that fixed it
-ws
Thanks, that fixed it
-ws
ASKER
Well the index=i, refers to just a counter correct? So if there are two questions on the page, the loop will go from 1 to 2. The problem I see is that the "i" that gets appended to each question is the questionId, it may not always be in order like that. For example, the below code *should* be wrong I think.
1. How many fingers do I have (questionId5) ?
2. How many legs does a dog have (questionId9) ?
<cfloop index="i" from="1" to="#form.numquestions#">
#evaluate("form.questionid
#evaluate("form.answer"&i)
</cfloop>
If in the loop we still have two questions, but the variable i does not line up with the questionId's
-ws