Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Mysql query to list all WordPress posts with categories and tags
SELECT
cat_posts.ID as ID,
cat_posts.post_title as Title,
cat_posts.post_date as Published,
CASE
WHEN cat_term_taxonomy.taxonomy = 'category' THEN GROUP_CONCAT(DISTINCT cat_terms.name SEPARATOR ', ')
ELSE ""
END
as Categories,
CASE
WHEN cat_term_taxonomy.taxonomy = 'post_tag' THEN GROUP_CONCAT(DISTINCT cat_terms.name SEPARATOR ', ')
ELSE ""
END
as Tags
FROM wp_posts AS cat_posts
INNER JOIN wp_term_relationships AS cat_term_relationships ON cat_posts.ID = cat_term_relationships.object_id
INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON cat_term_relationships.term_taxonomy_id = cat_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms AS cat_terms ON cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN wp_postmeta AS meta ON cat_posts.ID = meta.post_id
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = 'post'
group by cat_posts.ID
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.