Problem

Can we get the list of blog posts in the following structure?

Published Date
Author
Category
Post Title
Blog URL

Solution

[bash]

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;

[/bash]

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: