Skip to main content
I
Inkfast
Inspiring
February 29, 2012
Answered

Attempting query update from array

  • February 29, 2012
  • 1 reply
  • 2381 views

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

This topic has been closed for replies.
Correct answer Dan_Bracuk

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


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?

1 reply

D
Dan_Bracuk
Inspiring
February 29, 2012

What exactly happens when you run that code?

I
InkfastAuthor
Inspiring
February 29, 2012

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)#">

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

D
Dan_Bracuk
Inspiring
February 29, 2012

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?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices