coldfusion Shop cart Confusion

Report · Aug 24, 2008

Hi I am building shopping cart with Micheal's Tutorial Using Arrays and sessions. But i am stuck at the point where i need to store the order details in the database before the payment button is clicked and actually it go to paypal to process the order:

i am doing something like this.

<cfquery name="addOrder" datasource="#application.dsn#">
INSERT INTO orders (o_cust, o_total, o_items,o_quantity,ipaddress)
VALUES (
<cfqueryparam cfsqltype="cf_sql_numeric" value="#logMem.c_id#">,
<cfqueryparam cfsqltype="cf_sql_double" value="#variables.totalprice#">,
<cfoutput>
<cfloop from="1" to="#arrayLen(session.shoppingcart)#" index="i">
<cfif i eq arrayLen(session.shoppingcart)>
<cfqueryparam cfsqltype="cf_sql_varchar" value="#session.shoppingcart.name#">
<cfelse>
<cfqueryparam cfsqltype="cf_sql_varchar" value="#session.shoppingcart.name#">
</cfif>
</cfloop>
</cfoutput>,
<cfoutput>
<cfloop from="1" to="#arrayLen(session.shoppingcart)#" index="i">
<cfif i eq arrayLen(session.shoppingcart)>
<cfqueryparam cfsqltype="cf_sql_varchar" value="#session.shoppingcart.quantity#">
<cfelse>
<cfqueryparam cfsqltype="cf_sql_varchar" value="#session.shoppingcart.quantity#">
</cfif>
</cfloop>
</cfoutput>,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#CGI.REMOTE_ADDR#">)
</cfquery>

if i enter as above it creates only 1 row and enters the info like:

o_items as product1product2 and o_quantity as: 23

and i am unable to identify the products and quantity through database as it looks like item product1product2 has been purchased with quantity 23

can anyone guide how can i do it..

If you can Suggest e database change, Pease reply , I will do that because this is something confusing me last 3 days

Cheers

Report · Aug 25, 2008

I suggest a database change. The orders table would be something like,

order_id (primary key)
customer_id (foreign key)
date
other fields.

Then I would have an order_item table looking something like this.

order_id
item_id
quanity

Where the primary key is the order_id and item_id.

If I were doing it for real, I would also do something about the fact that the price of an item changes over time.