How to Write a MySQL Query to Fetch All WordPress Posts with Category Name

I run this website on wordpress powered by a mysql database. Very often I want to take a look at all the published posts on the site. By quickly looking at the list of post titles I can avoid posting duplicate or similar articles. If you have a wordpress blog with plenty of posts, you may need a list of all your wordpress posts along with the category name. You can then check whether you already have a post on the topic you are planning to publish. In this article I will provide four MySQL queries which you can run on your wordpress mysql database to get a list of your published posts with category name.

The following mysql query prints all the published wordpress posts grouped by category name. Note that the query uses the default table names used in wordpress. If you are using a cloud provider such as Dreamhost, you may have a different name for your wordpress tables. In that case replace the table names in the following query.

SELECT wp_posts.post_title AS ‘Title’, wp_terms.name AS ‘Cateogry’
  FROM wp_posts
  INNER JOIN wp_term_relationships
  ON wp_posts.ID = wp_term_relationships.object_id
  INNER JOIN wp_terms
  ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
  INNER JOIN wp_term_taxonomy
  ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  ORDER BY wp_terms.name;

The following mysql query prints all the published wordpress posts with most recent one printed first. This allows you to analyse how frequently you are posting articles and take a look at your recent or old posts.

SELECT wp_posts.post_title AS ‘Title’,
       wp_terms.name AS ‘Cateogry’,
       wp_posts.post_date AS ‘Date’
  FROM wp_posts
  INNER JOIN wp_term_relationships
  ON wp_posts.ID = wp_term_relationships.object_id
  INNER JOIN wp_terms
  ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
  INNER JOIN wp_term_taxonomy
  ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  ORDER BY wp_posts.post_date DESC;

You can customise the above queries in different ways for your specific requirements. For example, the following mysql query prints posts only in a specific category,

SELECT wp_posts.post_title AS ‘Title’,
       wp_terms.name AS ‘Cateogry’
  FROM wp_posts
  INNER JOIN wp_term_relationships
  ON wp_posts.ID = wp_term_relationships.object_id
  INNER JOIN wp_terms
  ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
  INNER JOIN wp_term_taxonomy
  ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
  WHERE wp_posts.post_status = 'publish'
  AND wp_posts.post_type = 'post'
  AND wp_term_taxonomy.taxonomy = 'category'
  AND wp_terms.name='Android';

The following mysql query only prints posts with more than 10 comments,

SELECT wp_posts.post_title AS ‘Title’, wp_terms.name AS ‘Cateogry’
    FROM wp_posts
    INNER JOIN wp_term_relationships
    ON wp_posts.ID = wp_term_relationships.object_id
    INNER JOIN wp_terms
    ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
    INNER JOIN wp_term_taxonomy
    ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE wp_posts.post_status = 'publish'
    AND wp_posts.post_type = 'post'
    AND wp_term_taxonomy.taxonomy = 'category'
    AND wp_posts.comment_count > 10
    ORDER BY wp_terms.name;

Do you have a question on the above article or do you have a programming problem that you are unable to solve? Please email us.

Comments are closed.