WordPress MySql Statement To Delete All Pending Comments

Posted on in Categories , , last updated November 19, 2013

I have over 1800+ pending comments and most of them are spams in WordPress based blog. How do I delete all (mass delete) the pending comments using sql statements?

You can delete all pending comments from your database. WordPress has wp_comments table. This table can be used to store, retrieve, modify and delete comments.

Procedure to delete all the pending comments from your database

First, login to your remote or local server over the ssh based sesson.

Next, type the following command to login into your mysql database:

Warning: Before you get started with the following commands, it’s a good idea to back up your database. This means if there are any issues you can easily restore your database. See how to backup your database for more information.

mysql -u Your-DB-User-Name-Here -p Your-DB-Name-Here

OR

mysql -u Your-DB-User-Name-Here -h Db-Server-IP-Or-HostName -p Your-DB-Name-Here

In this example, login to db called blog as foo user on localhost:

mysql -u foo -p db

OR

mysql -u foo -h localhost -p db

Once logged in type the following desc wp_comments; command to see wp_comments structure:

mysql> desc wp_comments;

Sample outputs:

+----------------------+---------------------+------+-----+---------------------+----------------+
| Field                | Type                | Null | Key | Default             | Extra          |
+----------------------+---------------------+------+-----+---------------------+----------------+
| comment_ID           | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| comment_post_ID      | bigint(20) unsigned | NO   | MUL | 0                   |                |
| comment_author       | tinytext            | NO   |     | NULL                |                |
| comment_author_email | varchar(100)        | NO   |     |                     |                |
| comment_author_url   | varchar(200)        | NO   |     |                     |                |
| comment_author_IP    | varchar(100)        | NO   |     |                     |                |
| comment_date         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| comment_date_gmt     | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| comment_content      | text                | NO   |     | NULL                |                |
| comment_karma        | int(11)             | NO   |     | 0                   |                |
| comment_approved     | varchar(20)         | NO   | MUL | 1                   |                |
| comment_agent        | varchar(255)        | NO   |     |                     |                |
| comment_type         | varchar(20)         | NO   |     |                     |                |
| comment_parent       | bigint(20) unsigned | NO   | MUL | 0                   |                |
| user_id              | bigint(20) unsigned | NO   |     | 0                   |                |
+----------------------+---------------------+------+-----+---------------------+----------------+
15 rows in set (0.00 sec)

To see all the pending comments, type:

mysql> select * from wp_comments where comment_approved = '0';

Or better just show all the pending comment count, enter:

mysql> select count(*) from wp_comments where comment_approved = '0';

Sample outputs:

+----------+
| count(*) |
+----------+
|       18 |
+----------+
1 row in set (0.01 sec)

Sql statement to delete all pending comments

Type the following command at mysql> prompt>

mysql> delete from wp_comments where comment_approved = '0';

Sample outputs:

Query OK, 18 rows affected (0.09 sec)

To quite from mysql session, enter:
mysql> quit;

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

4 comment

  1. This will leave a lot of orphaned records in the commentmeta table (about twice as much as the deleted comments). There will be broken references in the commentmeta.comment_id table, pointing to commets that no longer exist, whereas when you delete the pending comments from the web interface, it cleans up the meta as well.

    I don’t see the point of this post, given that WordPress includes a “Delete all” functionality in the admin-panel, that would do everything for you, and leave the database much more tidy…

    1. How? And where is a “Delete all” function you speak of in wp-admin? Can you provide a better version of query posted by OP or Adrian7?

      TIA.

      1. In Dashboard, go to “Comments”, select “Spam”, then “Empty Spam”. I uploaded a screenshot of this at zindilis.com/pub/images/wordpress-delete-spam.png

  2. Nice post.
    I have implemented a similar solution in a cron script. It deletes all pending comments older then 30 days. PHP code generating the query below:

    $thirty_days_ago = date('Y-m-d H:i:s', strtotime('-30 days'));
    $SQL = "DELETE FROM `wp_comment`s WHERE `comment_approved` = '0' AND `comment_date`<'{$thirty_days_ago}'";

Comments are closed.