Moving Comments in WordPress
I find that often times comments on blogs are left for the wrong post. Whether by mistake, laziness, carelessness or otherwise on the part of the commenter, this can lead to potentially pertinent information being in the wrong place.
Whatever the cause, the following article will describe in detail how you can easily manipulate your wordpress database to move the misplaced comments you have to the post they belong under.
This article will accomplish this by using the command line mysql utility. Depending on where you have your wordpress blog hosted, you may not have access to the database at all, or if you do you may have to access it using a tool such as phpmyadmin.
But before I get started, here is a list of what you will need to complete this process:
* mysql username
* mysql password
* mysql wordpress database name
First you’ll need to connect to mysql and select your database
$ mysql -u wordpress -p
Enter Password: ********
mysql> use my-wordpress-database;
Database changed
mysql>
Now, there are two tables that are of primary interest, wp_posts and wp_comments.
Let’s start with wp_posts, the columns of interest are ID and post_title.
mysql>select ID, post_title from wp_posts;
+----+------------------------------------------------+
| ID | post_title |
+----+------------------------------------------------+
| 4 | Bob Dylan |
| 6 | Johnny Cash |
+----+------------------------------------------------+
Now let’s look at the comments that have been left, they reside in the wp-comments table.
Here, the columns of interest are comment_ID, comment_post_ID, and comment_content.
mysql>select comment_ID, comment_post_ID, comment_content from wp_comments;
+------------+-----------------+-------------------------------------------+
| comment_ID | comment_post_ID | comment_content |
+------------+-----------------+-------------------------------------------+
| 1 | 4 | I really like Bob Dylan, nice article |
| 2 | 4 | Johnny Cash is the best |
| 3 | 6 | Johnny Cash the man in black |
+------------+-----------------+-------------------------------------------+
Note, in wp_comments, comment_post_ID ties in to wp_posts ID. So that means there are two comments for the Bob Dylan post, and one comment for the Johnny Cash post.
But wait…
The second comment, with comment_ID 2 looks like it is supposed to be a comment for the Johnny Cash post. Let’s see how we can move it.
mysql> update wp_comments set comment_post_ID=6 where comment_ID=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
and verify that it was moved
mysql>select comment_ID, comment_post_ID, comment_content from wp_comments;
+------------+-----------------+-------------------------------------------+
| comment_ID | comment_post_ID | comment_content |
+------------+-----------------+-------------------------------------------+
| 1 | 4 | I really like Bob Dylan, nice article |
| 2 | 6 | Johnny Cash is the best |
| 3 | 6 | Johnny Cash the man in black |
+------------+-----------------+-------------------------------------------+
Good, the comment was successfully moved, but there is still a problem.
mysql> select comment_count from wp_posts where ID=6;
+---------------+
| comment_count |
+---------------+
| 1 |
+---------------+
There are supposed to be two comments, so let’s update the comment_count for both the post that the comment was moved from, and where it was moved to.
mysql> update wp_posts set comment_count=2 where ID=6;
mysql> select comment_count from wp_posts where ID=4;
+---------------+
| comment_count |
+---------------+
| 2 |
+---------------+
mysql> update wp_posts set comment_count=1 where ID=4;
mysql> select post_title, comment_count from wp_posts where ID in (4,6);
+------------------------------+---------------+
| post_title | comment_count |
+------------------------------+---------------+
| Bob Dylan | 1 |
| Johnny Cash | 2 |
+------------------------------+---------------+
And that’s it. Now your comments are moved and all of the counters have been updated to reflect the moved comments.