Although the WP Query is great and provides a lot of flexibility, you can actually take your development efforts a step further with more advanced querying of the WordPress database. Throughout this series, we're going to take a look at the advantages of advanced queries, how we interface with the WordPress database, and how we can retrieve, update, and insert new and existing data.

Last month, we took a look at the Beginner's Guide To The WordPress Loop. The post was a solid overview of WordPress' Loop and how it's used within the context of the theme. In the post, Braden also gave a more in-depth look at WP Query showcasing how you can customize the results that are returned prior to jumping advantage of the Loop. Today, we are going to look at the WordPress database, how we interface with it, and the advantages of advanced queries.

A Look at the WordPress Database

Before digging too deeply into querying WordPress' underlying database, it's important to understand its layout. We don't necessarily need to examine every column of every table, but a general understanding can go a long way.

Generally speaking, the WordPress tables and their responsibilities are as follows:

• wp_comments: Where comments are stored
• wp_commentmeta: Meta data associated with each comment
• wp_links: Where data is stored based on WordPress Links
• wp_options: All of the options under Administration > Settings are stored here
• wp_posts: Post and Page data are stored here
• wp_postmeta: Meta data associated with each post
• wp_terms: Categories and Tags for both posts and pages
• wp_term_relationships: Data representing the relationship between posts, categories, and tags
• wp_term_taxonomy: Descriptions for tags, links, and/or categories as stored in wp_terms
• wp_users: Where each user account is stored
• wp_usermeta: Meta data associated with each user.

Easy enough, right? Using your favorite database front end, you can drill down into each of these tables for a closer look - the columns are well named and easy to understand.

You can view the overall schema and read a much more in-depth description of the database at the Codex.

The Advantages of Advanced Queries

As flexible as WP Query is and as easy it is to construct custom queries, it's still limiting especially when you're looking to do advanced theme development or advanced plugin development.

For example, the WordPress API makes it easy to query for the number of comments per post but what happens if you want to pull back the number of comments for each user per post (or per page) and order them by date or number of how many times they commented? Obviously, you'd need to write an advanced query.

Of course, this is a simplistic example but as soon as you're clear on the database schema and understand that you can perform typical SQL queries based on the table, you have the ability to write some really powerful, really cool functionality into your next project.

QUERYING THE DATABASE

Luckily, the WordPress API makes it easy to connect to and query the underlying database. Similar to WP Query, WordPress provides the wpdb class that we can use in order to interface with the database. This particular class is used to read, write, and update the WordPress database tables. It also provides a set of methods that make it easy to ensure our queries are safe from SQL injection or other malicious attacks.

To take advantage of wpdb, we must declare it as global. This will give us access to the instance of the class within the scope of our function (or class). For example:

global $wpdb;

To see an example of the class in action, we can pull back the total number of posts that currently reside in the database by running the following query:

$post_count = $wpdb->get_results("select count(*) from $wpdb->posts");

Note here that rather than selecting the data from wp_posts, we're selecting the data by using $wpdb->posts. This is a convenience function of the class that allows us to easily retrieve data regardless of the table's prefix.

This query returns the results into an Array object that's referenced via $post_count. You can examine the results by:

print_r($post_count);

The full script should look like this:

<?php
global $wpdb;
$post_count = $wpdb->get_results("SELECT COUNT(*) FROM $wpdb->posts");
print_r($post_count);
?>

For purposes of this example, you can drop this into any PHP file in your theme to test it (just make sure to remove it :).

Next Up

Obviously, we've just scratched the surface of what's possible with the wpdb class but it's important to understand the foundation on which we're working before diving in too deeply. Still, learning how to interface with the WordPress database opens up a ton of possibilities.

In the next post, we'll take a look at the different ways that we can retrieve data from the database and some of the pitfalls to avoid while doing so.