Merging two records with a common field and increment one field in MySQL & PHP

Question

I have a table as follows. What I would like to avoid is having two product id's in the table. How can I merge the two common fields using a query and increment the quantity?

cartid |  prodid     | quanity |

   1   |  9226582    | 3       |

   2   |  9226582    | 5       | 

   3   |  7392588    | 1       |

The desired results is that the table should be altered as follows:

cartid |  prodid    | quanity |

   1   |  9226582   | 8       |

   3   | 7392588    | 1       |

I have searched for answers but all seem too complex. Is there a way to do this in a simple way?

Answer 1

If you want to update the table in the database, you can do this:

create table newtable
   (`cartid` int, `prodid` int unique key, `quantity` int);

insert into newtable
   select * from yourtable order by cartid
   on duplicate key update quantity=newtable.quantity+values(quantity)

select * from newtable

Output:

cartid  prodid      quantity
1       9226582     8
3       7392588     1

If you're happy with the result you can then

drop table yourtable
alter table newtable rename to yourtable