Useful Wordpress Database Queries

worpress_database_queries.sql

-- Get All Published Posts

select * from wp_posts where post_status='publish'

-- Given a category name, get all the tags associated with posts in the category

select distinct terms.slug, terms.name 
from 
  wp_term_relationships rel, wp_term_taxonomy txnm, wp_terms terms
where
  rel.object_id in (
              select rel.object_id 
              from 
                  wp_term_relationships rel, 
                  wp_term_taxonomy txnm, 
                  wp_terms terms
              where
                  rel.term_taxonomy_id = txnm.term_taxonomy_id and
                  txnm.term_id = terms.term_id and
                  terms.slug='".$category."'
              )
  and
    rel.term_taxonomy_id = txnm.term_taxonomy_id and
    txnm.term_id = terms.term_id and
    txnm.taxonomy = 'post_tag'
    
--Get 5 most Recent Posts

SELECT * FROM wp_posts where post_status = 'publish' ORDER BY ID DESC LIMIT 5

-- Get Posts for Tag

select
      wp.ID, wp.post_author, wp.post_date, wp.post_content
from
    wp_term_taxonomy wtt,
    wp_term_relationships wtr,
    wp_terms wt,
    wp_posts wp
where
    wtt.taxonomy = 'post_tag' and
    wtt.term_taxonomy_id = wtr.term_taxonomy_id and
    wtt.term_id = wt.term_id and
    wt.name='".$TAGNAME."' and
    wp.ID = wtr.object_id

-- GET POST AUTHOR

select wu.meta_value
from
    wp_usermeta wu,
    wp_posts wp
where
    wu.user_id = wp.post_author and
    wu.meta_key = 'nickname' and
    wp.id = '".$POSTID."'