Wordpress Database SQL query help needed

Question

Hi,

I've written a PHP script to access the latest item from the wordpress database, which it does. But I need to use it twice, once for the latest item from a specific category, and another from a differerent category...

But right now I cannot figure out how to put the query together.

The post has a post_parent, which in another table, called wp_term_relationships, is referred to as object_id, and has a term_taxonomy_id, which then relates to a different table, called wp_terms where the term_taxonomy_id is now term_id and then you have the category slug name available to select...

I really cannot understand how this query would work though.

I've made a really crap mock up of it, to try to "visually" explain what i'm trying to do...

SELECT *
FROM `wp_posts`
WHERE post_status = 'publish'
AND (SELECT term_taxonomy_id FROM wp_term_relationships WHERE object_id = post_parent)
AND (SELECT slug FROM wp_terms WHERE term_id = term_taxonomy_id)
ORDER BY ID DESC
LIMIT 1

Really would appreciate some help... Thanks.

Answer 1

Use this SQL to get the most recent published post in CAT_ID (the ID for the 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_posts.post_status = 'publish'
    AND wp_term_taxonomy.taxonomy = 'category'
    AND wp_term_taxonomy.term_id = CAT_ID;
ORDER BY wp_posts.post_date DESC
LIMIT 1

You can re-use this SQL changing the value of CAT_ID to get posts from other categories.