WordPress Essentials: Interacting With The WordPress Database

"While you already use many functions in WordPress to communicate with the database, there is an easy and safe way to do this directly, using the $wpdb class. Built on the great ezSQL class by Justin Vincent, $wpdb enables you to address queries to any table in your database, and it also helps you handle the returned data. Because this functionality is built into WordPress, there is no need to open a separate database connection (in which case, you would be duplicating code), and there is no need to perform hacks such as modifying a result set after it has been queried.
The $wpdb class modularizes and automates a lot of database-related tasks.
In this article, I will show you how to get started with the $wpdb class, how to retrieve data from your WordPress database and how to run more advanced queries that update or delete something in the database. The techniques here will remove some of the constraints that you run into with functions such as get_posts() and wp_list_categories(), allowing you to tailor queries to your particular needs. This method can also make your website more efficient by getting only the data that you need — nothing more, nothing less.

Getting Started

If you know how MySQL or similar languages work, then you will be right at home with this class, and you will need to keep only a small number of function names in mind. The basic usage of this class can be best understood through an example, so let’s query our database for the IDs and titles of the four most recent posts, ordered by comment count (in descending order).

<?php
  $posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'
  AND post_type='post' ORDER BY comment_count DESC LIMIT 0,4")
?>

As you can see, this is a basic SQL query, with some PHP wrapped around it. The $wpdb class contains a method (a method is a special name for functions that are inside classes), named get_results(), which will not only fetch your results but put them in a convenient object. You might have noticed that, instead of using wp_posts for the table’s name, I have used $wpdb->posts, which is a helper to access your core WordPress tables. More on why to use these later.
The $results object now contains your data in the following format:

Array
(
  [0] => stdClass Object
     (
        [ID] => 6
        [post_title] => The Male Angler Fish Gets Completely Screwed
     )

  [1] => stdClass Object
     (
        [ID] => 25
        [post_title] => 10 Truly Amazing Icon Sets From Germany
     )

  [2] => stdClass Object
     (
        [ID] => 37
        [post_title] => Elderberry Is Awesome
     )

  [3] => stdClass Object
     (
        [ID] => 60
        [post_title] => Gathering Resources and Inspiration With Evernote
     )

)

Retrieving Results From The Database

If you want to retrieve some information from the database, you can use one of four helper functions to structure the data.

get_results()

This is the function that we looked at earlier. It is best for when you need two-dimensional data (multiple rows and columns). It converts the data into an array that contains separate objects for each row.

<?php
  $posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'future'
  AND post_type='post' ORDER BY post_date ASC LIMIT 0,4")

  // Echo the title of the first scheduled post
  echo $posts[0]->post_title;
?>

get_row

When you need to find only one particular row in the database (for example, the post with the most comments), you can use get_row(). It pulls the data into a one-dimensional object.

<?php
  $posts = $wpdb->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'
  AND post_type='post' ORDER BY comment_count DESC LIMIT 0,1")

  // Echo the title of the most commented post
  echo $posts->post_title;
?>

get_col

This method is much the same as get_row(), but instead of grabbing a single row of results, it gets a single column. This is helpful if you would like to retrieve the IDs of only the top 10 most commented posts. Like get_row(), it stores your results in a one-dimensional object.

<?php
  $posts = $wpdb->get_col("SELECT ID FROM wp_posts WHERE post_status = 'publish'
  AND post_type='post' ORDER BY comment_count DESC LIMIT 0,10")

  // Echo the ID of the 4th most commented post
  echo $posts[3]->ID;
?>

get_var

In many cases, you will need only one value from the database; for example, the email address of one of your users. In this case, you can use get_var to retrieve it as a simple value. The value’s data type will be the same as its type in the database (i.e. integers will be integers, strings will be strings).

<?php
  $email = $wpdb->get_var("SELECT user_email FROM wp_users WHERE user_login = 'danielpataki' ")

  // Echo the user's email address
  echo $email;
?>

Inserting Into The Database

To perform an insert, we can use the insert method:

$wpdb->insert( $table, $data, $format);

This method takes three arguments. The first specifies the name of the table into which you are inserting the data. The second argument is an array that contains the columns and their respective values, as key-value pairs. The third parameter specifies the data type of your values, in the order you have given them. Here’s an example:

<?php
  $wpdb->insert($wpdb->usermeta, array("user_id" => 1, "meta_key" => "awesome_factor", "meta_value" => 10), array("%d", %s", "%d"));

  // Equivalent to:
  // INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, "awesome_factor", 10);
?>

If you’re used to writing out your inserts, this may seem unwieldy at first, but it actually gives you a lot of flexibility because it uses arrays as inputs.
Specifying the format is optional; all values are treated as strings by default, but including this in the method is a good practice. The three values you can use are %s for strings, %d for decimal numbers and %f for floats.

Updating Your Data

By now, you won’t be surprised to hear that we also have a helper method to update our data — shockingly, called update(). Its use resembles what we saw above; but to handle the where clause of our update, it needs two extra parameters.

$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

The $table, $data and $format parameters should be familiar to you; they are the same as before. Using the $where parameter, we can specify the conditions of the update. It should be an array in the form of column-value pairs. If you specify multiple parameters, then they will be joined with AND logic. The $where_format is just like $format: it specifies the format of the values in the $where parameter.

$wpdb->update( $wpdb->posts, array("post_title" => "Modified Post Title"), array("ID" => 5), array("%s"), array("%d") );

Other Queries

While the helpers above are great, sometimes performing different or more complex queries than the helpers allow is necessary. If you need to perform an update with a complex where clause containing multiple AND/OR logic, then you won’t be able to use the update() method. If you wanted to do something like delete a row or set a connection character set, then you would need to use the “general” query() method, which let’s you perform any sort of query.

$wpdb->query("DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' ");

Protection And Validation

I hope I don’t have to tell you how important it is to make sure that your data is safe and that your database can’t be tampered with! Data validation is a bit beyond the scope of this article, but do take a look at what the WordPress Codex has to say about “Data Validation” at some point.
In addition to validating, you will need to escape all queries. Even if you are not familiar with SQL injection attacks, still use this method and then read up on it later, because it is critical.
The good news is that if you use any of the helper functions, then you don’t need to do anything: the query is escaped for you. If you use the query() method, however, you will need to escape manually, using the prepare() method.

$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );

To make this a bit more digestible, let’s rewrite this basic format a bit.

$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 )
$wpdb->query($sql);

As you can see, this is not that scary. Instead of adding the actual values where you usually would, you enter the type of data, and then you add the actual data as subsequent parameters.

Class Variables And Other Methods

Apart from these excellent methods, there are quite a few other functions and variables to make your life easier. I’ll show you some of the most common ones, but please do look at the WordPress Codex page linked to above for a full list of everything that $wpdb has to offer.

insert_id()

Whenever you insert something into a table, you will very likely have an auto-incrementing ID in there. To find the value of the most recent insert performed by your script, you can use $wpdb->insert_id.

$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 )
  $wpdb->query($sql);

  $meta_id = $wpdb->insert_id;

num_rows()

If you’ve performed a query in your script, then this variable will return the number of results of your last query. This is great for post counts, comment counts and so on.

Table Names

All the core table names are stored in variables whose names are exactly the same as their core table equivalent. The name of your posts table (probably wp_posts) would be stored in the $posts variable, so you could output it by using $wpdb->posts.
We use this because we are allowed to choose a prefix for our WordPress tables. While most people use the default wp, some users want or need a custom one. For the sake of flexibility, this prefix is not hardcoded, so if you are writing a plug-in and use wp_postmeta in a query instead of $wpdb->postmeta, your code will not work on some websites.
If you want to get data from a non-core WordPress table, no special variable will be available for it. In this case, you can just write the table’s name as usual.

Error Handling

By calling the show_errors() or hide_errors() methods, you can turn error-reporting on or off (it’s off by default) to get some more info about what’s going on. Either way, you can also use the print_error() method to print the errors for the latest query.

$wpdb->show_errors();
  $wpdb->query("DELETE FROM wp_posts WHERE post_id = 554 ");

  // When run, because show_errors() is set, the error message will tell you that the "post_id" field is an unknown
  // field in this table (since the correct field is ID)

Building Some Basic Tracking With Our New $wpdb Knowledge

If you’re new to all of this, you probably get what I’m going on about but may be finding it hard to implement. So, let’s take the example of a simple WordPress tracking plug-in that I made for a website.
For simplicity’s sake, I won’t describe every detail of the plug-in. I’ll just show the database’s structure and some queries.

Our Table’s Structure

To keep track of ad clicks and impressions, I created a table; let’s call it “tracking.” This table records user actions in real time. Each impression and click is recorded in its own row in the following structure:

• ID The auto-incremented ID.
• time The date and time that the action occurred.
• deal_id The ID of the deal that is connected to the action (i.e. the ad that was clicked or viewed).
• action The type of action (i.e. click or impression).
• action_url The page on which the action was initiated.
• user_id If the user is logged in, their ID.
• user_ip The IP of the user, used to weed out any naughty business.

This table will get pretty big pretty fast, so it is aggregated into daily statistics and flushed periodically. But let’s just work with this one table for now.

Inserting Data Into Our Tables

When a user clicks an ad, it is detected, and the information that we need is sent to our script in the form of a $_POST array, with the following data:

Array
(
  [deal_id] => 643
  [action] => click
  [action_url] => http://thisiswhereitwasclicked.com/about/
  [user_id] => 223
  [user_ip] = 123.234.223.12
)

We can then insert this data into the database using our helper method, like so:

$wpdb->insert('tracking', array("deal_id" => 643, "action" => "click", "action_url" => "http://thisiswhereitwasclicked.com/about/",
"user_id" => 223, "user_ip" => "123.234.223.12"), array("%d", %s", "%s", "%d", "%s"));

At the risk of going on a tangent, I’ll address some questions you might have about this particular example. You may be thinking, what about data validation? The click could have come from a website administrator, or a user could have clicked twice by mistake, or a bunch of other things might have happened.
We decided that because we don’t need real-time stats (daily stats is enough), there is no point to check the data at every insert. Data is aggregated into a new table every day around midnight, a low traffic time. Before aggregating the data, we take care to clean it up, taking out duplicates and so on. The data is, of course, escaped before being inserted into the table, because we are using a helper function; so, we are safe there.
Just deleting in bulk all at once the ones that are made by administrators is easier than checking at every insert. This takes a considerable amount of processing off our server’s shoulders.

Deleting Actions From a Blacklisted IP

If we find that the IP address 168.211.23.43 is being naughty-naughty, we could blacklist it. In this case, when we aggregate the daily data, we would need to delete all of the entries by this IP.

$sql = $wpdb->prepare("DELETE FROM tracking WHERE user_ip = %s ", '168.211.23.43');
  $wpdb->query($sql);

You have probably noticed that I am still escaping the data, even though the IP was received from a secure source. I would suggest escaping your data no matter what. First of all, proper hackers are good at what they do, because they are excellent programmers and can outsmart you in ways that you wouldn’t think of. Also, I personally have done more to hurt my own websites than hackers have, so I do these things as a safety precaution against myself as well.

Updating Totals

We store our ads as custom post types; and to make statistical reporting easier, we store the total amount of clicks that an ad receives separately as well. We could just add up all of the clicks in our tracking database for the given deal as well, so let’s look at that first.

$total = $wpdb->get_var("SELECT COUNT(ID) WHERE deal_id = 125 ");

Because getting a single variable is easier than always burdening ourselves with a more complex query, whenever we aggregate our data, we would store the current total separately. Our ads are stored as posts with a custom post type, so a logical place to store this total is in the postmeta table. Let’s use the total_clicks meta key to store this data.

$wpdb->update( $wpdb->postmeta, array("meta_value" => $total), array("ID" => 125), array("%d"), array("%d") );

  // note that this should be done with update_post_meta(), I just did it the way I did for example's sake

Final Thoughts And Tips

I hope you have gained a better understanding of the WordPress $wpdb class and that you will be able to use it to make your projects better. To wrap up, here are some final tips and tricks for using this class effectively.

• I urge you to be cautious: with great power comes great responsibility. Make sure to escape your data and to validate it, because improper use of this class is probably a leading cause of hacked websites!
• Ask only for the data that you need. If you will only be displaying an article’s title, there is no need to retrieve all of the data from each row. In this case, just ask for the title and the ID: SELECT title, ID FROM wp_posts ORDER BY post_date DESC LIMIT 0,5.
• While you can use the query() method for any query, using the helper methods (insert, update, get_row, etc.) is better if possible. They are more modular and safer, because they escape your data automatically.
• Take care when deleting records from a WordPress (or any other) database. When WordPress deletes a comment, a bunch of other actions also take place: the comment count in the wp_posts table needs to be reduced by one, all of the data in the comment_meta table needs to be deleted as well, and so on. Make sure to clean up properly after yourself, especially when deleting things.
• Look at all of the class variables and other bits of information in the official documentation. These will help you use the class to its full potential. I also recommend looking at the ezSQL class for general use in your non-WordPress projects; I use it almost exclusively for everything I do.

---

© Daniel Pataki for Smashing Magazine, 2011."

Posted in: Database,Tutorial,WordPress