How to get WordPress post permalinks directly from the MySQL database

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:

5 Comments

  1. Ken Lyle

    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;

Leave a Reply

Your email address will not be published. Required fields are marked *