IT Blog

  • Blog
  • Technology
    • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
Experience IT
In a World of Technology, People Make the Difference.
  1. Home
  2. Technology
  3. CMS
  4. Wordpress
  5. WP-MySQL
  6. WordPress Sitemap Query

WordPress Sitemap Query

2018-12-01 830 Views 0 Like 0 Comments

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
  • Get category query
  • Get categorized sitemap
  • Result

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

sitemap

Loading

error
fb-share-icon
Tweet
fb-share-icon
IT Team
Author: IT Team

Tags: None
Last updated:2018-12-01

IT Team

This person is lazy and left nothing

Like
< Previous

Comments

Cancel reply
Newest Hotspots Random
Newest Hotspots Random
Rich editor not working Making web page scroll down automatically Getting data from Dapper result All Unicode Chars How to keep and display contact form 7 data Common Regular Expressions
Get event list with feature image Fix checkbox symbol Entering large number of Envira Galleries at a time WordPress Featured Image in database Adding reCaptcha for user forms in WordPress usp_Email_AutomationScorecard
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.

This site is supported by Hostlike.com