updating sql values with array which have a couple times occured items

Question

While i am coding a shopping site, I need to update product stock. But the thing is, naturally shopping cart can have the same items a couple of times. What is the best way for updating it?

I tried IN but the following SQL query returns 3 items.

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

Here is my solution but, I don't think this is the best one.

$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?