Tracking Shopping Cart Inventory

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I am at a total loss as to how to accomplish a feature in a shopping
cart. (I am using PHP5 and MySQL)

Basically, when someone adds an item to their cart, it should alter
the inventory to reflect it. That part i have fine, but the problem
is, that should a person decide not to complete their purchase, the
level should go back up. So the decrease in available stock should go
down only temporarily, until checkout.

The actual transaction is through PayPal, which complicates things,
cause let's say someone quits halfway on the paypal site, i'd have no
way to trigger a script or something like that. Or, even if they go
through a transaction, a user may not return to the site to trigger a
confirmation script.

I have been reasearching all day, and it just baffles me. I had
considered a type of "users online" type of script, but i guess the
logic just baffles me. The poimt of this is to avoid overselling

So yeah, if anyone can help by pointing me to a script or just give
some logic on how i can keep track of inventory this way, that would
be fantastic.

Re: Tracking Shopping Cart Inventory

Quoted text here. Click to load it

of course, is this is also impossible, that would actually help me
sleep a little better :)

Re: Tracking Shopping Cart Inventory

Quoted text here. Click to load it

I'll disagree with that, somewhat.  You should have several inventory
statistics, including (a) number of the item on hand (adjusted when
you ship orders and when new stock arrives and when items are
returned, if they are not defective), (b) number of the item on
hand minus pending orders (a pending order is one where the order
is COMPLETED by the customer but has not shipped yet.  Perhaps it's
waiting for credit card validation or for someone to pull the item
from the warehouse and ship it.), and perhaps (c) number of the
item on hand minus pending orders minus items in the shopping cart
for orders not yet entered.

You should be able to identify the difference between (a) and (b)
with order numbers and quantities for orders that haven't shipped
yet.  Perhaps rather than keeping a running total, you should be
computing this value on the fly.  That also means you don't fiddle
with inventory numbers when an order that didn't ship is cancelled.

Quoted text here. Click to load it

You should be able to account for the difference between (c) and
(b) by each individual shopping cart.  I'll recommend computing
this on the fly by summing the amounts in each individual shopping
cart.  If a shopping cart gets too old - you don't want to permanently
reserve merchandise for someone who puts an item in a shopping cart,
then dies or never visits your site again because they found a better
price elsewhere - then it doesn't count any more.

Perhaps you should be computing this on the fly also:  if it's in
a shopping cart that's been modified less than, say, 24 hours ago,
deduct this from available inventory.  No action need be taken when
the shopping ages over 24 hours, as you automatically quit counting
    SELECT sum(qty) FROM shopping_cart_item_table
    WHERE part_no='$part_number' and cart_timestamp > subdate(now(), 1);

If the user modifies an old shopping cart, making it new again,
perhaps he should be warned that the item sold out in the mean time
if that happened.

Quoted text here. Click to load it

I know there's something about the way Paypal works that permits
you to trigger *SOMETHING* that says "this guy completed submitting
his order and paid for it:  NOW SHIP IT".  (This doesn't address
later disputing the charge with Paypal, but it does address basic
authorizing a charge in the first place).  If you don't have that
ability you shouldn't be doing e-commerce via Paypal.  I thought
Paypal did this with callbacks to your web server.  I doubt Paypal
would survive if it didn't have some way to tell the merchant
securely whether the customer paid or not.

Quoted text here. Click to load it

Users do not log out.  Not dependably, anyway.  You need to be
careful about the inventory cost of merchandise reserved by long-gone
customers.  You should also seriously consider whether you want to
sell an item to a user who is checking out vs. saving it for one
who put it in his shopping cart last week and hasn't logged in

Quoted text here. Click to load it

Oh, yes, you should allow users to backorder merchandise (assuming
it's not discontinued) rather than making them log in every hour
to try to get one.  Warn them, perhaps giving a time estimate, but
if the user agrees, let him backorder.

Re: Tracking Shopping Cart Inventory

thank you both very much, this helps clear up quite a few things for

Re: Tracking Shopping Cart Inventory

On Mon, 03 Nov 2008 16:48:05 -0600, (Gordon
Burditt) wrote in

Quoted text here. Click to load it

[snip distinguishing between a) on-hand inventory and b) pending

Quoted text here. Click to load it

[expanding on this idea ...]

I'm a big proponent of not storing calculated values under most
circumstances.  A lot of people do it because they worry about
performance, but any modern RDBMS should be able to calculate
something as simple as this extremely quickly, assuming a properly
designed and indexed database.

Storing calculated values actually adds complexity in many situations,
as you have to insert or update the line-item record and then update
the summary record.  Also, having a single record in the database that
must be updated any time there is a relevant transaction can lead to
resource contention issues if you have a sufficient number of
transactions, or even errors if the update is done improperly.

// Get the current total of available widgets, which is inventory
// on-hand less widgets in pending orders
$query_current_count = "SELECT value FROM inventory_summary WHERE name
= 'widgets_available'";
$res_current_count = mysql_query($query_current_count);
$widgets_available = mysql_result($res_current_count, 0, 0);
// Calculate the new value.  $widgets_in_order already has
// the value from the order.
$widgets_available = $widgets_available - $widgets_in_order;
$query_update = "UPDATE inventory_summary SET value =
$widgets_available WHERE name = 'widgets_available'";
$res_update = mysql_query($query_update);

I've seen a lot of code like this.  The fatal flaw here is that you
can't be sure that the value hasn't been changed between the SELECT
statement and the UPDATE statement, so you can't be sure that the new
total is correct.

You can, of course, do this:

$query_update = "UPDATE inventory_summary SET value = (value -
$widgets_in_order) WHERE name = 'widgets_available')";

which addresses the issue of errors, though not resource contention,
but I find that it's usually better to avoid the problem completely
and just make inventory_summary a view that calculates all of the
values you need on the fly.

Also, in the example above, what do you do if the update to
inventory_summary fails?  You'd have to wrap the whole thing in a
transaction (assuming that you're using an RDBMS that supports them,
unlike, say MySQL with MyISAM).

[snip rest]
Charles Calvert             |  Web-site Design/Development
Celtic Wolf, Inc.           |  Software Design/Development |  Data Conversion
(703) 580-0210              |  Project Management

Re: Tracking Shopping Cart Inventory

themayanlion wrote:
Quoted text here. Click to load it

Go ahead and decrement the total, and add it to your orders table.  Keep
the order marked as "Payment Pending", with a timestamp for the last
update to the order (or maybe update it every time they do something on
the site - in case they look around for a while).

Use PayPal's IPN (Instant Payment Notification) to post the payment
information back to your site.  If they complete the payment, you will
get a notification from PayPal (warning - sometimes this takes a
while!), mark the order as "Paid" and let it continue processing.

If they don't complete the order, you will never get notification from
PayPal.  This is where the timestamp field comes in.  If an order has
been in "Payment Pending" status for a while(1), mark it as rejected and
add the items back to your inventory.

(1) - Don't set the timeout value too short.  Normally PayPal's IPN
returns almost immediately.  However, when they take PayPal down for
maintenance, people can typically still make the payment, but the
notification will be queued and sent later.  If enough get queued, it
can take several hours for the backlog to get cleared up.  I've seen
delays of 1-2 hours after such an action.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline