Skip to content

Export a list of posts, tags and categories from WordPress

Last Updated: May 6, 2019

This complex SQL query can generate a complete table of your WordPress posts, plus a comma-separated list of both categories and tags.

SELECT
	p.id,
	p.post_title,
	p.post_type,
	GROUP_CONCAT( DISTINCT( c.name ) ) AS categories,
	GROUP_CONCAT( DISTINCT( t.name ) ) AS tags
FROM wp_posts p
JOIN wp_term_relationships cr
    ON( p.id = cr.object_id )
JOIN wp_term_taxonomy ct
    ON( ct.term_taxonomy_id = cr.term_taxonomy_id
    AND ct.taxonomy = 'category' )
JOIN wp_terms c
	ON( ct.term_id = c.term_id )
JOIN wp_term_relationships tr
    ON( p.id = tr.object_id )
JOIN wp_term_taxonomy tt
    ON( tt.term_taxonomy_id = tr.term_taxonomy_id
    AND tt.taxonomy = 'post_tag' )
JOIN wp_terms t
    ON( tt.term_id = t.term_id )
WHERE p.post_status = 'publish'
GROUP BY p.id

Adapted from this post on StackOverflow.

Posted in ,

Chris Aram

I'm a developer who specializes in whipping your technology into shape so that it makes your professional and personal life better.