It’s been a while since i wrote an article for this series. All the “masochists” out there must be wondering. The truth is that firstly there was no interesting topic to write about and secondly it is pretty hard to write such an article since it needs a lot of research from my side. Anyway, yesterday i got a comment from Kim on the revisions removal here. What she is asking is a way to change all the articles from the “Uncategorized” category to another one through the database rather from the WordPress administration. It sounds pretty reasonable since she claims she wants to do so for over a thousand posts. Obviously, using the admin is not an option. To go through this i feel it’s necessary to see how WordPress stores categories and the relationship with the posts.

As you can see on the figure on the right, the relationship between a post and it’s categories is not that straight forward. That is because WordPress uses a rather complex way to represent a category. As you can see there is a table “term”. In there, all the categories, tags and links of your blog are stored. But they are all messed up. In order to figure out which is what there is another table called “term_taxonomy” that actually stores the “genre” of the term. In other words, there, you will find if the term is a category, a link or a tag. Finally, there is the table “term_relationships” where a term is associated with a post in a way of “post_id -> taxonomy_id”.

In order to illustrate how this works here is a small example. When you start out your blog there is a category called “Uncategorized”. That is stored in the “terms” table with it’s name, slug and, ofcourse, it’s id. In order to show that it is a category, there is an entry on the “term_taxonomy” table where the term with id 1 (in which case is the “Uncategorized” term) is given a taxonomy id of 1 and is set to have a taxonomy of “category”. This way, WordPress defined that there is a category named “Uncategorized”. Finally, to add the post with id 1 (the one that welcomes you to your blog) under the “Uncategorized” category there is a final entry on the table “term_relationships” where the object with id 1 (the first post) is associated with the term taxonomy with id 1 (the “Uncategorized” category). Not simple, but that’s that.

As a developer, i’d like to pose an objection to this way of representation. In my oppinion, it’s not good for many reasons. First, there is no clear way of representation for crucial entities within your blog, which are categories and a tags. I think there should be different tables for those two. Secondly, from a more practical point of view, imagine having 30 categories and 1000 tags. If you wanted to join the relationships table with the posts one to get a category related result, you would get x1000 rows on your result set for no reason. On the other hand, i can see it’s too late to change it since every new release has to be backwards compatible with the older ones, so changing it is not an option.

Enough with the “optimizing” rambling. Let’s get down to what Kim asked. As it might seem obvious now, you need to take the following steps:

1. Create the new category you want to put the posts under (through the admin).
2. Find out of the taxonomy id the category gets.
3. Open up phpmyadmin (or mysql console or whatever you use to access your database) and update the table of the relationships replacing taxonomy id’s of 1 to the taxonomy id of your new category.

Before going on with the queries that would do such a thing, there is a pitfall i’d like you to notice. In the table “term_relationships” the columns “object_id” and “term_taxonomy_id”, both, form the primary key of the table. This means, among other things, that there can’t be the same pair of object id and taxonomy id more than once in the table. For instance, you can’t have object id 5 and taxonomy id 1 twice (or even more) in the table. That’s reasonable since one post doesn’t need to associate to the same category twice. One row is enough to declare the relationship. Now, why would that happen? Consider the case where, creating a post, you saved it at first and then chose a category for it. If you forgot to remove the “Uncategorized” category (which was added by default on the save) this post belongs both to “Uncategorized” and your category of choice. This means that on the relationships table there are two rows with the same object id (the post at hand) and the two taxonomy ids, 1 for the “Uncategorized” and another number for the other category (let’s say it’s 10). In this scenario, wanting to change the taxonomy id from 1 to 10 would cause an error since you would have a key conflict. This scenario, although possible, is highly unlikely, since all your “Uncategorized” posts would probably not be associated with any other category, especially the one you targeted on moving them.

Leaving the above, worst case scenario, aside, here are the two SQL queries that will do the dirty job. The first one will return all the categories with their properties. From there you will get the taxonomy id we want.

SELECT wp_terms.name, wp_term_taxonomy.term_taxonomy_id FROM `wp_terms`, `wp_term_taxonomy` WHERE wp_term_taxonomy.taxonomy="category" AND wp_terms.term_id=wp_term_taxonomy.term_id;

The second one, is the one that will actually do the job. Here we assume that the taxonomy id of the target category is 10 but you replace that with the one you found from the above query.

UPDATE `wp_term_relationships` SET term_taxonomy_id=10 WHERE term_taxonomy_id=1;

Pretty easy huh? Now here comes the question. Why not go through “Manage->Categories->Uncategorized” and change the name there. Well, from a practical point of view, there is no difference what so ever. But, a WordPress installation defaults to this one. This means that this category will become the one that keeps all the posts that don’t have any category. If you name it something else it might be semanticaly wrong to you. Using this way, you keep your default “Uncategorized” category where all “orphaned” posts reside and you file your so far “orphaned” to a category that might make some sense to you.

I hope this answers Kim’s question. One more note on the subject. Backup, backup and… ahm backup! When messing with your database, a small mistake on your query might bring your blog down to it’s knees. As always, comments, errors, suggestions all are welcome and appreciated!