Skip navigation
Currently Being Moderated

Attempting query update from array

Feb 29, 2012 1:41 PM

I'm attempting to update a table from an array by looping it over but having no luck. There doesn't seem to be much info out there on how to do this, everything I've found is related to looping queries into arrays (the reverse obviously). It's seems like it should be simple enough the test array is;

 

(sel, optiontype_ID)

 

[1] [1] Blue

[1] [2] 65

[2] [1] White

[2] [2] 73

 

And my attempt.....

 

<cfloop index="x" from="1" to="#arrayLen(arr1[sel])#">

<cfloop index="y" from="1" to="#arrayLen(arr1[optiontype_ID])#">

<cfquery datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">

update tbl_skuoptions

set option_name = '#arr1[sel]#'

where option_Type_ID = '#arr1[optiontype_ID]#'

</cfquery>

</cfloop>

</cfloop>

 

Any ideas or even a slap upside the head from Dan would be appreciated.

Thanks!

David

 
Replies
  • Currently Being Moderated
    Feb 29, 2012 2:34 PM   in reply to Inkfast

    What exactly happens when you run that code?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 29, 2012 3:41 PM   in reply to Inkfast

    Looping through 2D arrays is such a pain it's rare that I ever attempt it.  Queries are much easier to deal with.  On that note, from where did the data for your array come in the first place?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 29, 2012 3:55 PM   in reply to Inkfast

    How are the form fields named?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 29, 2012 4:47 PM   in reply to Inkfast

    You have to be doing something to build your array.  At the stage when you populate each row, couldn't you update your db instead?

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 29, 2012 5:08 PM   in reply to Inkfast

    Try something like this:

     

    <cfloop from = "1" to = "#ListLen(form.sel)#" index = "idx">

    update query goes here

    </cfloop>

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 1, 2012 9:52 AM   in reply to Dan Bracuk

    It seems like the core of your problem with your implementation has to do with how you are implementing your loop.  You can use the "Array" attribute of <cfloop> to loop over your array and have your index be the CF contents of each array position (rather than having to do everything by position reference as you attempted to do in your example)

     

    Try something like this:

    <cfloop array="#arr1#" index="arrInner">
    <cfquery datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">

    update tbl_skuoptions

    set option_name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arrInner[1]#">

    where option_Type_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arrInner[2]#">
    </cfloop>

     

    I threw in some <cfqueryparam> goodness as well.  Of course, this assumes that array positions 1 & 2 of your inner array are simple data types.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points