WordPress Sitemap Query - IT Blog

In WordPress MySQL database, we may directly query wp_posts table to get sitemap. By combining with category we can get powerful categorized sitemap.

Table of Contents

Get simple sitemap from wp_posts

SELECT id,post_title, concat('https://211cn.ca/',year(post_date),'/',month(post_date),'/',post_name) url, post_date
FROM wp_posts
where post_type='post'

Save this query as a view v_sitemap.

Get category query

Category stored in wp_terms table, it's description and hierarchy information is stored in wp_term_taxonomy table.

SELECT t.term_id,parent.name parent_cat,t.name cat,tax.description,tax.count articles,tax.parent
FROM wp_term_taxonomy tax
JOIN wp_terms t on t.term_id=tax.term_id AND tax.taxonomy='category'
JOIN wp_terms parent on parent.term_id=tax.parent

Get categorized sitemap

SELECT s.id,c.parent_cat,c.cat,s.post_title,s.url,s.post_date
FROM v_sitemap s
JOIN wp_term_relationships r on r.object_id=s.id
JOIN wp_term_taxonomy tax on tax.term_taxonomy_id=r.term_taxonomy_id
JOIN v_category c on c.term_id=tax.term_id
ORDER BY c.parent_cat,c.cat,s.post_title

Result

Author: IT Team

Comments