In this article we'll discuss an even more advanced method for cleaning up spam comments from your WordPress database using phpMyAdmin.

If you followed along with our 320: WordPress comment spam clean up article this would be one of the more advanced methods mentioned in that article.

The previous article 321: WordPress advanced comment spam clean up with search explained another advanced way of cleaning up spam from the database. The method discussed in this article can be used in conjunction with those steps in order to clean things up even better.

Multiple entry removal via advanced query

A lot of the time your spam comments are going to be mixed among good comments and hard to track down. You can use some advanced queries in phpMyAdmin to automate finding potential spam for removal using the steps below:

  1. First you'll need to get to your WordPress database via the phpMyAdmin interface.
  2. Click on the SQL tab at the top.
    wp-comments-sql-tab
  3. Enter in the following query, then press Go.

    You'll want to adjust this query specific to your WordPress database and table prefix.

    In this example our database name is simply wordpress, and our table prefix is wp_.

    SELECT comment_author, COUNT(comment_author)
    FROM wordpress.wp_comments
    GROUP BY comment_author
    HAVING COUNT(comment_author) > 5
    ORDER BY COUNT(comment_author)
    DESC

    wp-comments-sql-statement-and-go

    The SQL query breaks-down as follows:

    SELECT comment_author, COUNT(comment_author)
    Selects the comment_author row and counts each entry.

    FROM wordpress.wp_comments
    Sets the database and table we're selecting from.

    GROUP BY comment_author
    Groups our selection by the comment_author row.

    HAVING COUNT(comment_author) > 5
    Only select entries that have 5 or more duplicate entries.

    ORDER BY COUNT(comment_author)
    Order the results by the number of duplicate entries.

    DESC
    Sort our entries descending, showing largest entries first.

     

  4. Copy (Ctrl-C) the text of the comment_author entry with the most duplicate entries. wp-comments-sql-results
  5. Click on the Search tab.
    wp-comments-sql-results-search
  6. In the first field Word(s) or value(s) to search for: paste (Ctrl-v) your copied text.

    Place % symbols in-front and behind of the text for wildcards, now any entry containing the word at all will display.

    Leave the Find: option as the default of at least one of the words.

    In the Inside table(s): menu click on wp_comments to only search within that table.

    Next to the Inside column: field enter in comment_author, then finally click Go.wp-comments-sql-results-search-go
  7. Click on Browse.
    wp-comments-sql-results-search-browse
  8. Now you can review the comment_content row for that user to determine if it's all spam content.
    wp-comments-sql-results-search-comments-content
  9. In this case all comments were spam, so scroll to the bottom and click on Check All.

    If not all of the comments were spam, you can simply place a checkmark beside the ones that you wish to remove.
    wp-comments-sql-results-search-check-all
  10. Then click on Delete at the bottom of the page. wp-comments-sql-results-search-delete
  11. Confirm your deletions by clicking on Yes.
    wp-comments-sql-results-search-delete-confirm
  12. MySQL should now return an empty result set if the deletion was successful. wp-comments-sql-results-search-delete-empty

    Below you can see why this can be such an efficient method for finding spam in your WordPress database, using the different common WordPress comment rows we can find duplicate entries easily:

    comment_post_ID

    wp-comments-sql-comment-post-ID

    comment_author

    wp-comments-sql-comment-author

    comment_author_email

    wp-comments-sql-comment-author-email
     

    comment_author_url

    wp-comments-sql-comment-author-url

    comment_author_IP

    wp-comments-sql-comment-author-IP

    comment_content

    wp-comments-sql-comment-content
     
Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve this article:
Email Address
Name

new! - Enter your name and email address above and we will post your feedback in the comments on this page!

Like this Article?

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

0 Questions & Comments

Post a comment

Back to first comment | top

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!