WordPress bulk modify post catgories - Part III

Posted by Jim on October 21, 2007

So lets get started on joining these two table and see what results we can get from our SELECT statement.

JOIN wp_3_posts and wp_3_post2cat on Post_id
SELECT Post_id, Post_title
FROM `wp_3_posts`
WHERE wp_3_post2cat.post_category = 1

Woops, didn’t work, so let’s grab the JOIN sample

SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

and rewrite it correctly

SELECT wp_3_posts.id, wp_3_posts.post_title
FROM wp_3_posts
INNER JOIN wp_3_post2cat ON wp_3_posts.id - wp_3_post2cat.post_id
WHERE wp_3_post2cat.category_id =4
LIMIT 0 , 30

Now this worked but did not give me what I expected, so suspect I did something wrong in my SELECT

Showing rows 0 - 29 (16,375 total, Query took 0.0472 sec)
571 Reciple blog theme
repeated 25 times, so i checked the table for 571

SELECT * FROM `wp_3_post2cat` WHERE post_id = 571

And got just one result returned
571 571 8
So I might try switching the JOIN order, but when I went to type it again I noticed I had put a - sign instead of an = sign in the JOIN forumla

SELECT wp_3_posts.id, wp_3_posts.post_title
FROM wp_3_posts
INNER JOIN wp_3_post2cat ON wp_3_posts.id = wp_3_post2cat.post_id
WHERE wp_3_post2cat.category_id =4
LIMIT 0 , 30

works fine, So now to add the search on the post_title contents

SELECT wp_3_posts.id, wp_3_posts.post_title
FROM wp_3_posts
INNER JOIN wp_3_post2cat ON wp_3_posts.id = wp_3_post2cat.post_id
WHERE wp_3_posts.post_title LIKE ‘%EVE%’
LIMIT 0 , 30

Showing rows 0 - 29 (30 total, Query took 0.0063 sec)

Works fine, so now lets add to return results only if it has a category_id of 1 the uncategorized value

SELECT wp_3_posts.id, wp_3_posts.post_title
FROM wp_3_posts
 INNER JOIN wp_3_post2cat ON wp_3_posts.id = wp_3_post2cat.post_id
WHERE wp_3_posts.post_title LIKE ‘%EVE%’
 AND
 wp_3_post2cat.category_id = 1
LIMIT 0 , 30

Showing rows 0 - 24 (25 total, Query took 0.1416 sec)

BINGO - we now have a working query that based upon a keyword in this case %EVE% resturns a result of those posts whose have a category of Uncategorized. Now two things to make this better:
1 - make sure that the posts only have a single category assigned to them. Any posts with uncategorized and some other category should have the uncategorized removed. And then 2 - convert the SELECT into an UPDATE

Popularity: 1%