WordPress MySql Statement To Delete All Pending Comments

[sajugovind]

 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:

 

Code: [Select]

mysql -u Your-DB-User-Name-Here -p Your-DB-Name-Here
Once logged in type the following desc wp_comments; command to see wp_comments structure:

Code: [Select]

mysql> desc wp_comments;
Sample outputs:

Code: [Select]

+----------------------+---------------------+------+-----+---------------------+----------------+
| 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:

Code: [Select]

mysql> select * from wp_comments where comment_approved = '0';
Or better just show all the pending comment count, enter:

Code: [Select]

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

Code: [Select]

+----------+
| count(*) |
+----------+
|       18 |
+----------+
1 row in set (0.01 sec)
Sql statement to delete all pending comments

Type the following command at mysql> prompt>

Code: [Select]

mysql> delete from wp_comments where comment_approved = '0';
Sample outputs:

Code: [Select]

Query OK, 18 rows affected (0.09 sec)
To quite from mysql session, enter:

Code: [Select]

mysql> quit;