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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_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
_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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nmaranoAuthor Commented:
agx-

Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.