• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Cfloop and insert

Experts-

I am looking for some assistance with making an insert with cfloop.

I have a table called answers that I need to make an insert to from form fields.  The user can provide two different answers, and two different types, and I need to insert them into the answer table.  I've attached code below.  The top half is my form, and the bottom half is my action page.  This comment below separates the two...
<!--- This is what I currently have in trying to make my update --->

Any help/suggestions would be greatly appreciated.

Thanks
Nick


<!--- Below are my fields....cAnswer = text, cType = select, mAnswer=text, mtype=Select --->
<tr>
    <td valign="top"><div align="right"><strong>Correct Answer</strong></div></td>
    <td valign="top">&nbsp;</td>
    <td valign="top"><input name="cAnswer" type="text" /><select name="cType">
    <option value="Select The Answer Type"></option>
    <cfoutput query="getTypes">
    <option value="#getTypes.answerID#">#getTypes.answerType#</option>
    </cfoutput>
    </select><br />
      <span class="scale">Provide the Correct Answer.</span><br />
    &nbsp;</td>
  </tr>
  <tr>
    <td valign="top"><div align="right"><strong>Misconception</strong></div></td>
    <td valign="top">&nbsp;</td>
    <td valign="top"><input name="mAnswer" type="text" /><select name="mType">
    <option value="Select The Answer Type"></option>
    <cfoutput query="getTypes">
    <option value="#getTypes.answerID#">#getTypes.answerType#</option>
    </cfoutput>
    </select>
 <br />
      <span class="scale">Provide the Misconception.</span><br />
    &nbsp;</td>
  </tr>
<!--- Total number of possible answers --->
	<input type="hidden" name="TotalAnswers" value="2" />
 
<!--- This is what I currently have in trying to make my update --->
 <!--- set the ID --->
  <cfset itemID = #maxID.lastID#>
  <!--- Set the answers for this item --->
  <cfparam name="FORM.TotalAnswers" default="0">
  <cfloop from="1" to="#FORM.TotalAnswers#" index="i">
  <!--- Covert the form variables to local vars --->
  <cfif isDEFINED("FORM.cAnswer"&i)> 
    <cfset this.answer = FORM['cAnswer'&i]>
    <cfset this.type=FORM['cType' & i]>
    <cfelseif isDEFINED("FORM.mAnswer"&i)>
    	<cfset this.answer=FORM['mAnswer'&i]>
        <cfset this.type = FORM['mType'&i]>
  <cfquery name="insertAnswers" datasource="#Request.datasource#">
  	INSERT INTO answers(itemID, answer, answerID)
    	VALUES(#itemID#, '#this.answer#','this.type#')
  </cfquery>
  </cfif>
  </cfloop>

Open in new window

0
nmarano
Asked:
nmarano
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
eszaqCommented:
I am a little bit confused about what you are trying to do. What are the possible values of #getTypes.answerType#? Is TotalAnswers always 2?

Where do Questions themselves come from? Is there table "Questions"? If so, why your insert does not cover QuestionID. You are not interested to link answers to questions? OR your users enter their own question (plus correct answer and misconcept) into web form, and itemID is in fact question ID?

Knowing more about database logic behind your application would help
0
 
gdemariaCommented:
I agree with eszag that some more information about what this would look like would be helpful.

Once observeration, your processing part is looking for an "array" of form fields, for example with this line..
   <cfset this.answer = FORM['cAnswer'&i]>
You are looking for variables that look like this...  FORM.cAnswer1  Form.cAnswer2...

But in your form part (upper half) you are not creating any "array" fields.  The form fields look like this:   name="cAnswer"  (there is no 1, 2, 3)   So you are not being consistent.

To understand which way to go (to use the array style or not)  we need more information about what you have.   Are you putting 10 questions on the page, each one requires a 1,2,3 after the name or do you really only have these two questions, which you have named differently (cAnswer and mAnswer) and therefore do not need the array & i part in your action section...


0
 
nmaranoAuthor Commented:
Maria, eszag

Sorry for the confusion.

#getTypes.AnswerType# returns two values 'C' for Correct and 'M' for Misconception.  A user is using this form to enter information about a question.  The information is then stored in the database. The user enters a letter value(A,B,C,D etc) into cAnswer text field and then picks a type('C' or 'M') from my select cType.  Then they enter a letter value for mAnswer text field and then they pick a type from the mType select.  

Essentially if I had two insert statements, they would look like this.....
<cfquery name="insertCAnswers" datasource="#Request.datasource#">
        INSERT INTO answers(itemID, answer, answerID)
        VALUES(#itemID#, '#FORM.cAnswer#',#FORM.cType#')
  </cfquery>

AND

<cfquery name="insertMAnswers" datasource="#Request.datasource#">
        INSERT INTO answers(itemID, answer, answerID)
        VALUES(#itemID#, '#FORM.mAnswer#',#FORM.mType#')
  </cfquery>

I guess I'm looking for a better way of doing this rather than having two queries, and I'm guessing there must be.

Thanks
Nick
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
ZvonkoSystems architectCommented:
Your confusion starts with your submit form.
Why do you name the field cAnswer and mAnswer?
Would it be more simple to name the field: theAnswer!
When you ask the user to SELECT!!! the C for Correct answer then is one of you two dumb, either your user doing so as you asked or...

Simply use a hidden field for corresponding theAnswer where you asked for type C answers and where you asked for M answers use hidden field with value type set to M.

By the way, your action page expect numbered cAnswer1 and mAnswer234 form fields. Did you notice that? The submit form has no numbered fields?


0
 
ZvonkoSystems architectCommented:
Your form looks then like this:
<input type="hidden" name="TotalAnswers" value="2" />
 
  <tr>
    <td valign="top"><div align="right"><strong>Correct Answer</strong></div></td>
    <td valign="top"> </td>
    <td valign="top"><input name="theAnswer1" type="text" /><br />
      <input type="hidden" name="typeAnswer1" value="C" >
      <span class="scale">Provide the Correct Answer.</span><br />
     </td>
  </tr>
  <tr>
    <td valign="top"><div align="right"><strong>Misconception</strong></div></td>
    <td valign="top"> </td>
    <td valign="top"><input name="theAnswer2" type="text" /><br />
      <input type="hidden" name="typeAnswer2" value="M" >
      <span class="scale">Provide the Misconception.</span><br />
     </td>
  </tr>

Open in new window

0
 
ZvonkoSystems architectCommented:
And your action temlate looks then like this:
  <cfloop from="1" to="#FORM.TotalAnswers#" index="i">
    <cfif isDEFINED("FORM.theAnswer"&i) > 
      <cfquery name="insertTheAnswers" datasource="#Request.datasource#">
        INSERT INTO answers(itemID, answer, answerID)
        VALUES(#itemID#, '#FORM["theAnswer"&i]#','#FORM["typeAnswer"&i]#')
     </cfquery>
    </cfif>
  </cfloop>

Open in new window

0
 
eszaqCommented:
You have not  answered question what ItemID stands for. Is it for question (disregard of who is answering it)? Or it is for the person answering question?

I suspect there is a flow in both - business logic and database design. Is it something you are building from scratch? Without knowing the purpose of your application... the way you described your problem in the second round... I do not even see the reason to over complicate the issue by storing answers as separate records in your database. Just have three columns in your "answers" table:
 itemID, CorrectAnswer, Misconcept.
Anyway, since you have fre-form text input and not some sort of multiple choice form-fields, you will not even be able to run stats on the data you are collecting.

What is your application for? Educational test? Trivia poll with random questions for entertainment purpose (which would explain so little info stored)?
0
 
nmaranoAuthor Commented:
Zvonko-

I see what your saying about using the hidden fields.

eszag-
itemID is a PK generated in the db.  The app is for education.  A researcher uses this form to enter information about the question that they want to use for a test.  As they enter all of the information, they also enter the correct answer for the question they are submitting along with a possible misconception that a student may have on the question.  I'm storing the actual answers/misconception for the question a researcher is submitting in a table called answer.  The table looks like this

 itemID     answer     answerID
101            a                   c
101            d                   m
102            d                   c
102            b                   m

When I refer to answers, I'm not referring to the response that a student would give, but rather the correct answer or misconception for this particular question/itemID.  

My thought on doing it this way, and please tell me if my logic is incorrect, was to be able to grade the kids test with the reports that I would be running.  I thought I would be able to compare answer.itemID to studentresponse.itemID in order to know if the student got it right/wrong or if it was a misconception.  
0
 
eszaqCommented:
Application design... I need to give it a tthought. Do not want to rush in.

But in terms of getting into your table the same data you are inserting now, I don't even see need to use hidden fields.
<tr>
  <td valign="top"><div align="right"><strong>Correct Answer</strong></div></td>
  <td valign="top"> </td>
  <td valign="top"><input name="theAnswer_C" type="text" /><br />
    <span class="scale">Provide the Correct Answer.</span><br />
   </td>
</tr>
<tr>
  <td valign="top"><div align="right"><strong>Misconception</strong></div></td>
  <td valign="top"> </td>
  <td valign="top"><input name="theAnswer_M" type="text" /><br />
    <span class="scale">Provide the Misconception.</span><br />
   </td>
</tr>
 
 
YOUR BACK END PROCESSING:
 
<CFIF IsDefined("FORM.fieldnames")>
 <CFLOOP index="thisField" list="#FORM.fieldnames#">
  <cfset thisAnswerType = listLast(thisField,"_")>
  <cfset thisAnswer = evaluate("form."&thisField)>
  <cfquery name="insertTheAnswers" datasource="#Request.datasource#">
	INSERT INTO answers(itemID, answer, answerID)
	VALUES(#itemID#, '#thisAnswer#','#thisAnswerType#')
  </cfquery>
 </CFLOOP>
</CFIF>

Open in new window

0
 
eszaqCommented:
Of course code above assumes that there are no fields coming from form submission with names that to not fit into this pattern  theAnswer_C / theAnswer_M. You might want to check for validity of field name and only process thoise that match. E.g. if your sumbit button has name it will be passed through. So, you'd have to add condition <cfif thisField NE "SUBMIT"> (all form field names are passed in UPPER case). Or better use regular expressions to check validity of the formm-field name to be included in the loop for your insert query.

Might seem like a pain. But advantage of this solution - you can have as many Answer / Misconcept pairs as you want. Of course, tracking itemID that goes with each question will need additional processing, but this, too, can be resolved in similar fashion. E.g., form fileds are named according to the convention:
#itemID#_theAnswer_#answerType#
(or #itemID#_theAnswer_#answerID# - whatever you find more convenient)

Then you process form fields like this:
 <cfset thisItemID = listFirst(thisField,"_")>
 <cfset thisAnswerType = listLast(thisField,"_")>
  <cfset thisAnswer = evaluate("form."&thisField)>


0
 
nmaranoAuthor Commented:
Thank you all for your input and suggestions.  
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now