Problem
Can we get the list of blog posts in the following structure?
Published Date
Author
Category
Post Title
Blog URL
Solution
SELECT p.ID,DATE(p.post_date) Date,u.display_name Author,p.post_title Title,GROUP_CONCAT(t.name) Category,
CONCAT('http://yourdomain.com/blog/',p.post_name) URL
FROM wp_posts p
LEFT JOIN wp_users u ON p.post_author = u.ID
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 AND tax.taxonomy='category')
LEFT JOIN wp_terms t ON (t.term_id = tax.term_id AND t.name!='uncategorized')
WHERE p.post_status = 'publish' and p.post_type='post'
GROUP BY p.ID
ORDER BY Date;
Tables used in the above query are:
wp_posts
wp_posts table contains all kinds of contents of an WordPress Website / Blog and the content types include the following:
- posts
- pages
- custom post types
- attachments
- links
- navigation menu items (which are stored as individual posts)
wp_users
wp_users table contains user data.
wp_term_relationships
WordPress content types are attached to the following types of data:
- categories
- tags
- custom taxonomies and terms
- post metadata
wp_term_relationships contains the relationship between WordPress contents and categories / tags.
wp_term_taxonomy
wp_term_taxonomy contains the taxonomy details.
wp_terms
wp_terms contains the details of all defined categories and tags.
The WordPress Database Structure
WordPress uses a number of database tables with relationships between them, most of these relationships are one-to-many.
One-to-many relationships occur when each record in TableA may have many linked records in TableB but each record in TableB may have only one corresponding record in TableA.
In case of WordPress, one user can have many posts that they have written related to their user record.
The diagram below is taken from the WordPress Codex and shows the database tables and how they are related / linked:
