0

I am extracting data from my Wordpress database to analyse site structure. Works pretty good so far but I would like to include the posts main category in the table to enable further analysis. This is my current query:

SELECT post_name, post_content FROM wp_posts WHERE post_type='post' AND post_status='publish'

How can I enhance this query to include the main category in the result? I am pretty simple when it comes to SQL queries and would love to learn more.

0

You need to JOIN the term tables with the posts table, something like this:

SELECT
    p.post_name,
    p.post_content,
    t.name
FROM wp_posts p
JOIN wp_term_relationships tr ON ( tr.object_id = p.ID )
JOIN wp_term_taxonomy tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )
JOIN wp_terms t ON ( t.term_id = tt.term_id )
WHERE
    p.post_type='post'
AND
    p.post_status='publish'
AND
    tt.taxonomy = 'category'

Let me know if you have any questions!

  • 1
    Fantastic. Thanks for writing this. The only thing I had to change was the wp prefix for table names as I use the multi-network plugin. – Peter Prevos Apr 3 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.