0

Automatically update quantity in database after purchase is made.

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;

1st Sep 2018, 9:59 AM
Mogammad Shameer Losper
Mogammad Shameer Losper - avatar
8 Answers
+ 1
I just did , thanks , let me get to the solution you offered, i will reply if i encounter problems
1st Sep 2018, 3:20 PM
Mogammad Shameer Losper
Mogammad Shameer Losper - avatar
+ 1
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 } }
2nd Sep 2018, 11:41 PM
Mogammad Shameer Losper
Mogammad Shameer Losper - avatar
+ 1
You're welcome, good to know the problem is solved, good job :D
3rd Sep 2018, 5:13 AM
Ipang
0
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;
1st Sep 2018, 2:23 PM
Ipang
0
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.
1st Sep 2018, 3:08 PM
Mogammad Shameer Losper
Mogammad Shameer Losper - avatar
0
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 : )
1st Sep 2018, 3:11 PM
Ipang
0
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; ?>
1st Sep 2018, 8:13 PM
Mogammad Shameer Losper
Mogammad Shameer Losper - avatar
0
<?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 ... } ?>
2nd Sep 2018, 6:52 AM
Ipang