inserting form fields

Experts-

Looking for best way to insert form fields into a table.  Do I use a simple cfloop or is there a more efficient way to make the insert.  Also, I want to insert a record for each argument even if they didn't respond.  Below is what I currently have....

 <cfquery name="addSurvey" datasource="#REQUEST.datasource#">
              INSERT INTO sears_Survey_response(surveyid
                                                ,question_id
                                    ,prospect_id
                                    ,date_entered)
                                   
                        VALUES (<cfqueryparam cfsqltype="cf_sql_integer"    value="#arguments.q1#">
                                    ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q2#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q3#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q3Text#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q4#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q5#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q5Text#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q6#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q7#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q8#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q8Text#">
                                ,#CREATEODBCDATE(NOW())# )
          </cfquery>
LVL 2
nmaranoAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
Sure you can do that.   Just adapt my second example to insert the field name in one column and the value in the other.

<cfloop list="#structKeyList(arguments)#" index="fieldName">
           <!--- if this is a question field ....--->
           <cfif left(fieldName, 1) EQ "q">
               <cfquery name="addSurvey" ...>
                   INSERT INTO sears_Survey_response ( question_id, response_id, ....)
                    VALUES (
                   <cfqueryparam cfsqltype="cf_sql_integer"    value="#fieldName#">
                  , <cfqueryparam cfsqltype="cf_sql_integer"    value="#arguments[fieldName]#">
                  , ...
                  )
              </cfquery>
           </cfif>
</cfloop>

> would like to have the name of my questions inserted here

Though it would be better if you stored the questions in another database table. Then stored that table's unique ID into the sears_Survey_response table instead of the text "q2".

0
 
_agx_Commented:
I'm not sure I follow.  How do your values (#arguments.q1#, etc...) match up with the table columns?
0
 
nmaranoAuthor Commented:
agx-

Sorry....#arguments.q1# are my form fields.

So my statement should look more like...

 <cfquery name="addSurvey" datasource="#REQUEST.datasource#">
              INSERT INTO sears_Survey_response(surveyid
                                                ,question_id
                                    ,prospect_id
                                    ,date_entered)
                                   
                        VALUES (<cfqueryparam cfsqltype="cf_sql_integer"    value="2"><!---surveyID --->
,<cfqueryparam cfsqltype="cf_sql_integer"    value="#arguments.q1#"><!---question_id--->
,<cfqueryparam cfsqltype="cf_sql_integer"    value="#arguments.prospectID#">
                                ,#CREATEODBCDATE(NOW())# )
          </cfquery>

So the surveyID, prospectID and date would all be the same, but the questions from my form which are these are different....
<cfqueryparam cfsqltype="cf_sql_integer"    value="#arguments.q1#">
                                    ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q2#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q3#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q3Text#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q4#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q5#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q5Text#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q6#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q7#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q8#">
                                ,<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.q8Text#">
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
_agx_Commented:
If all argument names were numeric ie q3,q4 instead of q3,q3Text then you could just loop from 1 to the total number of fields and extract the values using array notation

ie   <cfloop from="1" to="#numOfFields#" index="counter">
            <cfset questionValue = arguments["q"& counter"]> <=== ie q1, q2, q3, ....>
            <cfquery name="addSurvey" ...>
                 INSERT INTO sears_Survey_response ( question_id, ....)
                 VALUES (<cfqueryparam cfsqltype="cf_sql_integer"    value="#questionValue #">, ...)
            </cfquery>
      </cfloop>

But if you can't change it for some reason .. #arguments# is a structure. So you could also loop through it's keys and look for any arguments starting with "q" instead


ie   <cfloop list="#structKeyList(arguments)#" index="fieldName">
           <!--- if this is a question field ....--->
           <cfif left(fieldName, 1) EQ "q">
               <cfquery name="addSurvey" ...>
                   INSERT INTO sears_Survey_response ( question_id, ....)
                    VALUES (<cfqueryparam cfsqltype="cf_sql_integer"    value="#arguments[fieldName]#">, ...)
              </cfquery>
           </cfif>
      </cfloop>

0
 
nmaranoAuthor Commented:
cool thanks!  I'll try it out!
0
 
nmaranoAuthor Commented:
agx-

Sorry...having a pretty tough day already...

I forgot to include response in my query to insert.  

 <cfquery name="addSurvey" datasource="#REQUEST.datasource#">
              INSERT INTO sears_Survey_response(surveyid
                                                ,question_id
                                    ,prospect_id
                                    ,response
                                    ,date_entered)
                                   
                        VALUES (<cfqueryparam cfsqltype="cf_sql_integer"    value="2"><!---surveyID --->
,<cfqueryparam cfsqltype="cf_sql_varchar"    value="#arguments.q1#"><!---question_id--->
,<cfqueryparam cfsqltype="cf_sql_integer"    value="#arguments.prospectID#"><!--- Prospect_id --->
,,<cfqueryparam cfsqltype="cf_sql_varchar"    value="#arguments.q1#"><!--response --->
                                ,#CREATEODBCDATE(NOW())# )
          </cfquery>

So I guess my confusion is this.  Below is what my question looks like, so I want to be able to take "q1" and insert that as my question_id in the db and then take whatever they respond(ie, 1, 2,3, 4) and insert that as my response field in the db.

Again, sorry about the confusion.  
0
 
_agx_Commented:
so I want to be able to take "q1" and insert that as my question_id in the db and then take whatever they respond(ie, 1, 2,3, 4) and insert that as my response field in the db.

Now it's my turn to be confused :)  Do you mean insert the literal string "q1" as the question ID?  
     If the field name is:  <cfset aguments.q1 = 4>
     Then => INSERT INTO (question_id, response_id) VALUES ('q1', 4)

        ,<cfqueryparam value="#arguments.q1#"><!---question_id--->
        ,<cfqueryparam value="#arguments.q1#"><!--response --->
   
Or is there actually a separate "response" argument somewhere?
 
0
 
_agx_Commented:
Or to put it another way, do you have a separate field for the "question_id" and "response_id" values OR is there just one field:  q1, q2, etc...?
0
 
nmaranoAuthor Commented:
Alrighty then!

So my tbl looks like this...
survey_id<--will default to 2
question_id<!--would like to have the name of my questions inserted here
prospect_id<!-- essentially a user id
response<!---whatever their choice was for their answer.

So two questions on my form look like this....
<tr>
               <td valign="top" class="statement">&nbsp;</td>

          <td class="statement">Rate the professionalism of the agent you spoke with when you called  Pro.</td>
          <td class="mid"><input name="q1" type="radio" value="4" /></td>
          <td class="mid"><input name="q1" type="radio" value="3" /></td>
            <td class="mid"><input name="q1" type="radio" value="2" /></td>
          <td class="mid"><input name="q1" type="radio" value="1" /></td>
           <td class="mid"><input name="q1" type="radio" value="0" /></td>
        </tr>

ANOTHER QUESTION LOOKS LIKE THIS.....

<p class="stem">Did you set an appointment?</p>
<p class="response"><input name="q3" type="radio" value="1" onClick="ShowHideIt('q3Text', false);" />Yes</p>
<p class="response"><input name="q3" type="radio" value="2" onClick="ShowHideIt('q3Text', true);" />No</p>

<p class="response"><textarea cols="60" rows="6" Id="q3Text" style="display:none"></textarea></p>

So is there a way for me to take the input name q3 and insert it as the question_id and let's say they selected "Yes" to insert '1' as the response.

I think I am way overthinking this and actually causing confusion that isn't needed.  



0
 
nmaranoAuthor Commented:
agx-

Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.