Shopping cart loop

Get Started
1

Hi all. I have a small shopping cart application based on Arrays and structures!


<cfif Not structKeyExists( session, 'cart' )>
	<cfset session.cart = arrayNew(1)>
</cfif>
<cfset newitem = 0>
<cfloop index="thisCartItem" from="1" to="#arrayLen( session.cart )#">
    <cfif  session.cart[thisCartItem].productID EQ form.id>
	<cfset session.cart[thisCartItem].quantity = session.cart[thisCartItem].quantity + #form.qnt#>
    <cfset newitem = 1 >
	    <cfbreak>            
	</cfif>
</cfloop>
<cfif newitem EQ 0>
<cfset temp = arrayAppend( session.cart, structNew() )>
<cfset thisCartItem = arraylen( session.cart )>
<cfset session.cart[thisCartItem].productID = form.id>
<cfset session.cart[thisCartItem].name = form.name>
<cfset session.cart[thisCartItem].quantity = form.qnt>
<cfset session.cart[thisCartItem].price = form.price>
</cfif>

the cart itself works great, I’m only struggling with a small adjustment I would like to make. In the products table I have a field inStock (int). At checkout I would like to update(decrease) this field for each product in the shopping cart with the quantity of each product in the cart.

For example:

In Database
Product1 inStock 10
Product5 inStock 8

In Shopping Cart
Product1 Qnt 3
Product5 Qnt 2

Result after checkout
Product1 inStock 7
Product5 inStock 6

For a few hours now I’m playing arround with this and have the Idea that I’m near, but I’n not near enough because It isn’t working. :rolleyes: I added this to the page where the order is processed:


<cfloop index="thisCartItem" from="1" to="#arrayLen( session.cart )#">
     <cfquery name="numbersInStock" dataSource="#Request.dsn#"> 
          SELECT p_id, p_instock
         FROM LProducts
         WHERE p_id = #session.cart[thisCartItem].productID#
     </cfquery>
</cfloop>

    <cfset newInStock = numbersInStock.p_instock - session.cart[thisCartItem].quantity />

<cfloop index="ListElement" list="#p_id#" delimiters=" ,">
     <cfquery Name="#thisCartItem#" dataSource="#Request.dsn#">    
         UPDATE LProducts    
        SET inStock = #newInStock#
        WHERE p_id in ( <cfqueryparam cfsqltype="cf_sql_integer" value="#ListElement#" list="true">
     </cfquery>
</cfloop>

But like I said it isn’t working. Does anyone see where my thinking is wrong.

Thank you in advance.

2

<cfquery result=“stockUpdate” …>
UPDATE YourTable
SET InStock = InStock - #QuantityFromCart#
WHERE ProductID = #ProductIDFromCart#
AND InStock >= #QuantityFromCart#

Afterward check the stockUpdate.recordCount …

Probably should have emphasized the “result” part …:wink:

3

If you’re running CF8, use the “result” attribute. It should return the number of records modified for UPDATE’s. For earlier versions, you’d have to do something different. The syntax is db dependent. So we’d need to know which one and version you’re using.

Thinking about it, you probably want to use transactions, if you’re not already. It’s recommended for apps that deal w/financial transactions. The standard reasons why is in the Description section (banking app)

So I think you could run the checkout/update loop within a transaction. If there weren’t enough of the ordered item, rollback the transaction and let the user adjust their order. Just remember transactions should always be as minimal as possible. In other words, don’t just stick a cftransaction around the whole page.

There’s an example of a rollback at the bottom of the docs. You could do something similar for the out of stock scenario.
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_t_09.html

4

You don’t need two queries. Just loop through your cart items, and for each item, subtract the quantity from the “inStock” value in an UPDATE query. (Substitute cfqueryparam …). I assume you’ve already added some logic to prevent users from ordering items that aren’t in stock any more too.


UPDATE  YourTable
SET       InStock = InStock - #QuantityFromCart#
WHERE   ProductID = #ProductIDFromCart#

<cfloop index=“thisCartItem” from=“1” to=“#arrayLen( session.cart )#”>
<cfquery name=“numbersInStock” dataSource=“#Request.dsn#”>
SELECT p_id, p_instock
FROM LProducts
WHERE p_id = session.cart[thisCartItem].productID#
</cfquery>
</cfloop>

&lt;cfset newInStock = numbersInStock.p_instock - session.cart[thisCartItem].quantity /&gt;

<cfloop index=“ListElement” list=“#p_id#” delimiters=" ,“>
<cfquery Name=”#thisCartItem#" dataSource=“#Request.dsn#”>
UPDATE LProducts
SET inStock = #newInStock#
WHERE p_id in ( <cfqueryparam cfsqltype=“cf_sql_integer” value=“#ListElement#” list=“true”>
</cfquery>
</cfloop>

BTW: That kind of query is dangerous in multi-threaded apps, because it is vulnerable to race conditions. It’s more noticeable with high volume sites, but can happen in any app under certain conditions.

Say you have 10 “widgets” in stock. Then 2 users both order 5 “widgets” and go to the checkout page at the exact same time. The #numbersInStock# query tells both users the number of widgets in stock is 10. Then the code deducts their 5 widgets:

&lt;cfset newInStock = numbersInStock.p_instock - session.cart[thisCartItem].quantity /&gt;

… and the #newInStock# value for both users is 5 … which is wrong. So after both users checkout, and the UPDATE’s run, the “InStock” value will 5, when it should really be 0.

5

Kewl. Yeah, always better to plan for unexpected and set up things to scale.

6

cfStarlight and It’s working like a charm :slight_smile: Thank you so much. I think I have things coffered now. Like I said before It’s not a huge site, but it’s better to be on the save site :cool:

7

It all depends on your checkout process (what happens and when) and how you want to handle it if the user orders 5 of something and only 2 are available. But a start would be only UPDATE if InStock >= the amount requested

<cfquery result=“stockUpdate” …>
UPDATE YourTable
SET InStock = InStock - #QuantityFromCart#
WHERE ProductID = #ProductIDFromCart#
AND InStock >= #QuantityFromCart#

Afterward check the stockUpdate.recordCount. If the records updated is > 0 there were enough items in stock. Otherwise, there weren’t. So you can reject the order or do some error handling…

8

Thank you very much for your input, very helpful

For what the stock concerns I’m thinking of only showing products that are in stock so that solves something and then on the addtocart/update cart page do some kind of error handeling, i.o.w. Check if the quantity requested is available.

again thank you very much

9 

<cfloop index="thisCartItem" from="1" to="#arrayLen( session.cart )#">
	<cfquery dataSource="#Request.dsn#"> 
		UPDATE LProducts
    	SET p_inStock = p_InStock - #session.cart[thisCartItem].quantity#
    	WHERE p_id = #session.cart[thisCartItem].productID#
	</cfquery>
</cfloop>

For the second part. What would you suggest that I should do? I’t not a huge site but it is possible what you said

Thank you in advance

10

HI cfStarlight,

I’m breaking my head over your last suggestion the recordCount How do I do a recordCound on a update query or do I need to add another query.

I would love to have some kind of message stating that there are not enough items in stock and give the customer the opportunity to go back two steps (cart.cfm) where they can change the number of items and at the same time send a message to the site owner that the a item or certain items are about to go out of stock. You have an idea or do you know of any tutorial dealing with such things

Thank you