Roger Stringer   About ▾

I'm Roger Stringer, a DevOps engineer, developer, author, chef, speaker, dad. Founder of Flybase.

Moving All Your Links

Code

When I moved this blog from wordpress, I changed the permalink structure, that meant a lot of broken links. But I had hundreds of links, I didn’t want to handcode those, so I went to the database and wrote a query to convert it:

SELECT
    CONCAT("redirect 301 ",REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(
        wpo.option_value,'%year%',date_format(wpp.post_date,'%Y')),
        '%monthnum%',date_format(wpp.post_date,'%m')),
        '%day%',date_format(wpp.post_date,'%d')),
        '%postname%',wpp.post_name ),
        '%category%',wpc.slug ) ) as permalink,
    CONCAT_WS("/",wpo_su.option_value,date_format(wpp.post_date,'%Y'),date_format(wpp.post_date,'%m'),date_format(wpp.post_date,'%d'),wpp.post_name) as new_permalink
FROM wp_posts wpp
INNER JOIN wp_options wpo on wpo.option_name='permalink_structure' and wpo.blog_id=0
INNER JOIN wp_options wpo_su on wpo_su.option_name='siteurl' and wpo_su.blog_id=wpo.blog_id
INNER JOIN (
    SELECT wtr.object_id ID, max(wpt.slug) slug
    FROM wp_term_relationships wtr
    INNER JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id=wtr.term_taxonomy_id AND wtt.taxonomy='category'
    INNER JOIN wp_terms wpt ON wpt.term_id=wtt.term_id
    GROUP BY wtr.object_id
) wpc ON wpc.ID=wpp.ID
WHERE wpp.post_type = 'post' AND wpp.post_status = 'publish'

What this does is spit out two fields, one is the old link, the other is the new link:

redirect 301 /2010/03/this-is-a-test    http://rogerstringer.com/2010/03/01/this-is-a-test

The new permalink field is directly related to Second Crack’s link formatting, but you can easily change this however you want.

I then copied the two fields and saved them to my .htaccess as they look above, so I ended up with several hundred lines of links to new addresses.

Theoretically related posts


Roger Stringer spends most of his time solving problems for people, and otherwise occupying himself with being a dad, cooking, speaking, learning, writing, reading, and the overall pursuit of life. He lives in Penticton, BC.

Connect: Twitter |  Google+
This content is supported by readers like you.