UPDATE Stock table quantity based on order, and stock id

by

I’m having trouble creating a sql statement that will update the quantityonhand based on the order placed on my website (fake order),

What I have right now:

function updateStock($databaseConnection, $stockItemID, $amount){
    $Query = "UPDATE stockitemholdings
            SET QuantityOnHand = QuantityOnHand-$newQuantity
            WHERE StockItemID= ";

    $Statement = mysqli_prepare($databaseConnection, $Query);
    mysqli_stmt_bind_param($Statement, "ii", $amount, $stockItemID);
    mysqli_stmt_execute($Statement);
    $rowsChanged = mysqli_stmt_get_result($Statement);
?>

What it is now:

Grapes: StockItemID= 1     QuantityOnHand=175609
Apples :             2                    165538
Bananas:             3                    253190
Kiwi's               4                    208109
Grapefruit           5                    199064
Pear                 6                    196995

the order:

10 apples, 10 bananas, 10 kiwi’s, 10 grapefruit, 10 pears, 10 grapes.

What it needs to be after

QuantityOnHand= 175599
                165528
                253180 
                208099
                199054
                196985

  • this question is in severe need of editing fixes. anyway, did you forget php tag?

    – 

  • if you really need in SQL statement then remove all PHP code which is excess and unrelated.

    – 

  • the order: 10 apples, 10 bananas, 10 kiwi’s, 10 grapefruit, 10 pears, 10 grapes. How do you provide this data into SQL query? in what format?

    – 

  • Your code is generating a Warning: Undefined variable $newQuantity, you should fix that

    – 

  • @BagusTesa, yes I did forget the PHP tag.

    – 

Leave a Comment