Looping an array into a DB

[lewiscob]

I am building a shopping cart web application based on the 3 part tutorial on here but I have found that the when adding the items of an order it looks through the whole array and saves it into one DB field and looses the quantity of each item and subtotal for each item.

I need it so it loops through the array and saves item name, quantity and subtotal for each item in seperate DB rows. I was thinking I would need to change the DB fields to something like:

itemname1
itemname2
itemname3
itemprice1
itemprice2
itemprice3
itemquantity1
itemquantity2
itemquantity3
itemtotal1
itemtotal2
itemtotal3

etc.

Can anyone offer any help on how i can loop through the array to make it do this or if you have any other ways I can go about it?

Thansk

Lewis

[chrisbowyer]

This will do it...

<cfset myArray = ArrayNew(2) />
<cfset myArray[1][1] = "apples" />
<cfset myArray[1][2] = "bananas" />
<cfset myArray[1][3] = "cherries" />
<cfset myArray[2][1] = "1.00" />
<cfset myArray[2][2] = "2.00" />
<cfset myArray[2][3] = "3.00" />
<cfset myArray[3][1] = "10" />
<cfset myArray[3][2] = "20" />
<cfset myArray[3][3] = "30" />
<cfset myArray[4][1] = "10.00" />
<cfset myArray[4][2] = "40.00" />
<cfset myArray[4][3] = "90.00" />

<cfloop index="i" from="1" to="#ArrayLen(myArray)#" step="4">
    <cfloop index="j" from="1" to="#ArrayLen(myArray)#">
        <cfquery datasource="#request.dsn#">
        INSERT INTO
            ITEM
        (
            ITEMNAME,
            ITEMPRICE,
            ITEMQUANTITY,
            ITEMTOTAL
        )
        VALUES
        (
            '#myArray[1]#',
            #myArray[2]#,
            #myArray[3]#,
            #myArray[4]#
        )
        </cfquery>
    </cfloop>
</cfloop>

[Adam Cameron.]

I don't believe a two-dimensional array is the most appropriate data-type for the job here.  There is a clear sequential relationship between items in the cart, so it makes sense for the items to be an array, but there is no sequential relationship between name, price, quantity, total.  Indeed each element has a clear name ("name", "price", etc): that's more describing a struct, to me.  So I would alter the logic to use an array of structs, eg:

item.name

item.price

item.quantity

item.total

I haven't often come across a situation in which a two-dimensional array is the best fit for the data presented.  It happens, but not often.  So if I found myself thinking "tw-dimensional array", I'd re-assess what I'm doing.

Of course another approach here would be to store the data in a query:

item.name

item.price

item.quantity

item.total

Depending on how the data's going to be used, that might be a better solution.

--

Adam

[BKBK]

One way might be to store the array as a session variable. Then it wont get lost. For example,

<cfset session.order = arrayNew(2)>

<!--- for product item 1 --->

<cfset session.order[1][1] = orderID>

<cfset session.order[1][2] = itemname1>

<cfset session.order[1][3] = itemname2>

... etc

<!--- for product item 2 --->

<cfset session.order[2][1] = orderID>

<cfset session.order[2][2] = itemname1>

<cfset session.order[2][3] = itemname2>

... etc

OrderID is the primary key of the database table.

[-__cfSearching__-]

saves it into one DB field

If you mean storing a delimited list like: "1,5,6,8" in a single column ... definitely do not do that.

I was thinking I would need to change the DB fields to

something like:

itemname1

itemname2

itemname3

...

It sounds like you could also use some assistance on the table structure. When you find yourself creating the same column name over and over, only differentiated by a number (like Widget1,Widget2,Widget...), the data is better represented as separate records. Then you can have as few or as many entries as needed.

Columns: ItemID (FK), ItemQuantity, ItemPrice, ItemTotal