WordPress Hacks : Export and Redirect Posts based on their Category

WordPress is an excellent blogging engine. It is easy to setup and even easier to use. It has got a huge collection of themes and plugins. Because of all these things WordPress has got so widespread use.

WordPress has the concept of categories to segregate posts in different types.  When number of posts in some category becomes really huge, its good to make a separate website for this particular category.

I faced similar problem for one of my website and I decided to migrate all of my posts in “XYZ” category to a different domain. But, unfortunately WordPress Admin Dashboard doesn’t provide any direct method to export all posts in a particular category.

So, to perform such migration we’ll have to go through several steps. For better understanding we can basically divide this full migration in two steps:

1. Export Posts / Comments based on its category
2. Redirect posts of this category

Export Posts based on its category

WordPress admin panel is very rich, and it does provide direct method to export posts, pages, comments, custom fields, categories, and tags. But you can only export it based on its author, there isn’t any way based on category. So, basically what I did was :

1. Create a new user “AUTHOR_1”
2. Change author of all the posts in category “CATEGORY_XYZ” to “AUTHOR_1”
3. Export posts of “AUTHOR_1”

Step 1 and 3 are quite obvious and straight, the only challenge is with Step 2. The best way I felt was to dig into the WordPress database and change author on table level itself.

Database Manipulation

WordPress stores all posts in a table called “wp_xxxxxx_posts”. This table consists a column named “post_author”. So basically we’ll have to change this column for our posts. To do this just run following queries on your wordpress database.

NOTE : Before running any query on database level, I’ll recommend you to take backup of the datebase.

Get TERM_ID

SELECT  `term_id` ,  `name` ,  `slug`
FROM  `wp_h8kac5_terms`
WHERE  `name` LIKE  'CATEGORY_XYZ'

Here you'll have to note the "term_id" In following query put term_id taken form above query.

Get TERM_TAXONOMY_ID

SELECT  `term_taxonomy_id` ,  `term_id` ,  `taxonomy` ,  `count`
FROM  `wp_h8kac5_term_taxonomy`
WHERE  `term_id` = %term_id%
AND taxonomy =  'category'

Here you have to note "term_taxonomy_id". Secondly you can see a column "count", this count should be equal to the posts returned in :

http://www.example.com/wp-admin/edit.php?category_name=CATEGORY_XYZ

Update AUTHOR_ID in Posts Table

This is the final sql query to update author. Before this you need to find the user_id of the user we just created. This can be found in table "wp_h8kac5_users".

UPDATE wp_h8kac5_posts
SET post_author = %USER_ID%
WHERE id IN (
SELECT object_id FROM `wp_h8kac5_term_relationships` WHERE `term_taxonomy_id` = %TERM_TAXONOMY_ID% )

You need to replace %TERM_TAXONOMY_ID% from the "term_taxonomy_id" returned from the previous query.

Export Posts

Hurry !! half the battle is won. We have changed author of all the desired posts. Now we just have to log on to admin panel and export the posts of this author. This can be done from:

http://www.example.com/wp-admin/export.php

Imports Posts on New Domain

Now log on to the admin dashboard of new domain and import these posts from:

http://www.example.com/wp-admin/import.php

At at this point we have done with export part of this migration process. Now the only thing left is to redirect these posts. For redirection we’ll use .htaccess rules.

Redirect posts of the category

On my website I have following permalink format :

/%year%/%monthnum%/%category%/%postname%/

So, basically “Category” is the 3rd parameter in my URL Scheme. You need to keep similar URL Scheme on your new website. According to this URL pattern we have to write a rule in .htaccess file.

# BEGIN Redirection

RewriteEngine On
RewriteCond %{REQUEST_URI} ^(/\d{4}/\d{2}/)([^/]+)(/.*)$
RewriteCond %2 ^CATEGORY_XYZ$
RewriteRule ^(.*)$ http://www.new-domain.com/$1 [redirect=permanent,last]

# END Redirection

Just put above code in starting of .htaccess, and you are all done.

You can test our implementation by visiting any post of “CATEGORY_XYZ”, it should be redirected to your new domain.

Feel free to shoot any queries through comments.