How to delete duplicate enteries in database? (MYSQL, PHP)

Discussion in 'Databases' started by umarpk, Apr 18, 2011.

0
  1. #1
    I have a classified site and where people come to publish their ads for free. As you know where "free" things are allowed people missuse of that advantage. So they publish their ad again and again. I have to delete their duplicate ads to run good my classified site. So please suggest me any mysql query to delete only duplicate ads and I want to delete only duplicate ads not all.

    Thanks in advance.
     
    umarpk, Apr 18, 2011 IP
  2. rayqsl

    rayqsl Active Member

    Messages:
    91
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    53
    #2
    What is the table schema? You haven't given us any details to work on. Show the table schema and some sample data.
     
    rayqsl, Apr 18, 2011 IP
  3. umarpk

    umarpk Peon

    Messages:
    59
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Dear I am sory for incomplete info provided by me,

    Now note please, in my classified posts there are about more than 4000 posts and increasing day by day. I want to make a such php page from which i can view only duplicate title ads. or duplicate discription ads.

    my table contains these columns in database.
    id, title, discription, location, name, email, phone, image, etc


    Please suggest any solution.
     
    umarpk, Apr 18, 2011 IP
  4. artus.systems

    artus.systems Well-Known Member

    Messages:
    87
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    103
    #4
    You can save the users IP address and check for match while entering data in database.
    If match > don't allow them to save
    else > save the data.

    OR
    you can check for certain combination's like name+email+phone
     
    artus.systems, Apr 18, 2011 IP
  5. umarpk

    umarpk Peon

    Messages:
    59
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    As you know this post is related to classified ads, and I am also saving ip addresses of each user. So each user can post ads again and again. I just want to delete only duplicate ads. But on the spot I want to allowed users to place their ads in my website with out restrictions.
    So what you say?
     
    umarpk, Apr 18, 2011 IP
  6. aman_gcs

    aman_gcs Member

    Messages:
    178
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    26
    #6
    Hi,

    I used the following SQL Query to determine Duplicate Records with same Name
    & Email in the row:

    SELECT email,
    COUNT(email) AS NumOccur1, name, COUNT(name) AS NumOccur2
    FROM mytbl
    GROUP BY name, email
    HAVING ( COUNT(email) > 1 ) AND ( COUNT(name) > 1 )
     
    aman_gcs, Apr 19, 2011 IP
  7. umarpk

    umarpk Peon

    Messages:
    59
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    I am very thankful to you that you have made a special query for me but still I want to delete them not declare them.
    Dear please note that, I have arqued in my thread that I want to delete with the same query, I have found the duplicate items but unable to delete them from the msql query.
     
    umarpk, Apr 19, 2011 IP
  8. artus.systems

    artus.systems Well-Known Member

    Messages:
    87
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    103
    #8
    artus.systems, Apr 19, 2011 IP
  9. EvanP

    EvanP Banned

    Messages:
    1,731
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Well any query that any person gives you will only return results that are exact duplicates. And by my guessing, members of your site will have changed a word or two to make it seem less obvious and harder for you to catch their duplicate adverts. I guess, to stop it from happening in the future, you could code something which will stop users entering the exact same data already found in your table?
     
    EvanP, Apr 20, 2011 IP