11 Replies Latest reply: Mar 1, 2012 9:59 AM by Inkfast RSS

    Attempting query update from array

    Inkfast Community Member

      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

        • 1. Re: Attempting query update from array
          Dan Bracuk Community Member

          What exactly happens when you run that code?

          • 2. Re: Attempting query update from array
            Inkfast Community Member

            Hi Dan,

             

            First let me update the code to it's current state since the post;

             

            <cfloop index="x" from="1" to="#ArrayLen(arr1)#">

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

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

             

            Right now I'm getting "Object of type class coldfusion.runtime.Struct cannot be used as an array" in the second loop but an example I found worked perfectly on the old CF7 server that we're stuck with on this website. It seems that looping 2D arrays is a pain to do.

             

            Thanks for taking a look.

            • 3. Re: Attempting query update from array
              Dan Bracuk Community Member

              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?

              • 4. Re: Attempting query update from array
                Inkfast Community Member

                The info comes from repeating form fields in another page creating the strings, it's CartWeaver and I can't change that aspect or I would. I turned that into an array and thought it would be easy to loop through and cfquery/update the existing table.

                 

                Since there is more than one string I don't see any other way of doing this at the moment, I've been on it for a long time already and this error is at the very end of about 230 lines so I'm not giving up on it.. When I figure it out, I'll post it all over the darn place!

                • 5. Re: Attempting query update from array
                  Dan Bracuk Community Member

                  How are the form fields named?

                  • 6. Re: Attempting query update from array
                    Inkfast Community Member

                    "sel" and "option_Type_ID" to match the data table. See something I missed?

                    • 7. Re: Attempting query update from array
                      Dan Bracuk Community Member

                      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?

                      • 8. Re: Attempting query update from array
                        Inkfast Community Member

                        Here's what I have, I think I tried that and got an error.

                         

                        <cfscript>

                        arr1 = ARRAYNEW(1);

                        arr1[1][1] = "#listgetat(form.optiontype_ID, 1)#";

                        arr1[1][2] = "#listgetat(form.sel, 1)#";

                        arr1[2][1] = "#listgetat(form.optiontype_ID, 2)#";

                        arr1[2][2] = "#listgetat(form.sel, 2)#";

                        </cfscript>

                         

                        <cfloop index="x" from="1" to="#ArrayLen(arr1)#">

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

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

                        • 9. Re: Attempting query update from array
                          Dan Bracuk Community Member

                          Try something like this:

                           

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

                          update query goes here

                          </cfloop>

                          • 10. Re: Attempting query update from array
                            insuractive Community Member

                            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.

                            • 11. Re: Attempting query update from array
                              Inkfast Community Member

                              I haven't seen any examples like this anywhere. I think all of the examples that I have been trying to follow may have worked on older versions of CF. I'll give this a try and report back.

                               

                              Thanks for the advice.