Inventory count of my database keeps starting at 28?

Question

I am new to coding and in my code and i am having trouble updating my database. I am using mysql and php to create a shopping cart that updates inventory when an item is purchased. The cart takes the product id of the item and stores it using the GET array. The problem is that after someone purchases an item, the inventory doesn't adjust probably in my database. the inventory count always starts at 28 every time I purchase an item instead of starting at the proper inventory amount. The code for the shopping cart is:

require 'connect.php';
require 'item.php';
  if(isset($_GET['id'])){
  $result = mysqli_query($con, 'select * from products where 
  id='.$_GET['id']);
  $products = mysqli_fetch_object($result);
  $item = new Item();
  $item->id = $products->id;
 $item->name = $products->name;
  $item->price = $products->price;
  $item->quantity = 1;
  $_SESSIONS['id']=$_GET['id'];
  // Check if the products exists in the cart
  $index = -1;
  $cart = unserialize(serialize($_SESSION['cart']));
 for($i=0; $i<count($cart); $i++)
  if($cart[$i]->id==$_GET['id'])
  {
  $index = $i;
  break;
  }
if($index==-1)
 $_SESSION['cart'][] = $item;
else{
$cart[$index]->quantity++;
$_SESSION['cart'] = $cart;
}

}

// Delete products in cart
if(isset($_GET['index'])){
  $cart = unserialize(serialize($_SESSION['cart']));
  unset($cart[$_GET['index']]);
  $cart = array_values($cart);
  $_SESSION['cart'] = $cart;
  }  

 if(array_key_exists('submit2', $_POST))
   {  
 $results = mysqli_query($con, "select * from products"); 
 $sql="";//init
 while($products=mysqli_fetch_object($results)){

 for($i=0; $i<count($cart); $i++){    
 $idcart=$cart[$i]->id;
 $quantity= $products->quantity;
 $cartquantity = $cart[$i]-> quantity;
 $sql= "UPDATE products SET quantity='$quantity' - ".$cartquantity." WHERE id='$idcart';";//u need this to append the query
session_destroy(); }//end for
}//end while
if ($con->multi_query($sql) === TRUE) {//use multi_query
header("location: thankyou.php");
} else {
echo "Error updating record: " . $con->error;
}
 $con->close();

I believe the problem is in this code here. $sql= "UPDATE products SET quantity='$quantity' - ".$cartquantity." WHERE id='$idcart';";. Any help will be appreciated. Thank you!

Answer 1

The whole logic around updating the cart quantities seems to be unusual. You have a loop which loops over all of the products and then updates a somehow related database record based on the id from the cart with the quantity from the product. There is no attempt to match the quantity from the product ID with the ID from the cart.

This code just loops through the cart and reduces the quantity of the matching product for each row, it also prepares the statement before the loop and just executes it once for each row...

if(array_key_exists('submit2', $_POST))
{
    $sql = "UPDATE products 
                SET quantity = quantity - ?  
                WHERE id= ?";
    $stmt = $conn->prepare($sql);
    foreach ( $cart as $item ){
        $stmt->bind_param('ii', $item->quantity, $item->id );

        $stmt->execute();
    }//end foreach
    session_destroy();
}//end if