CODEWITHSUNDEEP
phpmysql
King Julien
King Julien

Reputation: 11308

Get duplicate entries from a table

How can I get rows that were inserted more than once from a table? I allow my site users to have only one account per email but my script had a little bug so users where able to register more than once with the same email address. I want to get all the emails that has been recorded more than once. How is that possible using php/mysql?

Upvotes: 2

Views: 126

Answers (3)

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

You may want to try something like the following:

SELECT      email_address, 
            COUNT(*) number_of_dupes, 
            GROUP_CONCAT(user_id) users
FROM        users
GROUP BY    email_address
HAVING      COUNT(*) > 1;

Test case:

CREATE TABLE users (user_id int, email_address varchar(30));

INSERT INTO users VALUES (1, '[email protected]');
INSERT INTO users VALUES (2, '[email protected]');
INSERT INTO users VALUES (3, '[email protected]');
INSERT INTO users VALUES (4, '[email protected]');
INSERT INTO users VALUES (5, '[email protected]');
INSERT INTO users VALUES (6, '[email protected]');

Result:

+-------------------+-----------------+-------+
| email_address     | number_of_dupes | users |
+-------------------+-----------------+-------+
| [email protected] |               2 | 1,3   |
| [email protected] |               3 | 2,4,6 |
+-------------------+-----------------+-------+
2 rows in set (0.00 sec)

Upvotes: 4

Sarfraz
Sarfraz

Reputation: 382696

Use this query:

SELECT email FROM tableName GROUP BY email HAVING count(email) > 1

Upvotes: 2

Dennis Haarbrink
Dennis Haarbrink

Reputation: 3760

select email from table group by email having count(email) > 1
should do the trick :)

Upvotes: 3

Related Questions