WordPress.org

Ready to get started?Download WordPress

Forums

How to delete 3400 spam comments? (16 posts)

  1. giorgio
    Member
    Posted 9 years ago #

    Is there a way to delete 3400 spam comments all together? Deleting all that spam comment by comment would take me a month or so :-( Thanks

  2. Mark (podz)
    Support Maven
    Posted 9 years ago #

    what version of wp ?
    what spam plugins do you have ?
    are these comments for existing posts ?
    We need more info for this.......

  3. giorgio
    Member
    Posted 9 years ago #

    I logged in into PhpMyAdmin... but I don't know how to delete them all together!

  4. awh
    Member
    Posted 9 years ago #

    If you're desperate you could export to a text file, delete the comments in a text editor (or Excel), and import back to MySQL. There's got to be a better way, though.

  5. Mark (podz)
    Support Maven
    Posted 9 years ago #

    If it's every post inside those numbers above, I'm sure there will be a way to delete them using an sql query - we just need to wait for someone who knows what they are doing to happen along :)
    In the meantime:
    http://www.tamba2.org.uk/wordpress/spam
    Go get some protection !

  6. giorgio
    Member
    Posted 9 years ago #

    I'm definitely going to adopt some sort of spam protection :-) Does anybody know a sql query to delete those comments? I'm not a developer, so I'm not experineced with sql

  7. shelleyp
    Member
    Posted 9 years ago #

    giorgio, the comments probably came in during a specific timeframe. First, make sure that you have a backup of your database. Your ISP should be doing nightly backups, or you could use mysqldump from the command line (if you don't have ssh access, you should also be able to save the database through PHPMyAdmin, using Export, though I've had problems with this).
    In PHPMyAdmin, click on the SQL tab, which will open a window to type in a SQL statement. What you're after is all comments between a particular timeframe, which should give you all your spam comments. Note, though, that there may be a few good ones, and you may have to refine your actions into multiple deletions, to eliminate the bad and keep the good.
    What you're first going to type is a SELECT statement, to test the query to make sure that you're only deleting the comments that are 'bad'. Type in something like the following, assuming that you used the default prefix with the WordPress tables:
    SELECT *
    FROM wp_comments
    WHERE comment_date > '2004-11-15 01:10:04'
    AND comment_date <= '2004-11-20 00:10:04'

    This will return all comments between 1:10AM, November 15th, and ten minutes after midnight, November 20th. Adjust the date values for your own case. The last six values are the hour, minute , and second in 24 hour time.
    Once you're sure your query highlights the bad comments only, adjust it to the following:
    DELETE FROM wp_comments
    WHERE comment_date > '2004-11-15 01:10:04'
    AND comment_date <= '2004-11-20 00:10:04'

    This will delete all comments for that period.
    Use this with caution.

  8. giorgio
    Member
    Posted 9 years ago #

    Instead of using the date and the time as parameters for selecting comments, can I use the comment_id? I know that comments from ID 177 to ID 3569 are spam, so they must be deleted. I'm pretty sure that there is no "good comment" among them.

  9. giorgio
    Member
    Posted 9 years ago #

    Thanks everybody... I managed to delete all the comments :-)

  10. BAKSHI
    Member
    Posted 8 years ago #

    Okay, I hate bumping this very old thread but I have a huge problem.

    Recently, my site got infected with a javascript worm and my host (ipowerweb) proved to be very incompetent at fixing my problem.

    Also, I host alot of blogs, most of them belonging to my friends.

    Because of this, I chose a new host Powweb. I was able to export some of the MySQL entries from my old host to my new host successfully.

    However, there was one blog that had more than a thousand spam comments on it.

    iPowerWeb, being a cheapass host, only limits export to like 600kb.

    So when I tried to export the comments from the old host in phpMyAdmin, I'd get an error message and then a file can't be found page in my browser. I've tried this with several browsers but to no avail.

    Is there any way I can delete the spam in the MySQL database without having to delete all the comments?

    Or another way to download it instead of going through iPowerWebs phpMyAdmin screen?

    This problem is frustrating me, thanks.

    Also I should note that the WordPress directory was deleted so I can't delete the comments from the blog.

    This is the new host where my friends blog is hosted.
    http://gooie.bakshi.us

  11. Would these be comments that you marked as "spam" at some point, are the actual spam comments that are currently "live" on on your blog, or are they in the moderation que?

  12. BAKSHI
    Member
    Posted 8 years ago #

    It was marked as spam.

  13. Use this plugin, it has an easy-to-use "nuke" button:

    http://www.chrisjdavis.org/2005/03/03/mass-delete-15/

  14. BAKSHI
    Member
    Posted 8 years ago #

    That would be useful, however I can't access WordPress itself. The directory was accidentally deleted during the worm cleaning. So I need to just export the comments and upload them to the cleaned WordPress on the new host.

    Also I checked the size of the comment table, it's 15.5 MB. That's quite alot of spam.

    I hope I don't get an SOL reply. Pleaassee

  15. In that case, you'll have to manually dig through your SQL backup file with a text editor and manually remove the entries.

  16. BAKSHI
    Member
    Posted 8 years ago #

    Actually, that site you gave me the address to solved the problem. The site contained the only command I needed.

    "DELETE from wp_comments WHERE comment_approved = 'spam'"

    That's all it needed. Thank you very much for your help. If it wasn't for you posting that link, I probably would of given up :)

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.