Updating sql values ​​with an array that have a couple of times occurring elements

Question

While I am coding a trading site, I need to update the product stock. But the fact is that, of course, a shopping basket can have the same items a couple of times. What is the best way to update it?

I tried IN , but the following SQL query returns 3 elements.

 SELECT * FROM `products` WHERE id IN ( 3, 4, 4, 6 ) LIMIT 0 , 30 

Here is my solution, but I don’t think it is the best.

 $cart = array(1,3,4,4,5,8,22,22); $itemlist = array_count_values($cart); foreach($itemlist as $itemid=>$ocurrence){ $SQL = "UPDATE products SET stock = stock-".$ocurrence." WHERE id = ".$itemid; mysql_query($SQL); } 

Answer 1

You can do something like this:

 SELECT * FROM menu WHERE item_id = 1 UNION ALL SELECT * FROM menu WHERE item_id = 1 UNION ALL SELECT * FROM menu WHERE item_id = 2 

Check this link: MySQL table -> Can you return the same row multiple times in the same query?

Answer 2

if possible, create a separate item_cart element, a table that will have (cart_id, item_id, product_id) as the primary key. from here you can get the group at product_id to find out how many products are not sold.

 select product_id, count(product_id) as "No of Product Sold" from item_cart group by product_id 

your php code will perfectly update the number of products in stock coloumn.

If possible, you are trying to set up triggers for update stock columns every time you sell a product.