[FIXED] How update stock of product being purchased

Issue

I am currently working on a store page and I am using PayPal Smart Button to carry out payments. Once the payment is approved, my stock number reduces. When I make a payment however, instead of reducing the stock of the item being purchased, it reduces the stock of every item on the database. What can I do in order to reduce the stock of only the item being purchased?

Here is my code.

view_product.php:

<?php
include 'pdo_connect.php';

$product_id = $_GET['product'];

$stmt = $db->prepare("SELECT * FROM products Where id = :id");
$stmt->bindParam(':id', $product_id );
$stmt->execute();

    while($row = $stmt->fetch()){
        $stock = $row['stock'];
        $id = $row['ID'];
        if ($stock > 0){
        echo 
        "<script>
        paypal.Buttons({

            style: {
                shape: 'rect',
                color: 'blue',
                layout: 'vertical',
                label: 'paypal',
                locale: 'en_CY'
            },
        
            createOrder: async function(data, actions) {
                let stock = (await fetch('get_current_stock.php')).json();
                let currentStock = stock['current'];
        
                //may use amoutTryingToOrder instead of 1
                if (currentStock < 1) {
                    alert('Out of stock, sorry :(');
                    return false;
                }
                return actions.order.create({
                    purchase_units: [{
                        amount: {
                            value: ". $row['paypal_price'] .",
                            currency: 'EUR'
                        }
                    }]
                });
            },
            onApprove: function(data, actions) {
                return actions.order.capture().then(function(details) {
                    //alert('Transaction completed by ' + details.payer.name.given_name + '!');
                    alert('Your payment has been processed!');
                    localStorage.clear();
                    window.location.href = 'http://localhost/website/thankyou.php';
                    
        
                    const formData = new FormData();
                    formData.append('amountOrdred', 1);
                    
                    fetch('update_stock.php', {
                        method: 'POST',
                        body: formData
                    });
                })
            }
        }).render('#paypal-button-container');
      </script>";
    }
    else{
    echo "Out of stock";
    }
  }
?>

update_stock.php:

<?php
include 'pdo_connect.php';

    $id = $_GET['ID'];

    $sql = "UPDATE products SET stock = stock - 1 WHERE stock > 0 and ID = :id";

    
    $stmt = $db->prepare($sql);
    $stmt->bindParam(':id', $id);
    $stmt->execute(); 

    $_POST['amountOrdered'];
    
?>

Thank you in advance!

Solution

This is not valid SQL:

UPDATE products WHERE id = :id SET stock = stock - 1 WHERE stock > 0

There are two WHERE clauses in this query, which SQL does not allow. You probably mean:

UPDATE products SET stock = stock - 1 WHERE stock > 0 and id = :id

Answered By - GMB

Answer Checked By - Robin (PHPFixing Admin)