Finding duplicates of mySQL and then merging data

Question

I have a mySQL database with less than 2 million rows. The database is not interactive, so performance is not key.

I have a (simplified) structure:

`id` int(11) NOT NULL auto_increment
  `category` varchar(64) NOT NULL
  `productListing` varchar(256) NOT NULL

Now the problem that I would like to solve is to find duplicates in the productListing field, combine the data in the category field into one result - delete duplicates.

So, give the following data:

+----+-----------+---------------------------+
| id | category  | productListing            |
+----+-----------+---------------------------+
|  1 | Category1 | productGroup1             | 
|  2 | Category2 | productGroup1             | 
|  3 | Category3 | anotherGroup9             | 
+----+-----------+---------------------------+

I want to finish with:

+----+----------------------+---------------------------+
| id | category             | productListing            |
+----+----------------------+---------------------------+
|  1 | Category1,Category2  | productGroup1             | 
|  3 | Category3            | anotherGroup9             | 
+----+----------------------+---------------------------+

What is the most efficient way to do this, either in a pure mySQL query or in php?

Answer 1

I think you are looking for GROUP_CONCAT:

SELECT GROUP_CONCAT(category), productListing
FROM YourTable
GROUP BY productListing

, , :

CREATE TABLE new_YourTable SELECT GROUP_CONCAT(...;
DROP TABLE YourTable;
RENAME TABLE new_YourTable TO YourTable;
-- don't forget to add triggers, indexes, foreign keys, etc. to new table

Answer 2

SELECT  MIN(id), GROUP_CONCAT(category SEPARATOR ',' ORDER BY id), productListing
FROM    mytable
GROUP BY
        productListing