Querying the WordPress Database

The standard installation of WordPress creates the underlying database and tables.  The default database is MySQL, but MariaDB is supported as well.  You can easily work with WordPress without ever seeing the database, but should your site grow to a certain level of complexity, you will almost certainly have to get in there.  In fact, if you’re especially knowledgeable about MySQL and SQL, you can gain some noticeable performance benefits from crafting SQL statements yourself.  To that end, today’s article will give a brief overview of the WordPress database schema and how to execute queries against it.

The WordPress Database Schema

As of WordPress 3.4, the database is created with the following standardized schema:

(click image to view full size version)

WordPress Database Diagram as of version 4.4.2

As we become accustomed to writing queries against the WordPress database, we’ll focus our attention on two of the most important tables:

temp_wp_posts

Posts are the heart of WordPress; they are stored in the temp_wp_posts table. Pages and navigation menu items are also stored here.

wp_postmeta

Each post features information called “meta data”; it is stored in the wp_postmeta table. Its status as the second most queried table in WordPress is due to the fact that plugins often add their own information to this table.

The $wpdb Object

WordPress defines a class called wpdb, which contains a set of functions for interacting with the WordPress database (although it can be used to communicate with any other supported database). We never need to instantiate the wpdb() class because WordPress provides the global $wpdb object, which is an instantiation of the wpdb class. To access $wpdb in our WordPress PHP code, all we have to do is declare $wpdb as a global variable using the global keyword:

function talk_to_database() {
    global $wpdb;

    //use the variable in a query...
} 

The $wpdb object can only connect to one database at a time, so if you ever need to connect to another database, you will need to instantiate your own object from the wpdb class with your own database connection information.

Table Aliases

You should never reference a table directly within your SQL statements as this needlessly ties them to a specific WordPress installation.  You might think that the table names are fairly static, but it is possible to give them a different prefix.  In fact, this is promoted as a good security measure.

WordPress keeps an internal mapping of every table so that they may be accessed using aliases.  The aliases for the temp_wp_posts and wp_postmeta tables are “posts” and “postmeta” respectively. 

Aliases are referenced as members of the $wpdb object:

$wpdb->posts
$wpdb->postmeta

Selecting a Single Value

The get_var() function returns a single scalar variable from the database, or NULL if no result is found. Though only a single value is returned, the entire result set is cached for later use.  Here is the syntax:

<?php $wpdb->get_var( 'query', column_offset, row_offset ); ?>

• query: (string) The SQL statement. Setting this parameter to null will return the specified variable from the cached results of the previous query.
• column_offset:  (integer) The column index, starting at 0. Defaults to 0.
• row_offset: (integer) The row index, starting at 0. Defaults to 0.

Note that PHP will substitute the value of $wpdb->posts because our query string is enclosed within double quotes.

Selecting a Single Column

The get_col() function returns a one dimensional array representing a single column or an empty array if no result is found.

<?php $wpdb->get_col( 'query', column_offset ); ?>  

• query: (string) The SQL statement.
• column_offset:  (integer) The column index, starting at 0. Defaults to 0.

Although only one column is returned by the query, there is nothing preventing you from including multiple columns, such as when Selecting All using the asterisk (*).  The entire result is cached for later use, so you can access other columns in subsequent invocations by setting the query string parameter to NULL and supplying a different column_offset.

Working with the wp_postmeta Table

For this example, we’ll query both the temp_wp_posts and wp_postmeta tables.  In order to do that, it would be helpful to know more about the wp_postmeta table’s structure.

Every record in the wp_postmeta table may contain up to four pieces of data:

1. an auto generated meta_id
2. the post_id of the post that the information relates to
3. a meta_key that acts as a string identifier for the data
4. the meta_value, which is of course the data itself

Table: wp_postmeta

For instance, imagine that our WordPress blog is devoted to information about automobiles. Each post covers a particular car (e.g. 1969 Camaro SS).  Three custom fields that could be stored as metadata include the manufacturer, model, and year.  For our Camaro SS post, the metadata might look as follows:

Let’s now construct a query to fetch the post IDs and custom fields for the Chevrolet manufacturer, sorted by model and year.

Care has to be taken to treat each piece of metadata as a separate table because we need to be able to sort on the same field – the meta_value – multiple times.

$postids=$wpdb->get_col( 
     "SELECT      key3.post_id,
                  key3.meta_value AS manufacturer,
                  key1.meta_value AS model,
                  key2.meta_value AS year
      FROM        $wpdb->postmeta key3
      INNER JOIN  $wpdb->postmeta key1 
                  ON key1.post_id = key3.post_id
                  AND key1.meta_key = 'model' 
      INNER JOIN  $wpdb->postmeta key2
                  ON key2.post_id = key3.post_id
                  AND key2.meta_key = 'year'
      WHERE       key3.meta_key = 'manufacturer' 
                  AND key3.meta_value = 'Chevrolet'
      ORDER BY    key1.meta_value, key2.meta_value;"
);

Here’s a sample of the result set, including our 1969 Camaro:

post_id  manufacturer  model     year
-------------------------------------
...
3766     Chevrolet     Blazer       2002
4042     Chevrolet     Blazer       2003
4334     Chevrolet     Blazer       2004
4648     Chevrolet     Blazer       2005
78        Chevrolet     Camaro     1967
97        Chevrolet     Camaro     1968
103        Chevrolet      Camaro      1969
109      Chevrolet     Camaro     1970
120      Chevrolet     Camaro     1971
124      Chevrolet     Camaro     1972
...

Conclusion

In the next instalment, we’ll look at some of the other WordPress tables, as well as learn how to sanitize user inputs and fetch one or more rows of data.

See all articles by Rob Gravelle