While generating a CSV of old-and-new URLs for a site I’ve been busy migrating to WordPress, I ran into this brilliant bit of semi-working code to get the permalink for Posts using a pure SQL query (for MySQL):
SELECT wpp.post_title, wpp.guid, wpp.post_date,
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 ) AS permalink
FROM wp_posts wpp
JOIN wp_options wpo
ON wpo.option_name = 'permalink_structure'
WHERE wpp.post_type = 'post'
AND wpp.post_status = 'publish'
ORDER BY wpp.post_date DESC;
References:
Very good! tanks!!!
I dont understant about wpp and wpo? pls help me out.
Thanks a lot Buddy
cool… concise
SELECT
wpp.post_title,
wpp.guid,
wpp.post_date,
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
) AS permalink
FROM
wp_kympstfnsd_posts wpp
JOIN wp_kympstfnsd_options wpo ON wpo.option_name = ‘permalink_structure’
WHERE
wpp.post_type = ‘post’
AND wpp.post_status = ‘publish’
ORDER BY
wpp.post_date DESC;
Does anyone have this for pages instead of posts?