MySQL display duplicate entries

I'm trying to go through a mysql database and display all the records that have a duplicate telephone number. The code below seems to only get the first record that has a duplicate but not all the records that have a duplicate.

$query = "SELECT *, count(*) cnt FROM newtab GROUP BY phone HAVING cnt > 1";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$lastname = $row['lastname'];
$firstname = $row['firstname'];
$phone = $row['dayphone'];

echo "Duplicate record - $firstname, $lastname, $phone";
}

You can't do a "SELECT *" with a GROUP BY clause. The only thing you can select are the columns you used in the GROUP BY clause (and column functions such as COUNT(), MAX(), AVG(), etc).


SELECT phone
FROM newtab
GROUP BY phone
HAVING count(*) > 1


Alternatively you can join the table with itself, which allows you to select other columns. You just need to be sure you don't select rows that match themselves.


SELECT phone, id
FROM newtab as t1
JOIN newtab as t2
ON t1.phone = t2.phone
WHERE t1.id != t2.id