Extracting post categories

WordPress has a table called *wp_term_relationships* to keep track of several types of relationships, including post – category.

Let’s go table by table:

1. wp_posts – holds all posts,pages, and other content. You want to poll all rows that have -> post_type = post . You might want to poll only published posts -> post_status = publish

2. wp_term_relationships – The object_id column will hold the post id’s and the term_taxonomy_id will hold the category id.

3. wp_term_taxonomy – This table will hold the information of each taxonomy. Not all taxonomies are categories in wordpress. So you will want to poll only terms that have -> taxonomy = category.

4. wp_terms – This table holds the term information. You already have the term_id in the table above, but you don’t know it’s name. You can get it from here.

Your SQL statement will look like:

SELECT wp_posts.post_title, wp_posts.ID, wp_terms.name 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_taxonomy.term_taxonomy_id=wp_term_relationships.term_taxonomy_id 
  LEFT JOIN wp_terms ON  wp_terms.term_id=wp_term_taxonomy.term_id 
  WHERE wp_posts.post_type = "post" AND wp_posts.post_status = "publish" AND wp_term_taxonomy.taxonomy = "category"