vanpariyar/get_title_permalink_mysql.md

## get_title_permalink_mysql.md

      
    Raw
  

              get_title_permalink_mysql.md
            
          
    SELECT
  wpp.post_title,
  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_name)) 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'
  INNER JOIN wp_term_relationships ON wpp.ID = wp_term_relationships.object_id 
  INNER JOIN wp_terms ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id 
  INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE wpp.post_type = 'post' AND wpp.post_status = 'publish' AND wp_term_taxonomy.taxonomy = 'category'
ORDER BY wpp.post_date DESC