Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Nov 2005
    Posts
    3
    Thanks
    0
    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";
    }

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    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).

    Code:
    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.

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •