Roger Stringer   About ▾

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

Moving All Your Links


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:

        '%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
    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

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.