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.
What is the table schema? You haven't given us any details to work on. Show the table schema and some sample data.
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.
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
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?
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 )
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.
This should work DELETE FROM table1 USING table1, table1 AS vtable WHERE vtable.id > table1.id AND table1.field_name = vtable.field_name found somewhere on the net 2-3 yrs ago, worked for me but please don't forget to keep backup if you try ... googled and found this http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/
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?