Updating products stocks in database

Question

I want to update the stock quantities of my products in the DB after a purchase.

My code already works fine, but I want to know if there is a best way to do, with only one SQL statement?

// All the product in the member's cart
if ($stmt = $conn->prepare("SELECT product_id, quantity FROM tbl_cart WHERE member_id = ?")) {
  $stmt->bind_param("i", $memberId);
  $stmt->execute();
  $result = $stmt->get_result();
  $stmt->close();
  $cartItem = $result->fetch_all(MYSQLI_ASSOC);

// Set the quantity after purchase
  foreach ($cartItem as $key => $item) {
    $stmt = $conn->prepare("UPDATE tbl_product SET stock = stock-? WHERE id = ?");
    $stmt->bind_param("ii", $item['quantity'], $item['product_id']);
    $stmt->execute();
    $stmt->close();
  }
}

It would be worth looking at https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query which shows how to do an `UPDATE... SELECT...` — Nigel Ren, Aug 02 '19 at 18:21
I don't see how or why you would do it with 1 sql statement. Your stock and id parameters change for each cart item. — Jack Albright, Aug 02 '19 at 18:22

score 0 · Answer 1 · answered Aug 02 '19 at 18:26

I don't know if this is better way but you may try this Sql statement.

UPDATE tbl_product 
SET stock = stock - ( SELECT quantity FROM tbl_cart 
WHERE product_id = tbl_product.id AND member_id = ? ) 
WHERE id IN ( SELECT product_id FROM tbl_cart WHERE member_id = ? )

score 0 · Answer 2 · answered Aug 02 '19 at 18:59

You can try using trigger function. Look here: mysql after insert trigger which updates another table's column

You could update other column when order is placed.

In my opinion, the trigger function is not the best option, because it will move logic to other place.

But it is one of the alternative, that you ask for.

score 0 · Accepted Answer · answered Aug 02 '19 at 22:57

0

That's the solution I found. I think it's just more clean.

$stmt = $conn->prepare("UPDATE tbl_product AS p 
                        JOIN tbl_cart AS c
                        ON p.id = c.product_id 
                        SET p.stock = p.stock-c.quantity
                        WHERE c.member_id = ?");
  $stmt->bind_param("i", $memberId);
  $stmt->execute();
  $stmt->close();

Thank you for your help.

answered Aug 02 '19 at 22:57

adamanyn

35
6

This is nearly the same as my [answer](https://stackoverflow.com/a/56972860/1422451) on your previous question. – Parfait Aug 03 '19 at 00:51

Updating products stocks in database

3 Answers3