Month: October 2008 (page 1 of 2)

WordPress blogs are based on MySQL and all the dynamic content is stored in a database. After trying out plugins, odd themes, and generally screwing around, my database has items in it that I can’t even recall what they were or if I need them.

One way to clean out the database is to export the blog to an XML file.  This will produce the posts, pages, and all the comments and not export the ton of junk in the database.  This way I create a clean empty blog, add the users, set up the theme and look I want and import the data.

This produces a very clean installation. It also removes me as the author for the comments I’ve left on my own blog. The table wp_comments table has a user_id field that the XML file does not have.

What I wanted to do is run a MySQL command that will find all instances in wp_comments which match my e-mail address, and update that record with my user_id on the blog. I could do this one at a time using phpMyAdmin but that’s pretty inefficient, not geeky, and time consuming.

After some research I found out to run these commands:

$ mysql -p
USE blogdatabase;
Database changed
SELECT * FROM wp_users WHERE user_email = 'notreally@myemail.com';

That gets me the ID number for my user.  Since it’s the first user created the ID is 2.

UPDATE wp_comments SET user_id = 2
WHERE comment_author_email = 'notreally@myemail.com';
Query OK, 113 rows affected (0.00 sec)
Rows matched: 113  Changed: 113  Warnings: 0

This updates the wp_comments table so comments left by me using my e-mail address.