WordPress: SQL to get all Categories

If you’re trying to get all categories in WordPress with a real, raw, mysql sql statement, this is the type of query you can use:

SELECT *
FROM wp_term_relationships
LEFT JOIN wp_term_taxonomy
   ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.taxonomy = 'category'
GROUP BY wp_term_taxonomy.term_id

This is how you can get posts of a certain category / taxonomy. I have a few extra lines commented out that could also be used:

SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE 1
#AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN (13)
#AND wp_term_taxonomy.term_id IN (1,2,3,4,5,6,7)
GROUP BY wp_posts.ID
ORDER BY ID

Update: Omar asks:

cool.. but how can I get the NAME of those categories ??

Well, try this:

SELECT *
FROM wp_term_relationships
LEFT JOIN wp_term_taxonomy
   ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
GROUP BY wp_term_taxonomy.term_id

or just for the names:

SELECT wp_terms.name
FROM wp_term_relationships
LEFT JOIN wp_term_taxonomy
   ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms on wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
GROUP BY wp_term_taxonomy.term_id

Related Posts:

  • No Related Posts
This entry was posted in Social Media, Tech Tips, Web Development and tagged , , , , , . Bookmark the permalink.

8 Responses to WordPress: SQL to get all Categories

Leave a Reply

Your email address will not be published. Required fields are marked *