How to Interact With the WordPress Database
This page may contain links from our sponsors. Here’s how we make money.
WordPress is one of the best blogging platforms and general CMS systems out there. Its flexibility is a testament to the amazing codebase behind it. In this article, I want to take you to one of the “deeper” areas of the system, the $wpdb class.
The $wpdb class is a simple utility for interacting with the database directly. If you’ve been around WordPress code you’ve probably seen the get_posts() function or the WP_Query class. These let you retrieve posts in a flexible fashion, but sometimes you just need more. The $wpdb class lets you add/modify/delete any data in WordPress modularly, making it a very powerful tool. Be careful though, with great power comes great responsibility (and awesome features).
What Is wpdb?
Wpdb is a class which is based on the ezSQL class by Justin Vincent. It provides an easy way for us to talk to the WordPress database. $wpdb is am instance of this object. It is created ny WordPress, all you’ll need to do is start using it.
When Should I Use wpdb?
By talking to the WordPress database directly you can provide much more complex features. WordPress has a host of functions for users, posts, taxonomies; but what if you’re adding something different – like a time tracking tool. In some cases the offered functions just don’t cut it. This is what Wpdb is for – a secure and standardized way of manipulating the database directly.
When Should I Not Use wpdb?
In all honesty, the number of time you should not use wpdb is far greater than the number of times you should. When confronted with the openness of this system I was tempted to do everything in my own tables, using my own code. Implementing a system for managing books with Wpdb sounds great but is just daft when you can use custom post types and custom taxonomies.
Always try and use WordPress-native functionality first. If this is not possible, or would be too inconvenient, go for it.
Getting Started
Wpdb can be used anywhere. It is instantiated and globalized for you so all you need to do is start writing code. Te only exception is within functions where you will have to globalize it of course.
Before taking a detailed look at all the methods available, let’s take a look at a simple example, retrieving a list of post ids.
$my_posts = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE post_author = 24" );
There are three components to this we should talk about.
- get_col() is a method which will retrieve one column from the table in question. We can also use get_var() to get a single value or get_row() to pull a single row.
- the query itself is next which is standard SQL. You can write any query in here which will executed, the results returned in a way determined by the method used (get_col(), get_var(), etc.)
- $wpdb->posts is a variable which points to the posts table. You might be used to the posts table being wp_posts but this is not always the case. Whenever referencing tables, always use the proper variable.
Now that we’ve got that covered, the rest will be easy, we just need to go through all the available methods
Retrieving Data
When reading data from your WordPress tables you’ll want to:
- get a single piece of data (retrieving the total number of comments on all of an author’s posts)
- get a single row (get the details of the first post published after a specific date)
- get a single column (get the ids of all drafts by a single author )
- get multiple rows and columns (getting the id and title of all posts which have a comment count of 6 or more)
There are four methods which correspond to these needs, here are some simple examples;
$comments = $wpdb->get_var( "SELECT SUM(comment_count) FROM $wpdb->posts WHERE post_author = 3 " );
In the example above we’re counting the total number of comments for a given author. The $comments variable should contain a single value.
$after_birthday = $wpdb->get_col( "SELECT * FROM $wpdb->posts WHERE post_status = 'publish' AND post_date > '2013-05-12 00:00:00' " );
Once this query is executed the $after_birthday variable should contain an object, the members corresponding to the field names. You could display the post title of the post retrieved by using;
echo $after_birthday->post_title;
A word of caution here, and with any query where retrieving multiple values. Using ‘*’ to retrieve all data is usually not a good idea because it is inefficient. If we’re just using our database call to display a list of posts do we really need the ‘post_content_filtered’ and the ‘to_ping’ fields? We don’t even need the ‘post_content’, which can be pretty hefty. Make sure to only select what you really need to!
$author_posts = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE post_author = 24 AND post_status = 'draft' " );
In the example above $author_posts should be a simple one-dimensional array of post ids.
$most_discussed = $wpdb->get_results( "SELECT ID, post_title, comment_count FROM $wpdb->posts WHERE comment_count > 6 ORDER BY comment_count DESC " );
The $most_discussed variable above will be a array objects. You can iterate through this array and display all posts easily.
echo '<ul>'; foreach( $most_discussed as $item ) { echo '<li><a href="' . get_permalink( $item->ID ) . '">' . $item->post_title . '</a></li>'; } echo '</ul>';
Note that while this example works fine, it is not what I would actually use in a production environment, more on this later.
Adding to the Database
The wpdb class is prepared to add things to the database for you with the insert() method. The general use of this method is as follows:
$wpdb->insert( $table, $data, $format );
You’ll need to know what table you’re adding to, you’ll need to prepare an array of the data you’re adding and an array of the formats used. Example ensues!
Let’s presume we’re adding time tracking functionality and we have a table which allows us to store time entries. It contains the following fields: ID, start_time, end_time, job_id, user_id. We could add an entry like so, signifying the starting of a timer (hence no end time).
$table = $wpdb->prefix . 'timetracking'; $data = array( 'start_time' => '2013-06-20 17:32:23', 'end_time' => '', 'job_id' => 44, 'user_id' => 145 ); $format = array( '%s', '%s', '%d', '%d' ); $wpdb->insert( $table, $data, $format );
Notice that since we are using a custom table so we don’t have a pre-defined variable for our table name like the posts table does. In this case we can use $wpdb->prefix to get the table prefix, and just add our table name after it.
The $data array should be self explanatory, it is an array which ties the table columns and their data together.
The $format array determines the format for each column, in the same order as given in the $data array. Possible values here are %s for strings, %d for integers and %f for floats.
Once an item has been added you can access the auto-increment ID using the $wpdb->insert_id variable.
If you’ve worked with databases before you may notice the lack of escaping values here. This is because if you use the specialised methods provided by the wpdb class the values are escaped for you.
Modifying Database Entries
Continuing on with our time tracking example we can assume that whenever someone stops the timer we add the end time to the entry we made earlier.
Generally speaking we use the update() method like so:
$wpdb->update( $table, $data, $where, $format = null, $where_format = null );
First you’ll need to specify the proper table, then the data (only the ones you’re modifying), the criteria for performing the update, the format the data is in and the format the criteria is in. If formats are not given, strings are presumed throughout.
$table = $wpdb->prefix . 'timetracking'; $data = array( 'end_time' => '2013-06-20 21:13:45' ); $where = array( 'ID' => 34 ); $format = array( '%s' ); $where_format = array( '%d' ); $updated = $wpdb->update( $table, $data, $where, $format, $where_format );
As you can see, this is quite similar to how inserting is done. Values are escaped for you and the number of rows updated is returned upon a successful operation.
Running Any Query
So far we’ve taken a look at three basic operations; insert, update and select. Sometimes you’ll need to perform other operations like deleting something, or very complex updates which will require a customised query you just can’t fit into any of the available methods. In this case you can use the query() method.
$wpdb->query( "DELETE FROM $wpdb->posts WHERE comment_count < 3 AND post_author = 16 AND post_status = 'publish' " );
While this is pretty straightforward you will most likely never use the query() method on its own. This method does not escape values for you and in a vast majority of cases you’ll be using variables as parameters so we’ll need to escape ourselves – OR WILL WE?
Of course there’s a handy method named prepare() which will do the grunt work for you. Using it is a bit like using the sprintf() function. We’ll replace all our variables with placeholders and add them separately as arguments. Here goes:
$comment_count = 3; $post_author = 16; $post_status = 'publish'; $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->posts WHERE comment_count < %d AND post_author = %d AND post_status = %s ", $comment_count, $post_author, $post_status ) );
The placeholders use the same formats as before – %s for string, %d for integer and %f for float. The prepare() method takes the query (with placeholders) as its first argument, and then the values for the placeholders, in the same order as they appear in the query.
Note that even though the placeholders are used inside the query you do not need to put them in quotes if they are strings, the method will take care of all this.
In some cases you may not know the number of variables passed to your query. Luckily you can also use the prepare() method with an array as the second argument, instead of a list of values:
$wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->posts WHERE comment_count < %d AND post_author = %d AND post_status = %s ", array( $comment_count, $post_author, $post_status ) ) );
Troubleshooting Issues
Finding out why something isn’t working can be a pain when databases get involved. Are you passing the right data, did you mistype a column name, is the table name correct, and so on. There are a couple of ways to help yourself.
You can use $wpdb->show_errors() to show errors and $wpdb->hide_errors() to make sure error reporting is turned on (or off).
$wpdb->print_error() will display errors from the most recent query, if there are any.
Practical Usage
It is extremely important to be able to decide when you need to use $wpdb. It is almost always redundant to use it for selecting posts as the WP_Query class is so powerful in this regard that it is almost always a better option.
Not Using wpdb
Don’t forget that when using the WP_Query class, the get_posts() function, or any other function, multiple actions and filters are run. This makes your code much more modular which is what you’ll want if you’re a plugin or a theme developer.
With $wpdb you are interacting directly (or close enough) with the database so your code will become much more difficult to interact with.
I suggest using the posts table and subsequently the WordPress loop as much as possible, exactly because of the awesome power of WP_Query. While this isn’t something I have thought through, I would probably use a custom post type for time tracking and use postmeta for holding the timing data. Here’s why.
When you have your items in the same form as posts (i.e.: in the posts table) the time needed to write front-end code will be much less. You can use the loop, you can paginate your items automatically, you have access to the template hierarchy (if you create a single-timetracking.php you’ve just created the single post view) and so on. Your features will be easy to enable or disable without damage to other parts of the site.
Using wpdb a little
I often see code which uses wpdb to retrieve posts based on some complex criteria, followed by a foreach loop to display the posts. This will work, but I find that a more elegant solution is to retrieve ids only when possible and feeding them to a loop, something like this:
$post_ids = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE post_author IN ( SELECT user_id FROM $wpdb->usermeta WHERE meta_key = 'age' AND 'meta_value' > 32 ) AND post_type = 'post' AND post_status = 'publish' " ); global $wp_query; $temp_query = $wp_query; $wp_query = null; $args = array( 'posts_per_page' => 8, 'post__in' => $post_ids, ); $wp_query = new WP_Query( $args ); if( have_posts() ) { while( have_posts() ) { the_post(); get_template_part( 'layout', 'post' ); } } $wp_query = $temp_query; wp_reset_postdate();
First we find the ids of posts where the post author is older than 32 years. We then create a new WordPress query by feeding it these post ids and how many posts we want to show. We then loop through the returned posts and show the post using the layout-post.php file.
In case you’re wondering what all the $temp_query nonsense is, I’m just making sure that we don’t interfere with the regular WordPress Loop. $wp_query is created and populated on all pages. We want to make sure that our custom little loop doesn’t overwrite or take it’s place. We store the original loop, then we restore it at the very end.
Using wpdb Heavily
Some cases warrant the all-out use of the wpdb class. Building an in-house ad-tracking system for multiple ad types with AB testing an other such details would definitely require some wpdb magic. Whenever you need to create a custom table in the database chances are you’ll need to rely on wpdb to heave your data around.
If this is the case then making good use of this class will ensure that even your highly specialized and customized code remains as accessible and as modular as possible.
In Conclusion
As we’ve seen wpdb is an extremely useful companion for those of you looking to build custom functionality into your WordPress sites, plugins or themes. While you should avoid using if you don’t have to – if you do need it, it is a Godsend.
I hope you’ve gained some insight into the inner-workings of this excellent class. I’d love to hear if you’ve used wpdb in your own applications, how you usually use it, and any other thoughts you may have.