Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: MySQL display duplicate entries
07-11-2007, 05:46 PM #1
- Join Date
- Nov 2005
- Thanked 0 Times in 0 Posts
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";
07-11-2007, 06:03 PM #2
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- Thanked 637 Times in 625 Posts
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).
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.Code:SELECT phone FROM newtab GROUP BY phone HAVING count(*) > 1
Code:SELECT phone, id FROM newtab as t1 JOIN newtab as t2 ON t1.phone = t2.phone WHERE t1.id != t2.id