Changing the user_id of WordPress comments

July 18th, 2009 · 1 Comment

Lately, on my other blogs, I’ve fallen into the habit of not explicitly logging in when responding to comments — especially if I’m somewhere where I don’t trust the security of the network I’m on. However, this means WordPress 2.7 and later won’t automatically highlight my comments with a different background color since an unlogged-in user always a user_id of 0. I’ve discovered it’s not too hard to go into MySQL and clean this up though! (Of course, if your WordPress theme wasn’t applying such highlighting to comments made when logged in, the following won’t help you at all.)

ASIDE: Why can’t WordPress force logins to be through https instead of http so I don’t have to worry about the password being sent in plaintext?

The instructions below assume you’re using MySQL as your database backend for WordPress. They also assume you’re using WordPress 2.7.x (they may work on other versions but I haven’t tested that the DB tables have the same structure so I can’t claim it.) You’ll need to be familiar with a bit of SQL to understand this:

  1. First, you’ll need to refresh yourself on how your WordPress instance logs into MySQL.  You can simply look at the content of the ‘wp-config.php’ file for this.   You want the DB_NAME, DB_USER, and DB_PASSWORD.
  2. You’ll now need to run a MySQL client that can connect to the host machine running your database.  If your like me, a terminal / shell on the webserver will suffice with a default host of ‘localhost’.  Simply launch the client like:
    mysql -u <DB_USER> -p -D DB_NAME

  3. You can now search for all records of comments you might have made whether logged in or not by running a SQL query like below. This won’t list every comment, just the different combinations of your user_id, name, and ip address.

    SELECT DISTINCT user_id, comment_author, comment_author_IP FROM wp_comments WHERE comment_author = “<USERNAME>”;

    where <USERNAME> is the name you use to post comments when you’re logged on or not.  If you’ve used different user names, simply modify that query to do an OR expression to match multiple names.

    You’ll note that the results of that query include an IP address. I’m doing that because anyone can post a comment and use my username. Seeing the IP is a check to be sure that no one has done so — assuming you can remember the IP addresses from which you might have posted. If you can’t do that, you could instead show the “comment_author_email” or “comment_author_url” column values in your SQL query to ensure that you’re only seeing your own comments. However, again, anyone can type these values into your comment field so you need to be careful. Most people won’t know the full combination of values though. I just think the IP address is a bit harder for people to fake so I use that.

    If I find that the rows shown are not just my comments, I execute a SQL update command to change the comment_author. You may or may not want to do that. I’ll leave constructing that as an exercise for the reader since I’d have to know how you figured out they weren’t your comments in order to write SQL that worked for your case.

  4. Now note the different user_id’s shown. Find the one that matches when you comment from being logged in. It should be a non-zero value. From now on, we’ll denote this value by <MYUSERID>
  5. Now update all your comments to have that user_id you just noted. This is done by running a SQL update command like:

    UPDATE wp_comments SET user_id = <MYUSERID> WHERE comment_author = “<USERNAME>”;

    Of course, this only does the right thing if you can identify your own comments just by <USERNAME>

  6. Verify things worked okay by re-running the SQL select statement from above. You should now see all rows showing the value of <MYUSERID> you discovered above.
  7. Finally, revisit an actual post page on your blog, one with comments obviously, and verify your theme is applying the background highlight.

Tags: Blogging

1 response so far ↓

  • 1 Ilene // Oct 22, 2010 at 7:23 am

    Thanks! I was searching the net for a way to search comments by IP, and with the above code I was able to figure it out. :)