Attempting query update from array

[Inkfast]

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

[Dan_Bracuk]

What exactly happens when you run that code?

[Inkfast]

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.

[Dan_Bracuk]

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?

[Inkfast]

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!

[Dan_Bracuk]

How are the form fields named?

[Inkfast]

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

[Dan_Bracuk · Accepted solution]

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?

[Inkfast]

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

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

[Dan_Bracuk]

Try something like this:

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

update query goes here

</cfloop>

[insuractive]

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.

[Inkfast]

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.