Roger Stringer   About ▾

I'm Roger Stringer: a father, writer, developer, consultant, chef, speaker. Founder of TheInterviewr.

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:

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.

Filed Under: Code

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+ |  Buy Me A Cup of Coffee