Information and Links
Join the fray by commenting, tracking what others have to say, or linking to it from your blog.
WordPress wp_category and wp_post2cat errors after upgrading
For those who get wp_category and wp-post2cat errors after upgrading WordPress, there is a solution. I noticed on the wordpress.org forums, a lot of people asking how to fix their plugins after they upgrade. The usual most common response I found was "show me your code, and I'll show you how to fix it." That wasn't the answer I was looking for, and it would appear that lots of others weren't satisfied by that answer either.
If you have your own hand crafted WordPress plugins you wish to update, here is some useful information to help you:
1 - wp_category and wp_post2cat don't exist anymore. DO NOT recreate them, it will not help you.
2 - WordPress now uses three new tables to replace the old tables: wp_terms, wp_term_relationships, wp_term_taxonomy.
Here is an example code that I had in one of my plugins:
-
SELECT cat_ID, count(id) as cat_counter,cat_name, category_parent FROM wp_category LEFT JOIN wp_post2cat AS pc ON (cat_ID=category_id) LEFT JOIN wp_posts ON ID=post_id AND post_status='publish' AND post_type='post' GROUP by cat_ID ORDER BY category_parent,cat_name
Here is the code after I updated it:
-
select t.term_id as cat_ID, count(id) as cat_counter,t.name as cat_name,tt.parent as category_parent FROM wp_terms as t left join wp_term_taxonomy as tt on t.term_id=tt.term_id left join wp_term_relationships as tr on (tr.term_taxonomy_id=tt.term_taxonomy_id) left join wp_posts on wp_posts.ID=tr.object_id and tt.taxonomy='category' AND post_status='publish' AND post_type='post' group by t.term_id order by category_parent, cat_name
Here is what I did to upgrade my plugin:
1 - I didn't feel like rewriting tons of code, so where I had a table column in a select, I changed it to as tablename.column. For example, in the above example I needed cat_ID, cat_name and category_parent. The new names for those are wp_terms.term_id, wp_terms.name and wp_term_taxonomy.parent. By using wp_terms.term_id AS cat_ID, I would not have to modify any other code where I used cat_ID.
2 - Instead of requiring two tables in the FROM section of a MySQL SELECT, I need three, which also means I needed to add an additional MySQL WHERE query also. To clarify this part, I am going to outline it here:
OLD FROM -> NEW FROM
wp_category -> wp_terms
wp_post2cat -> wp_term_relationships
non-existent -> wp_term_taxonomy
New WHERE statements
-
wp_category.cat_ID=wp_post2cat.category
becomes
-
wp_terms.term_id=wp_term_taxonomy.term_id AND wp_term_taxonomy.term_taxonomy_id=wp_term_relationship.term_taxonomy_id
And to fix the last relationship which will join the posts to the WordPress categories:
-
wp_posts.ID=wp_post2cat.post_id
becomes
-
wp_posts.ID=wp_term_relationships.object_id
Note that you will need to add one last MySQL where clause. The reason for this last one, is that the taxonomy is no longer used just for categories. Since our old WordPress plugin we are updating was used for categories, we want to ensure we select just categories from our new tables.
Here is the one additional MySQL WHERE clause:
-
wp_term_taxonomy.taxonomy='category'
Hopefully this will help someone out in their WordPress upgrade problem. If you see any errors in my code, or have any questions, post a comment. I usually try to check my comments regularly but I am worked to the gills right now, so I might not respond very quickly.
Enjoy!
Great effort to put them together, your post worth reading, i am newbie wordress blogger, you make my job a lot easier.