WordPress DB Hacks: Determining the ID of a category parents

When manipulating WordPress databases for exports and merges, sometimes it is helpful to get a list of all of the parents for the categories your posts are in.

For the import I am working on right now.  The category list is being flattened from 30 categories down to 5 categories. The blog has just over 75 thousand posts in it and doing the conversion manually would be a rather arduous process to say the least.

So, I came up with this little query to give me the parent category for each of the child categories on that site.

It’s fairly simple but I’ve had to write it several times now and thought maybe someone else might need it sometime too:

SELECT `tt1`.`term_taxonomy_id` as 'Child Tax ID', `t1`.`term_id` as 'Child Term ID', `t1`.`name` as 'Child Name', `tt2`.`term_taxonomy_id` as 'Parent Tax ID', t2.`term_id` as 'Parent Term ID', `t2`.`name` as 'Parent Name' FROM `wp_term_taxonomy` `tt1`, `wp_term_taxonomy` `tt2`, `wp_terms` `t1`, `wp_terms` `t2` where `tt1`.`term_id` = `t1`.`term_id` and `tt1`.`parent` = `t2`.`term_id` and `tt1`.`taxonomy` = 'category' and `tt2`.`term_id` = `t2`.`term_id` and `tt2`.taxonomy = 'category';

Now I can just grab it instead of rewriting it again next time.  After all, this is The Code Cave…

PS – If you just want to see the top level categories, here’s how:

SELECT `tt1`.`term_taxonomy_id`, `t1`.`term_id`, `t1`.`name` FROM `wp_term_taxonomy` `tt1`, `wp_terms` `t1`where `tt1`.`term_id` = `t1`.`term_id` and `tt1`.`taxonomy` = 'category' and `tt1`.`parent`=0