// October 25th, 2009 // 1 Comment » // Dev, Web
I’ve been moving the Masabi web site and blog onto WordPress, from a combination of static web content and a blog driven by Blogger. WordPress has a great import function to move the posts across, which does most of the initial work for you.
However, WordPress won’t by itself set up redirects for the old Blogger to new WordPress permalinks. The two platforms shrink post titles to URLs differently, so it’s not as simple as matching WordPress permalink structures to Blogger’s under the Settings.
I did see one plugin which was supposed to migrate Blogger permalinks automatically, but it didn’t work and also didn’t cover the full scope I needed – I also have legacy static html links to remap into a totally different site structure. To achieve this I turned to the excellent Redirection plugin from John Godley.
Getting Inside The Database
The plugin allows you to manually set up redirects with a very friendly interface, but there’s no fun migrating 70 blog posts by hand. This is where SQL can come to our rescue!
VERY IMPORTANT: take a full backup of your database before you start messing around with SQL. In theory this is a pretty low risk operation, but, you never know!
The Blogger import utility saves custom fields for every imported post; the Blogger permalink is held in the
blogger_permalink field; custom fields are stored on the
post_meta table with an obvious ID based backlink to the original table. This makes one half of the mapping very easy to set up.
The other half is slightly more subtle, because WordPress permalinks are not actually stored on the
wp_posts table. Every post does have a GUID, but this is based on what its permalink was when you did the import – and if you imported when you created the blog and set your WordPress permalinks later, this will not reflect the post’s current permalink URI. Ideally we’d like the 301 to point to the real end URI, so we need to get a little creative and rebuild the permalink in the way WordPress does it, from the post metadata.
My permalink structure looks like this:
This can be rebuilt using the following string manipulation in SQL:
Notes on the SQL functions:
CONCAT just combines all of its arguments together into a single string;
LPAD is used to pad the left of the string with 0s, as the month and day are always 2 digits long;
DAY extract the relevant fields from the post’s creation date/time.
Given this data, we can easily create an automatic import SQL statement for moving the data across:
INSERT INTO wp_redirection_items (url,action_data,regex,group_id,status,action_type,action_code,match_type,last_access,position)
SELECT M.meta_value AS url,CONCAT('/',YEAR(P.post_date),'/',LPAD(MONTH(P.post_date),2,'0'),'/',LPAD(DAY(P.post_date),2,'0'),'/',P.post_name,'/') AS action_data,0 AS regex,1 AS group_id,'enabled' AS status,'url' AS action_type,301 AS action_code,'url' AS match_type, 0 AS last_access, 69 as position
FROM wp_postmeta M, wp_posts P
WHERE M.meta_key='blogger_permalink' AND M.post_id=P.ID AND P.post_status='publish';
Run this through PHPMyAdmin, refresh the Redirection admin page, and you should now find that all of your permalinks have been moved across. Note that we set the position field to an arbitrary constant, here 69, so we can easily delete the inserted rows if we messed up and then try again, without upsetting any other redirects already set up.