eCart solution to inventory control

It's been a few weeks since I did this, but I'm sure I can pick it up again.

I did this for paypal standard payments, but I think the same principles would apply whatever payment gateway is used.

What I can certainly do is give you a few pointers to help you achieve your objective. (Using php/mysql)

My Method...

To ensure that the stock level was updated ONLY when the order was completed and successful payment made, I run the update query on the stock table on the paypal IPN page.

In a nutshell, on my IPN page, there is a section which checks for a valid IPN, which then adds rows to the paypal payment and paypal cart tables.

In this section I run two additional queries...

the first loops through the cart items and sets the new stock qty for each item
the second updates the order status on my orders table.

In this instance, I don't need to worry about colours and sizes, so stock qty is held in my items table.
For your example, you would be updating a stock table

the query I used to update the inventory was something like this...

<?php

// initialize flag
$OK = false;
// create database connection
$conn = dbConnect('admin');

// Now update stock level in tblitem

for ($i = 1; $i <= $num_cart_items; $i++) {
$itemnumber = "item_number".$i;
$quantity = "quantity".$i;

$sqlstock = 'UPDATE tblitem SET StockQty = StockQty - ? WHERE ItemID = ?' ;


// initialize prepared statement
$stmt = $conn->stmt_init();
if ($stmt->prepare($sqlstock)) {
// bind parameters and execute statment
//$var1 = 'StockQty - '.$_POST[$quantity];
//$var2 = $_POST[$itemnumber];

$var1= $_POST[$quantity];
$var2 = $_POST[$itemnumber];



$stmt->bind_param('ii', $var1, $var2);
$OK = $stmt->execute();
}
}
// redirect if successful or display error
if ($OK) {
$result = ' Stock Level Updated ';
$result = nl2br($result);
}
else {
$result = $stmt->error;
}

?>

This uses the unique itemID to update the stock qty, you would need to change the parameters to filter for colour and size

I hope this helps.

Regards

Craig