08 Feb 2011

Wordpress permalink via SQL

Today someone asked how to get the wordpress permalink from another language via SQL. Well, after a little bit of head scratching I have come up with a relatively easy way of doing this… enjoy!

SELECT
wpp.post_title,
wpp.guid,
wpp.post_date,
CONCAT(wpo_su.option_value,
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
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'
ORDER BY
wpp.post_date DESC

update 9/2 - appended %category% join and replace