Manage WordPress comments with MySQL commands

Ago some time ago I showed you how to manage WordPress sites with commands, it was through a script perl. In this case, I will show you specifically how to manage WordPress comments using SQL queries, that is, using commands in the MySQL console.

The first thing to keep in mind is that they must have access to the MySQL terminal or console, suppose we access the server via SSH and inside it we write:

mysql -u root -p
This assuming that our MySQL user is root, if it is another, simply change root for your user

Once this is written and pressed Enter it will ask for the password of that MySQL user, they write it, they press again Enter and voila, they will have already accessed:

mysql-terminal-access

Once inside the MySQL shell we must indicate which database we are going to use, you can see the available databases with:

show databases;
In MySQL it is very important always end instructions with a semicolon;

This will show you as I said the available databases, suppose the desired one is called sitewordpress, let's start using it:

use sitewordpress;

Let's review what the tables are called with:

show tables;

This will tell us the names of the tables, extremely important because we must see what exactly the name of the table related to the comments is: comments

It is usually called wp_comments or similarly, the important thing is that it always ends in: comments

Delete SPAM comments

With this line all the comments that are marked as SPAM will be deleted:

DELETE from wp_comments WHERE comment_approved = 'spam';
Remember, if it tells you that the wp_comments table does not exist then you must change wp_comments to the exact name of the comment table, the name above after show tables; appeared to them

Delete all comments pending moderation

DELETE FROM wp_comments WHERE comment_approved = '0';

Replace text in all comments

Suppose we want to search all comments for the word "political" and replace it with "corrupt", it would be:

UPDATE wp_comments SET `comment_content` = REPLACE (` comment_content`, 'politicos', 'corruptos');

Delete comments based on author's site URL

Let's suppose that for a certain reason we want to eliminate all the comments of any user who, when commenting, has specified in the comment form data (name, site and email) that their site was http://taringa.com (to cite an example) , it would then be like this:

DELETE from wp_comments WHERE comment_author_url LIKE 'http://taringa.com';

Close comments on old articles

I know of people who want to close the comments on old posts on their sites, so they must edit the posts one by one to deactivate the "comments enabled" option in each one, this line will solve their life:

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date <'2010-02-10' AND post_status = 'publish';

As you can see, in the middle of the line is a date, 2010-02-10, this means that all the posts that are published and have a publication date lower than February 10, 2010 (that is, they have been published before ) will close the comments, no one will be able to comment on them anymore.

Close comments on all articles

In case you do not want to close the comments only in some posts but in all, this line will help you:

UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE comment_status = 'open';

If you want to reverse this, change closed to open and vice versa, and voila, rerun the line with the changes.

Delete comments made in a certain time range

Suppose we want to delete all the comments that were made on April 1, 2014, between 4:15 in the afternoon and 10:40 at night, the line would be:

DELETE FROM wp_comments WHERE comment_date> '2014-04-01 16:15:00' AND comment_date <= '2014-04-01 22:40:00';

As you can see, the time is in 24-hour format, that is, military time.

The end!

Well, nothing more to add, I know that more than one will find this interesting.

regards


7 comments, leave yours

Leave a Comment

Your email address will not be published. Required fields are marked with *

*

*

  1. Responsible for the data: Miguel Ángel Gatón
  2. Purpose of the data: Control SPAM, comment management.
  3. Legitimation: Your consent
  4. Communication of the data: The data will not be communicated to third parties except by legal obligation.
  5. Data storage: Database hosted by Occentus Networks (EU)
  6. Rights: At any time you can limit, recover and delete your information.

  1.   @Jlcmux said

    Creo que acabas de hackear a Desdelinux sin darte cuenta jajaj

  2.   diazepam said

    What happens to the pint of this article? This seems like shit.

    1.    let's use linux said

      ready. fixed up.
      this alejandro ...

  3.   let's use linux said

    haha! stop making shit alejandro!
    when I catch you….

  4.   Yeretic said

    And wouldn't a MySQL tutorial make more sense? Or, if what you want is "Manage wordpress comments from the console" at least have the decorum of presenting a shell script that automates all these queries.

    Anyway, limiting my contribution to the post (what a novelty!)

    To load the WordPress database and make it ground:
    DROP DATABASE;

    I hope it is useful ... 😉

    1.    KZKG ^ Gaara said

      A MySQL tutorial, querys and others would be more extensive ... but, for those who only want to make certain changes in the comments of a WordPress, it would be impractical, they would not understand much.

      Regarding the matter of having or not decorum, come on Willians, you first contribute something and then, then criticize the contribution of others ok 😉

      Where is your site / blog that is useful to the community? I ask why, you have to have decorum and dignity, right? ^ _ ^

      1.    Raphael Castro said

        The best part of the post…. corrupt politicians

        +1