Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does Wordpress link posts to categories in its database?

Tags:

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!

like image 216
bcmcfc Avatar asked May 17 '10 11:05

bcmcfc


People also ask

How do I link a post to a category in WordPress?

In your dashboard, click on Posts. Click on the post you want to assign to a category. Under Post Settings on the right, expand the Category option. Click Update or Publish to apply the changes to that post.

How are WordPress categories stored database?

The categories for both posts and links and the tags for posts are found within the wp_terms table. Each term features information called the meta data and it is stored in wp_termmeta.

How do WordPress categories work?

What are categories in WordPress? In short, categories are the most general method of grouping content on a WordPress site. A category symbolizes a topic or a group of topics that are connected to one another in some way. Sometimes, a post can belong to many categories at the same time.


1 Answers

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.

like image 148
nikc.org Avatar answered Sep 20 '22 15:09

nikc.org