How does WordPress link posts to categories in its database?

0 Views

At present I am displaying a list of the last 5 posts in a site’s blog in its footer using this mysql query: 

SELECT post_title, guid, post_date FROM wp_posts WHERE post_type = 'post' AND post_status = 'Publish' ORDER BY post_date DESC LIMIT 5

How can I edit this query to restrict the search to a particular category id? I thought it would be as simple as looking for a category field in the posts table but it isn’t!

Post Views: 0

Related posts

Leave a Reply

1 comment

  1. The relations of the WordPress database is available in the database diagram.

    In your particular case it is:

    wp_posts.ID
    ->wp_term_relationships.object_id
    ->wp_term_relationships.term_taxonomy_id
    ->wp_term_taxonomy.term_taxonomy_id
    ->wp_term_taxonomy.term_id
    ->wp_terms.term_id

    For querying you need to use an SQL join:

    SELECT p.ID, t.term_id
FROM wp_posts p
LEFT JOIN wp_term_relationships rel ON rel.object_id = p.ID
LEFT JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
LEFT JOIN wp_terms t ON t.term_id = tax.term_id

    But it should be noted that the wordpress database might change at any time, and you should use the WordPress provided mechanisms (such as query_posts) to filter posts from the database.