WordPress is really a force to be reckoned with as far as CMS go. With a huge community behind it, a plethora of features and frequent updates, it has become the defacto CMS to use. Other than a robust admin section, it’s also very easy to create templates for the front end as well.
Having said the above however, there might arise a need to query the WordPress database directly. Suppose you need to create a jQuery Mobile website using something other than php and you need to tap into the WordPress CMS for the posts.
An easy way to do this is to create a custom view within the WordPress database. The main table that houses the posts in WordPress is called “wp_posts”, and together with its related tables we can get all the data we need:
And here’s the SQL code:
SELECT DISTINCT 'wp_posts'.'ID' AS 'ID', 'wp_posts'.'post_author' AS 'post_author', 'wp_posts'.'post_date' AS 'post_date', 'wp_posts'.'post_content' AS 'post_content', 'wp_posts'.'post_title' AS 'post_title', 'wp_posts'.'post_excerpt' AS 'post_excerpt', 'wp_posts'.'post_status' AS 'post_status', 'wp_posts'.'post_parent' AS 'post_parent', 'wp_posts'.'post_type' AS 'post_type', 'wp_posts'.'menu_order' AS 'menu_order', 'wp_posts'.'comment_count' AS 'comment_count', 'wp_users'.'display_name' AS 'display_name', 'wp_posts'.'guid' AS 'guid', 'wp_terms'.'name' AS 'name', 'wp_terms'.'slug' AS 'slug' FROM ( ( ( ( 'wp_posts' JOIN 'wp_term_relationships' ON (( 'wp_posts'.'ID' = 'wp_term_relationships'.'object_id' )) ) JOIN 'wp_term_taxonomy' ON (( 'wp_term_relationships'.'term_taxonomy_id' = 'wp_term_taxonomy'.'term_taxonomy_id' )) ) JOIN 'wp_terms' ON(( 'wp_term_taxonomy'.'term_id' = 'wp_terms'.'term_id' )) ) JOIN 'wp_users' ON(( 'wp_users'.'ID' = 'wp_posts'.'post_author' )) ) WHERE ( ( 'wp_posts'.'post_type' = 'post' ) AND ( 'wp_posts'.'post_status' = 'publish' ) AND ( ( 'wp_terms'.'slug' = 'news' ) OR ( 'wp_terms'.'slug' = 'videos' ) OR ( 'wp_terms'.'slug' = 'photos' ) ) ) ORDER BY 'wp_posts'.'post_date' DESC
The query looks for all posts that have been marked as published, with categories that have slug “news”, “videos”, or “photos”. You can certainly add more arguments to the query, like the “post_date” and so on. It all depends on your needs.
We can then save the SQL code above into a view of its own (let’s call it “view_all_posts”), and run a simple query against it to select the latest 10 posts:
SELECT * FROM view_all_posts LIMIT 0, 10
Once we have our custom query, we can simply loop through it using our application language and output the content the way we want it.