update quantity of product after checkout

Question

I am trying to update the item quantity after the finished transaction. I have quantity on my item and I wanted to lessen the item quantity depending on the item ordered. the transaction works fine but the problem is the update does not execute and it does not update the quantity of the item in my table.

the code:

<?php
include 'includes/session.php';

if(isset($_GET['confirmation'])){
    $confirmation = $_GET['confirmation'];
    $p_quantity = $_GET['p_quantity'];
    $date = date('Y-m-d');

    $conn = $pdo->open();

    try{
        
        $stmt = $conn->prepare("INSERT INTO sales (user_id, confirmation, status, sales_date) VALUES (:user_id, :confirmation, :status,  :sales_date)");
        $stmt->execute(['user_id'=>$user['id'], 'confirmation'=>$confirmation, 'status'=>1,  'sales_date'=>$date]);
        $salesid = $conn->lastInsertId();

        
                $stmt = $conn->prepare("UPDATE products SET p_quantity=p_quantity-:p_quantity WHERE id=:id");
                $stmt->execute(['p_quantity'=>$p_quantity, 'id'=>$id]);
        
        try{
            $stmt = $conn->prepare("SELECT * FROM cart LEFT JOIN products ON products.id=cart.product_id WHERE user_id=:user_id");
            $stmt->execute(['user_id'=>$user['id']]);

            foreach($stmt as $row){
                $stmt = $conn->prepare("INSERT INTO details (sales_id, product_id, quantity) VALUES (:sales_id, :product_id, :quantity)");
                $stmt->execute(['sales_id'=>$salesid, 'product_id'=>$row['product_id'], 'quantity'=>$row['quantity']]);

            }

            $stmt = $conn->prepare("DELETE FROM cart WHERE user_id=:user_id");
            $stmt->execute(['user_id'=>$user['id']]);



            $_SESSION['success'] = 'Transaction successful. Thank you.';

        }
        catch(PDOException $e){
            $_SESSION['error'] = $e->getMessage();
        }

    }
    catch(PDOException $e){
        $_SESSION['error'] = $e->getMessage();
    }

    $pdo->close();
}

header('location: profile.php');

?>

`$id` appears to be undefined in your code. Done any [debugging](https://www.atatus.com/blog/debugging-in-php/)? Do you have errors and warnings switched on in PHP? — ADyson, Dec 14 '22 at 13:32
Please see https://stackoverflow.com/a/32648423/369143 for information about handling PDO errors. Maybe it will give you more insight about what is going wrong. — Bram Verstraten, Dec 14 '22 at 13:44
Please [edit] your question whenever you have any updates for us. — ADyson, Dec 14 '22 at 14:33
try{ $stmt = $conn->prepare("SELECT * FROM cart LEFT JOIN products ON products.id=cart.product_id WHERE user_id=:user_id"); $stmt->execute(['user_id'=>$user['id']]); foreach($stmt as $row){ $qty = $row['quantity']; $stmt = $conn->prepare("UPDATE products SET p_quantity=p_quantity-'$qty' WHERE id=:id;"); $stmt->execute(['p_quantity'=>$qty, 'id'=>$id]); i tried this query but this error shows Invalid parameter number: number of bound variables does not match number of tokens — Sencho, Dec 14 '22 at 14:34
You forgot to parameterise `$qty` - it should be `:p_quantity` I expect (to match what's in the value array you're passing to execute() — ADyson, Dec 14 '22 at 14:34
Well it's not working when you _don't_ use it either, is it? Your change is a step backwards, not forwards. — ADyson, Dec 14 '22 at 14:36
Like I said at the start, `$id` isn't defined anywhere in your PHP code. That's far more likely to be the root of the problem. Where do you think this value is coming from? — ADyson, Dec 14 '22 at 14:36

update quantity of product after checkout

0 Answers0