Show me the duplicates

Databases
1

Now I have a table, where I know there are duplicate e-mail.
I would like something that echoes out all the duplicates and a number next to it telling me how many duplicates I have.

Like this:

12 abc@website.com
10 abc2@website.com
10 bill@website.com
9 janet@webiste.com
8 tom@internet.com
8 steve@sitepoint.com

and so on… showing me only the ones that have duplicates and the top one first.

What I have now is just showing me the top one, so I guess there only have to be a modification of the following:

$result = mysql_query("SELECT email, COUNT(email) AS countemail FROM temp_db GROUP BY email ORDER BY countemail DESC LIMIT 1");
 $row = mysql_fetch_assoc($result);


echo "<p>The most duplicated email is " . $row['email'] . "</p>";
2

remove the ‘limit 1’.

3

And how will I get the number of duplicates for each one?

And removing LIMIT 1 didn’t repeat and list them either.

4

When you retrieve query results you usually do a loop, handling each result as you wish. So, by removing the limit 1 you tell sql to give you all the results. Then you need to process the results, using a while or foreach and echo out the info from each row.

5

Ok, I did a loop and it works. But where do I get the number of duplicates everyone has?

6

Thread moved to the database forum as it’s now more of a database problem

7

I believe countemail is what you want to see.

8

in the column countemail in the result set

9

Yes. This one works! Thanx.

$query = "SELECT email, COUNT(email) AS countemail FROM temp_db GROUP BY email ORDER BY countemail DESC";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
	echo "E-mail: ". $row['email'] ." Times: ". $row['countemail'];
	echo "<br />";
}