I couldn't get anywhere with that direction so I've gone back to a hopefully simpler approach described by Sades but need help finishing it off:
I've created a new field in my items table called Inventory then in the eCart calculations panel I added a new formula called substractions with this formula:
[Inventory] - [Quantity] so creating the formula substractions = Inventory - Quantity,
On the product page I open the "Add to cart button" server behavior and I bind the Inventory field to the Inventory column
On the checkout_success page, I add eCart display manager for a receipt, and add the following code near <?php echo $eCart->DisplayInfo("Name"); ?>
to update the Inventory
<?php
$FinalStock = $eCart1->DisplayInfo("substractions");
$IDItem = $eCart1->DisplayInfo("ID");
mysql_select_db($database_connTEST, $connTEST);
$query_substractions = "UPDATE products SET Inventory = $FinalStock WHERE ID = $IDItem" ;
$substractions = mysql_query($query_substractions, $connTEST) or die(mysql_error());
?>
Idea is when the success pages loads it subtracts the order quantity from the Inventory field.
But I'm getting a sql error "You have an error in your SQL syntax near 'WHERE LampID = etc'
Any idea if this should work and what I might be missing in this code?
