Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CFLOOP multiple form variables in a cfquery insert or update.

Posted on 2006-05-25
6
Medium Priority
?
290 Views
Last Modified: 2013-12-24
Form variables below, QUESTIONID and QUESTION are not looping (and others not shown here for simplicity).  Of course they are not because I don't  know how!  I've only been able to get  ANSWER(answer_index) to loop. How does one loop multiple variables and INSERT OR UPDATE them into a table? (cf5 and sql2000).

<cfloop index="field" list="#structKeyList(form)#"> gives me all the variables but I have no idea how to use it in a CFQuery insert or update.

 If you'd help, please modify my code so it will make some sense to this amateur. I really need help on this ASAP and will be much appreciated!

<cfloop index="answer_index" list="#form.answer#">
  <cfquery name="CreateAnswerInfo" datasource="survey">
    insert into survey_user_answers            
     (
        username,
        surveyid,
        questionid,  <!--- this needs to loop --->
        question,    <!--- this needs to loop --->
        answer,     <!--- this is looping correctly --->
        guid
    )
     VALUES
   (
      '#form.username#',
      '#form.surveyid#',
      '#form.questionid#',
      '#form.question#',
      '#answer_index#',  <!--- only this one loops --->
      '#form.guid#'
   )
  </cfquery>
</cfloop>    
0
Comment
Question by:RollinNow
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 16766320
RollinNow,

Are all lists of equal lengths, ie 10 entries in questions = 10 entries in anwers?

Regards
Plucka
0
 

Author Comment

by:RollinNow
ID: 16766328
For each survey, they are all equal. There may be other surveys, using the same code, with different amount of questions. But for each survey, all form variables I need to loop, will have the same count. Is that what you mean?
0
 

Author Comment

by:RollinNow
ID: 16766675
So, yes, 10 entries in questions = 10 entries in anwers, etc.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 18

Accepted Solution

by:
Plucka earned 2000 total points
ID: 16766722
Ok,

Here is an example, of one way there are many ways.

<cfset questions="One,Two,Three,Four" />
<cfset answers="Yes,No,No,Yes" />

<cfset qArray = listToArray(questions) />
<cfset aArray = listToArray(answers) />

<cfloop index="i" from="1" to="#arrayLen(qArray)#">
    <cfquery name="insert" datasource="blah">
        insert into myTable
        ( question, answer)
        values
        ( '#qArray[i]#', '#aArray[i]#' )
    </cfquery>
</cfloop>


Make sense?
0
 
LVL 18

Expert Comment

by:Plucka
ID: 16766728
You can do the same without arrays if you like

<cfset questions="One,Two,Three,Four" />
<cfset answers="Yes,No,No,Yes" />

<cfloop index="i" from="1" to="#listLen(questions)#">
    <cfquery name="insert" datasource="blah">
        insert into myTable
        ( question, answer)
        values
        ( '#listGetAt(questions, i)#', '#listGetAt(answers, i)#' )
    </cfquery>
</cfloop>
0
 

Author Comment

by:RollinNow
ID: 16771359
That did it and finally clicked home, into my brain. Thanks for the lesson. This has been a tough one for me to grasp. Now I think I've got it.

I used the Array version and will just expand on it. Thanks for taking the time and staying with this. I'm giving you both questions.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question