Member Avatar for sami.asanga

Hi,

I need to update the product quantity in the product table according to the quantity which the customers ordered...Since I am new to this field I dont know how to do it with a query.

This is what I want to do in more details.As the administrator checks for new order ,he may accept selected orders from the list.I want to update the product quantity according to that orders which the administrator accepted...

Can someone help me to do this??

Thanks alot

  • 2 Contributors
  • 2 Replies
  • 106 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sami.asanga

Dani AI

Generated

wanted the product stock reduced when the administrator accepts selected orders. ’s single-row suggestion points in the right direction, but when multiple orders and multiple products are involved the ordered quantities must be aggregated per product and the adjustments applied atomically.

A safe, common pattern (assumes tables orders, order_items(product_id, quantity, order_id) and products(id, quantity)) is:

START TRANSACTION;

-- quick pre-check: any product lacking enough stock?
SELECT p.id, p.quantity AS stock, t.total_qty
FROM products p
JOIN (
  SELECT product_id, SUM(quantity) AS total_qty
  FROM order_items
  WHERE order_id IN (/* selected order IDs */)
  GROUP BY product_id
) AS t ON p.id = t.product_id
WHERE p.quantity < t.total_qty;

-- if no shortages, deduct all at once
UPDATE products p
JOIN (
  SELECT product_id, SUM(quantity) AS total_qty
  FROM order_items
  WHERE order_id IN (/* selected order IDs */)
  GROUP BY product_id
) AS t ON p.id = t.product_id
SET p.quantity = p.quantity - t.total_qty;

-- mark the orders accepted
UPDATE orders
SET status = 'accepted'
WHERE id IN (/* same selected order IDs */);

COMMIT;

Notes and cautions:

  • Use InnoDB and wrap everything in a transaction to avoid partial work. If the pre-check query returns rows, ROLLBACK and handle shortages.
  • For strict atomicity, perform the update with WHERE p.quantity >= t.total_qty and then compare the number of affected rows to the number of distinct products; if fewer, ROLLBACK.
  • Sanitize the checkbox input: convert posted IDs to integers (no string interpolation into SQL). Use prepared statements (PDO or mysqli) and bind parameters where possible.
  • Consider SELECT ... FOR UPDATE on product rows when multiple concurrent processes might change stock, or implement a reservations column if orders are frequently placed but only some are accepted.

This approach aggregates quantities across selected orders, updates all affected products in one operation, and keeps order-state changes consistent.

Recommended Answers

All 2 Replies

Member Avatar for tesuji

You need something like that:

update products set quantity = quantity - quvalue_from_admin_list 
  where id_quantity = idvalue_from_admin_list

Obviously, in PHP code from your other posting you also need to replace INSERT by above UPDATE ...

-- tesu

Member Avatar for sami.asanga

Can you please give me the query..:)

I want to update multiple products in product table according to the check boxes which the administrator select..I think need to take the SUM quantity from the ordered items which is ORDER BY quantity..Isn't it??

I dont know how to write the query

If you dont mind post me a query here..Great Help..Thanks alot

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.