Blog

03/06/2019

WordPress MySql Statement To Delete All Pending Comments



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: SXI ADMIN

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

20/08/2019

Start your Bitcoin Exchange with our Software

Start your Bitcoin Exchange with our Software Setup your white label bitcoin exchange right away. Or create your own cryptocurrency using...
14/08/2019

How to KVM, QEMU start or stop virtual machine from command line (CLI)

KVM or Kernel Based Virtual Machine is a popular virtualization technology. It allows you to run virtual guest machines over a host machine. To start...
14/08/2019

How to Docker backup Saving and restoring your volumes

Running a Docker volume backup First, we spin up a temporary container, and we mount the backup folder and the target Docker volume to this container....