WordPress :: SQL query to output post_title, categories and tags

I needed to export a table of all posts with their categories and tags for the team to update ready to import back into WordPress with WP All Import.

This is the query I used. I ran it in phpMyAdmin and then used phpMyAdmin to export the result to CSV.

SELECT ID, 
post_title, 

(SELECT group_concat(wp_terms.name separator ', ') FROM wp_terms 
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id 
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
WHERE taxonomy= 'category' and wp_posts.ID = wpr.object_id ) AS "Categories" ,

(SELECT group_concat(wp_terms.name separator ', ') FROM wp_terms 
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id 
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
WHERE taxonomy= 'post_tag' and wp_posts.ID = wpr.object_id ) AS "Tags" 

FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' 
ORDER BY post_title

If this saved you an hour of mucking around, please let me know in the comments!

By |2017-03-25T12:12:32+00:00March 25th, 2017|WordPress|0 Comments

About the Author:

Melissa Freeman is a freelance WordPress developer in the Lower Blue Mountains, west of Sydney.

Leave A Comment