A few Wordpress SQL queries

Posted: 02/13/2012

SQL to get all posts in WordPress for a given category id (this is assuming that you have the default “wp_” prefix on the table name and you know the “term_id” of your category):

    SELECT * FROM wp_posts  
        INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)   
        INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)    
        WHERE (wp_term_taxonomy.term_id = 23   
            AND wp_term_taxonomy.taxonomy = 'category'    
            AND wp_posts.post_type = 'post'   
            AND wp_posts.post_status = 'publish'); 

Code to select the current version of a post given that you know the ID of it from a previous query:

    SELECT * 
        FROM wp_posts
        WHERE ID=300 AND post_status = 'publish'

SQL to view all unique categories:

    SELECT distinct t.term_id, name, count
        FROM wp_term_taxonomy tt
        INNER JOIN wp_terms t on tt.term_id = t.term_id
        WHERE taxonomy = 'category'

SQL to select latest articles:

    SELECT id, post_title
    FROM wp_posts
    WHERE post_status = 'publish' AND post_type = 'post'
    ORDER BY post_date desc LIMIT 5