I sometimes do some work on an e-commerce website and I needed to find a way to find duplicate products in their database. I didn’t want to use PHP as this was just unnecessary as I just wanted a simple report.
This query assumes that you have a MySQL table called product with a column called name and you want to find products that have the same name.
SELECT a.name FROM product a, product b WHERE (a.name = b.name) GROUP BY a.product_id HAVING COUNT(a.name) > 1;
This will give you a list like this:
+--------------------------------------+ | name | +--------------------------------------+ | sample product 1 | | sample product 3 | | sample product 4 | | sample product 5 | | sample product 6 | +--------------------------------------+
Permalink: http://www.websitefactors.co.uk/mysql/2011/06/finding-duplicate-rows-in-mysql/
You can also use a query like this one to remove the duplicate rows. Using this method saves you having to loop through the rows.
http://www.hotscripts.com/forums/php/5101-delete-duplicate-rows-mysql-using-php.html#post152705