CMS - Wordpress - Coding - Databases - PHP

Using $wpdb and the WordPress Database Functions

As I talked about previously in our Introduction to WordPress Plugin Development tutorial, one of the great things about working in WordPress is the ability to piggy-back on WP’s database connection. It not only saves you code and time, but also makes your plugin or theme run faster and more securely. Today, I’d like to get into more detail about exactly what database functions are available to you inside of WordPress.

$wpdb: Every Single Time

WordPress security is a complex topic. I touched on it here and will doubtless write more on it in the future, but one big (and easy) way to secure your plugin or theme is to always use the $wpdb object for your database queries.

$wpdb is the global database access layer for WordPress. Have a look at the two queries below. They do the same thing, in the same number of lines of code, but they do it in significantly different ways.


   // First, using traditional mysql_ PHP functions

   if ($findall = mysql_query("SELECT * FROM tblCustomers 
          WHERE State = 'NY'")) {
     while ($cust = mysql_fetch_array($findall)) {
       $custid = $cust['ID'];

  // Now, using $wpdb

  global $wpdb;
  $customers = $wpdb->get_results($wpdb->prepare("SELECT * FROM tblCustomers 
          WHERE State = %s", 'NY'));
  foreach ($customers as $cust) {
    $custid = $cust->ID;


They don’t really look that different. Both access a database and put the entire contents of the tblCustomers table into an array. But $wpdb is actually doing a lot more than meets the eye. The wpdb() class that the variable accesses has been optimized by WordPress developers over the years into an efficient and secure database connection. Here are some of the advantages of the $wpdb method:

  • Security: Protection against SQL injection and other database hacks
  • Efficiency: Functions are optimized and, in some cases, can auto-cache data, saving significant time on queries
  • Longevity: Even if WordPress’ table structure changes, the $wpdb functions will still work, while hard-coded queries will have to be rewritten.

The Main $wpdb Functions

There are several ways to make a query through $wpdb. You’ve already seen two of the most important functions in the wpdb() class: prepare() and get_results(). They do pretty much what they say on the tin. prepare() is a validation and sanitation function that keeps your query from making trouble. The one thing that might need a little explanation is the %s. The prepare function uses that place holder to denote that the variable in that place is a string. %d would indicate an integer and %f a float. get_results() is a generalized select function that loads the results of your query into an array that can then be parsed for the data you need.

Though get_results() works in a lot of cases, there are more specific query functions that you’ll want to use in certain situations:

If you are querying by an ID or other unique field, and you know that only one row will be returned by your query, then get_row() is a really efficient way to do so.

   $id = 7;
   $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM tblCustomers 
              WHERE ID = %d", $id));

Have another look at the prepare() function in this example too. See how it uses %d as a placeholder for the $id integer variable? If that variable had a string inside it (say, an SQL injection like "; DELETE FROM tblCustomers;, the prepare function would detect it and stop it from doing any damage to your table.

Some of the least efficient queries in straight PHP/MySQL come about when you want only one variable. Getting a single value should be simple, and with $wpdb->get_var(), it takes just one function.

   $new_customer_count = $wpdb->get_var( 
            $wpdb->prepare( "SELECT count(ID) FROM tblCustomers
            WHERE timestamp_created > %d", 1388534400 ));
   echo 'There have been '.$new_customer_count.' new customers added this year.';

We want to do more than just select existing data. Obviously, we’ll have to add more at some point too. The insert() function is pretty similar to a regular SQL INSERT query, but just rearranged a little.

   $firstname = 'Jane';
   $lastname = 'Doe';
   $city = 'Albany';
   $state = 'NY';
   $timenow = time();

   if ($newcust = $wpdb->insert('tblCustomers', 
             array('firstname' => $firstname, 'lastname' => $lastname, 
             'city' => $city, 'state' => $state, 'timestamp_created' => $timenow), 
             array(%s, %s, %s, %s, %d))) {
     echo 'New customer added.';

Notice that unlike the select query functions, I didn’t use prepare() here. That’s because validation and sanitation are built into insert(). As long as you format it right, with the proper placeholders for each variable, you should be safe from any SQL injection and other attacks.

There’s also a nice little bonus to using $wpdb->insert(). Once you’ve done the insert, if your table has an auto-increment ID column, you can get the ID using the function $wpdb->insert_id.

The last of these functions I’ll go over in detail is $wpdb->update(). This comes into play if, for example, your newest customer decides to move from chilly Albany out to the beautiful Pacific Northwest:

           array('city' => $newcity, 'state' => $newstate), 
           array('ID' => $id), 
           array(%s, %s), 
           array(%d)) {
     echo 'Customer city and state updated.';

Note that with update(), you need to set validation placeholder arrays for both the UPDATE fields (the first array with the city and state variables) and the WHERE fields (the second array that specifies Jane Doe’s specific ID).

Before we finish with this topic, there’s time for a more general question that you should always be asking when developing for WordPress:

Do You Need A Query At All?

One common mistake for beginner WordPress plugin and theme developers is writing more database queries than the situation requires. If you need a query, $wpdb is always going to be the best way to write it. But if you are querying data from native WordPress tables, there may already exist a WordPress function to get that data for you. A few examples:

  • General Information about your blog: try get_option()
  • Any field of data about a post: use get_post()
  • Custom fields associated with a post (including attached files): use get_post_meta()

In general, if you’re using $wpdb to query the tables that came with your WordPress install, you’re probably doing more work than necessary. Look for a native function.

What’s Next?

There is more to $wpdb, naturally, than fits in this post. Work on expanding your repertoire of $wpdb functions, as well as your vocabulary of native WordPress functions to cut down on the number of queries you have to use in your code. It’s great for a WordPress developer to know PHP outside of WordPress – it has its place – but only use it when you have to, and never for accessing your WordPress database. Happy programming!

About the author

Ian Rose is a web developer, blogger, and writer living in Portland, OR. In his day job, he develops WordPress plugins and custom PHP solutions, focusing on nonprofit clients. By night, he attempts to write both fiction and nonfiction. Ian's site is Seaworthy Web Solutions

Share this post

Leave a Comment

Subscribe To Our Newsletter