SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Show me the duplicates

    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:

    PHP Code:
    $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. #2
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    remove the 'limit 1'.

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    And how will I get the number of duplicates for each one?

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

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ok, I did a loop and it works. But where do I get the number of duplicates everyone has?

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,035
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Thread moved to the database forum as it's now more of a database problem
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I believe countemail is what you want to see.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by brad62 View Post
    But where do I get the number of duplicates everyone has?
    in the column countemail in the result set
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes. This one works! Thanx.

    PHP Code:
    $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 />";



Bookmarks

Posting Permissions

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