将字段移动到另一个表并使用该ID更新第三个表中的行

Question

I have three tables called "itemorders", "mycart" and "products". I am currently moving the items in mycart into itemorders table like this in my multiquery.

$query = "INSERT into itemorders 
  (itemID,itemName,itemSize,itemColour,itemPrice,quantity,orderID) 
   SELECT itemID,itemName,itemSize,itemColour,itemPrice,quantity, 
 LAST_INSERT_ID() FROM mycart WHERE email='".$email."'  ";

How do I use the itemID,itemColour and quantity from my query and use them to update the quantity field in the products table?

Answer 1

You could use an UPDATE INNER JOIN (https://dev.mysql.com/doc/refman/5.5/en/update.html) if your itemorders table also has a AUTO_INCREMENT column.

Something like:

UPDATE products p
INNER JOIN itemorders i ON p.itemID = i.itemID AND p.itemColour = i.itemColour
SET p.quantity = p.quantity - i.quantity
WHERE i.ID = LAST_INSERT_ID()

I haven't tested this and ensuring you handle the business invariants around avoiding going into negative stock counts is left as an exercise for the reader.

P.S. Also be careful about SQL injection attacks (the way you are interpolating the email may make life harder for poor Bobby Tables. https://xkcd.com/327/), I'd recommend looking into PDO http://php.net/manual/en/pdostatement.bindvalue.php.