Friday, February 28, 2014

How to get WordPress posts permalinks using SQL(database) alone

SELECT wpp.post_title, wpp.post_type, 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%', wpp.post_type ) ) AS permalink
FROM wp_posts wpp
INNER JOIN wp_options wpo ON wpo.option_name = 'permalink_structure'
INNER JOIN wp_options wpo_su ON wpo_su.option_name = 'siteurl'
WHERE (
wpp.post_type = 'post'
OR wpp.post_type = 'page'
)
AND wpp.post_status = 'publish'

No comments: