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.

6 Responses to WordPress: SQL to get all Categories

  1. Martin Oviedo says:

    Where i can define de name of custom post?

    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

  2. W says:

    @Michael DeMutis I suggest posting you question on either stackoverflow.com or wordpress.stackexchange.com

  3. Michael DeMutis says:

    Thank you this really helped me. But I have a further question.. what if I wanted to select all posts in a category but then also by a price range of a meta_key and only return the values of a specific_meta key.

    My application I am trying to do 3 select boxes through jquery, and chain them. So the first one selects a neighbourhood (this is category, the second one selects a price range) .. i need my SQL to return only the number_of_rooms meta_key values..

    To just select the number_of_rooms based on category I’m using this:

    $sql = "SELECT DISTINCT meta_value 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) LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1 #AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ($_POST[id]) #AND wp_term_taxonomy.term_id IN (1,2,3,4,5,6,7) AND meta_key = '_lwi_bedroomsCount' GROUP BY wp_posts.ID ORDER BY meta_value ASC";

  4. omar says:

    Excelent thanks… I’ve read that some people uses wordpress API.. like: query_posts(array(‘category__and’ => array(2,6))); and stuff like that. http://codex.wordpress.org/Template_Tags/query_posts

    Seems to be a better way to deal with wordpress db. still I don’t know how to execute those functions in php. What do u think is best?

  5. W says:

    Hi omar, I updated the post. Take a look at the update

  6. omar says:

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

Leave a Reply

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