Brian Jepson’s Weblog

The next problem was the categorization of my posts. I had hundreds of posts, and they picked up the blosxom categories like /Development/XML. But I wanted tags, instead. I also wanted to cut down on the number of tags I had. At first I used the WordPress user interface, but that would have taken days. So I poked around the Yahoo site management tools, and found that it was easy to install phpMyAdmin, which lets me do ad-hoc SQL queries against the mysql database.

By going to the WordPress admin interface, clicking Manage, and then Categories, I was able to find the unique IDs that WordPress assigns to each category. So, if I wanted to change all my posts tagged “/Development/XML” so that they were tagged “programming”, I had some digging to do.

Assuming “programming” was 30, and “/Development/XML” was 16, then all I had to do was find the table where the categories are stored, and issue an SQL command like:

update whatever-the-table-is-called set whatever-the-id-is-called = 30
where whatever-the-id-is-called = 16

In phpMyAdmin, it was a matter of selecting the database named “blog” (if you’re not on Yahoo!, it might be a different name), and issuing the query against the wp_post2cat table, using the category_id column. So a command like this would do the trick (you’d need to replace 30 and 16 with the correct values for your system, and please back up your database first!!!):

update `wp_post2cat` set category_id = 30 WHERE category_id = 16

So, I did this a lot, and eventually got my categories down to something manageable. Then I used the WordPress admin UI to delete the categories I was no longer using.

If you don’t know SQL, you should read up on it before you try anything like this. One wrong move, and you could accidentally do something like change the category of every post in your archive (or worse, delete everything).

This entry was posted on Saturday, January 14th, 2006 at 4:30 pm and is filed under blogging. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.