Combine sql rows with same id, sum and update the total

Question

Hello,

I am attempting to write a php script to compare rows from the database and then adds the quantities it the product id matches. Based on cusotmer_id

Example:

mydb.cart

id, customer_id, cart_id, product_id, qty, price

1, 0001000, 4545453, 20, 10, 12.99
2, 0001000, 7473432, 20, 2, 12.99

Result
3, 0001000, 7473432, 20, 12, 25.98

trying to merge cart information from a previous session.

Any suggestions is appreciated,

Thanks

Answer 1

select sum(id)
     , customer_id
     , cart_id
     , product_id
     , sum(qty)
     , sum(price)
  from mydb.cart
group
    by customer_id
     , cart_id
     , product_id

Answer 2

You can probably just do this in SQL instead of in PHP. Update the new cart row and delete the old one. Not sure if this is correct MySQL syntax, but it should be something like the following:

UPDATE cart SET qty = (
    SELECT SUM(qty) FROM cart c WHERE c.id = id 
    GROUP BY c.customer_id, c.product_id)
  WHERE EXISTS (
    SELECT 'x' FROM cart c2 
    WHERE c1.customer_id = c2.customer_id
    AND c1.product_id = c2.product_id
    AND c1.id < id);

DELETE FROM cart c1 WHERE EXISTS (
    SELECT 'x' FROM cart c2 
    WHERE c1.customer_id = c2.customer_id
    AND c1.product_id = c2.product_id
    AND c1.id < id);