update stock qty using mysqli
I am pretty familiar with the WA Mysqli extension, but I cant work out the syntax for this one.
on a checkout success page, i want to loop through the cart content and update the stock quantity of all items purchased.
it is pretty straightforward to update the stock qty field to a set value, but i want it updated to a calculated value, ie current stock qty - qty purchased
Can someone please help me determine what syntax I need for the 3rd input ($paramValue)
//loop through the cart so we know what ItemIDs have been purchased
// update the quantities in table
$inthecart = '';
$inthecartqty = '';
while (!$schoolshopcart->EOF()) {
$inthecart = $schoolshopcart->DisplayInfo("ID");
$inthecartqty = $schoolshopcart->DisplayInfo("Quantity");
$UpdateQuery = new WA_MySQLi_Query($PowerCMSConnection_i);
$UpdateQuery->Action = "update";
$UpdateQuery->Table = "tblitemculford";
$UpdateQuery->bindColumn("ItemQty", "i", $paramValue, "WA_DEFAULT");
$UpdateQuery->addFilter("ItemID", "=", "i", "".($inthecart) ."");
$UpdateQuery->execute();
$UpdateGoTo = "";
if (function_exists("rel2abs")) $UpdateGoTo = $UpdateGoTo?rel2abs($UpdateGoTo,dirname(__FILE__)):"";
$UpdateQuery->redirect($UpdateGoTo);
$schoolshopcart->MoveNext();
}
$schoolshopcart->MoveFirst();
I tried $UpdateQuery->bindColumn("ItemQty", "i", "ItemQty" - $inthecartqty, "WA_DEFAULT");
but this doesn;t bind the field in the calculation
the sql statement i am essentially trying to generate looks like this...
UPDATE tblitemculford SET ItemQty = ItemQty - ".$inthecartqty." WHERE ItemID =".$inthecart