I have a table with customer info. Normally, the PHP checks for duplicates before they new rows are inserted. However, I had to dump a lot of older rows manually and now that they are all in my table, I need to check for duplicates.
Example rows:
id, name, email, phone, fax
I would like to do a mysql query that will show all ID's with matching emails. I can modify the query later for phone, fax, etc.
I have a feeling I will be using DISTINCT, but I am not quite sure how it's done.
You can GROUP BY email with HAVING COUNT(*) > 1 to find all duplicate email addresses, then join the resulting duplicate emails with your table to fetch the ids:
SELECT id FROM my_table NATURAL JOIN (
SELECT email FROM my_table GROUP BY email HAVING COUNT(*) > 1
) t
while($row = mysql_fetch_array($result)) I get "Resource Id #2".... How do I set this up correctly?
mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information.