WordPress bulk modify post catgories - Part II

(No Ratings Yet)

 Loading ...

Posted by Jim on Oct 20, 2007 101 views

Checking into the post_category column in the wp_3_posts table, I see that it never changes

SELECT COUNT( * ) AS `Rows` , `post_category`
FROM `wp_3_posts`
GROUP BY `post_category`
ORDER BY `post_category`
LIMIT 0 , 30

The result set from this query is 653 records, all with a 0. That tells me that categories only come from the wp_3_post2cat table. Now that means if I do a query for a keyword in wp_3_posts.post_title or wp_3_posts.post_content, I can do a join with wp_3_post2cat and update the value there from uncategoriez to a value that matches the keyword. Or I could do a result query to get the post ID’s, and do an update based on that value as a seperate query without a JOIN. Any benefit between the two? The JOIN is cleaner and easier to do, without the JOIN is safer since it lets me see if a result returns to many values, etc..

I think i still need to do the join because I only want results of posts that have a post category of uncategorized with them. I don’t want one of my keyword queries to move things inbetween other categories.

So lets see what we have in wp_3_post2cat to being with

SELECT COUNT( * ) AS `Rows` , `category_id`
FROM `wp_3_post2cat`
GROUP BY `category_id`
ORDER BY `category_id`
LIMIT 0 , 30

This gives the following results -

Rows  category_id 
529 1
25 4
2 5
44 6
25 7
16 8
15 9

Now the categories are -

SELECT *
FROM `wp_3_categories`

1 Uncategorized 529
2 Blogroll 0
3 ANIME 0
4 Gaming 25
5 Vision / Detached Retina 2
6 Family 44
7 Soapbox 24
8 Technology 16
9 Entertainment 15

With the last number being the number of times that category is being used. That means that we need to lookup the keyword on the post, then update the post2cat and also update the counters by decreasing uncategorized and increasing the new category. This is getting more complicated, but if we continue to do this step by step, each of those steps are easy to do. it is just best if it all gets done as one large querry when finished.

Filed Under: technology