Extracting post categories

1 Views

I’m in the middle of assisting a colleague in reconfiguring a WordPress database for use on another platform. The database is essentially details regarding all the posts and accompanying metadata, etc.

One of the things I’m stuck on is determining how to correlate each post with its WordPress category. Essentially, I’d like to be able to pull out every post with its corresponding category into a spreadsheet, and to be able to do that, I clearly need to know their relationship within the tables.

Read More

Can anyone point me in the correct direction? Any help would be sincerely appreciated. Thank you.

Post Views: 1

Related posts

1 comment

  1. 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"

Comments are closed.