WordPress MySql Statement To Delete All Pending Comments

by on November 19, 2013 · 4 comments· LAST UPDATED November 19, 2013

in , ,

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?

Tutorial details
DifficultyIntermediate (rss)
Root privilegesNo
Requirementsmysql+shell access
Estimated completion time2m
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;

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 4 comments… read them below or add one }

1 Marios Zindilis November 19, 2013 at 4:21 pm

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…

Reply

2 Bob November 20, 2013 at 1:04 pm

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.

Reply

3 Marios Zindilis November 20, 2013 at 1:13 pm

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

Reply

4 Adrian7 November 20, 2013 at 8:52 am

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}'";

Reply

Leave a Comment

Tagged as: ,

Previous Faq:

Next Faq: