Automatically update quantity in database after purchase is made.

Question

My code below works when there is one product in the cart. When there is more than one, both quantities of the products are changed to the same value. For example; if i have 2 products. One has a quantity of 5 and the other of 8. When i run the code, both are changed to 4. Please help. I want the code to update each quantity correctly. Here is my code:
$producta = mysqli_fetch_assoc($result);

//if product id in cart equal product id in database

//This is an array going through all the values within array and printing each value

foreach($_SESSION['shopping_cart'] as $key => $product):

$cartID= $product['id'];
$databaseID= $producta['id'];

$quantityFromDatabase= $producta['Quantity'];
$quantityFromCart =$product['quantity'];
$newQuantity=$quantityFromDatabase-$quantityFromCart;

$queryUpdate= "UPDATE products SET Quantity = '$newQuantity' WHERE id='$cartID'";
$result = mysqli_query($connect, $queryUpdate);

endforeach;

Answer 1

I just did , thanks , let me get to the solution you offered, i will reply if i encounter problems

Answer 2

Thank you but i came up with my own solution taking into account some aspects of your code lol:

foreach($_SESSION['shopping_cart'] as $key => $producta){ // go through each product in cart

$tt=$producta['id'];
$sql = "SELECT id, Quantity FROM products WHERE id IN ('$tt')";
$result = mysqli_query($connect, $sql); //execute sql
while($product = mysqli_fetch_assoc($result)){
$r=$product['Quantity']-$producta['quantity'];
$sqlUpdate="UPDATE products SET Quantity = '$r' WHERE id = '$tt'";
$result2 = mysqli_query($connect, $sqlUpdate); //execute sql
}

}

Answer 3

You're welcome, good to know the problem is solved, good job :D

Answer 4

I'm thinking maybe you can try these steps;

* Collect all unique purchased products IDs from the shopping cart in session, then join those IDs as string, with comma as separator, you then use the string with IN clause to fetch only the purchased products' quantity from database.

* Loop through the result set, subtracting in house quantity with purchased quantity and save the new quantity to database.

I haven't seen a foreach loop written the way you did honestly, can you explain why you wrote this way?

foreach():
endforeach;

Answer 5

Hey again lol, I will try your solution now. I usually write my loops like that so i can distinguish clearly where the loop ends. If i have multiple symbols like this in my code- "{" , it can get confusing.

Answer 6

Hi, alright, can I ask you a favour, please undo the thread duplicate, we'll try to sort this out on this one instead, hope you understand : )

Answer 7

Hi , im still playing around with the code but so far it only works with one value, just like my code that i posed. Help please. Code is below:
Even when i put th echo in the loop, it just prints out the same value depending on how many products are in the cart. So if there are two products, it prints the same value twice etc
<?php
session_start();
$cartIDs = array();
$cartQ = array();
$cartQuantity =array();
$x=0;

//Working with cart
foreach($_SESSION['shopping_cart'] as $key => $producta){ // go through each product in cart
$cart[$x++]=$producta['id']; //store each id of product in cart within array
$cartQ[$x++]=$producta['quantity'];
$w=implode(", ", $cartQ ) ;//convert array to string
$z=implode(", ", $cart) ;//convert array to string

//Working with database
$sql= "SELECT Quantity FROM products WHERE id IN ('$z');"; //get matching id's from database
$result = mysqli_query($connect, $sql); //execute sql
while($product = mysqli_fetch_assoc($result)){
$newQ = $product['Quantity']-$w;

}

}
echo $newQ;
?>

Answer 8

<?php
// I don't know how your shopping cart data
// is written in session, this is a simple
// shopping cart data :
// * key is product ID
// * value is array of [price, quantity]
$cart = array(
"p001" => ["price" => 100, "qty" => 8],
"p002" => ["price" => 125, "qty" => 5],
"p003" => ["price" => 150, "qty" => 4],
"p004" => ["price" => 175, "qty" => 3],
"p005" => ["price" => 200, "qty" => 2]
);

// Collect array keys (product IDs) from cart
// Use this for SELECT query with IN clause
$query_filter = implode(',', array_keys($cart));

//Working with database
$sql= "SELECT id, Quantity FROM products WHERE id IN ('$query_filter');";
$result = mysqli_query($connect, $sql);
while($product = mysqli_fetch_assoc($result))
{
$storeQty = $product['Quantity'];
$cartQty = $cart[$product['id']]['qty'];
// Verify $cartQty is valid ...
$newQ = $storeQty - $cartQty;
echo "ID: $product[id] Store: $storeQty Cart: $cartQty Remaining $newQ<br />";
// Update products table ...
}
?>