Working with Databases in WordPress

Out of the box, WordPress provides tons of functions that can be used to interact with the database. In most cases, the WP_Query class and related functions such as wp_insert_post, update_post_meta, get_posts will be sufficient to get the job done. However, there will be times that we’re required to do something that is not provided by WordPress natively, especially when we need to deal with custom tables. In this tutorial, we’ll walk through the most important class to deal with databases in WordPress – wpdb, including a few tips and tricks that can be implemented in our development workflow later on. We’ll also touch on dbDelta that can be used to create custom tables in our plugin. We won’t cover the basics of creating your original WordPress database in this tutorial, but feel free to check out this tutorial on creating databases from cPanel

Working with the wpdb Class

wpdb is perhaps the single most important class that we use when we need to deal with database directly. It is based on the ezSQL class written by Justin Vincent, adapted to work with WordPress. The basic methods and properties of the wpdb class is already well explained on the WordPress Codex page so there’s no point to reiterate them here. Instead, we’re going to go through a few common mistakes that WordPress developers can make, how to rectify them, as well as best practices that can be applied when using the wpdb class.

Do Not Hardcode Table Names into the SQL Query

Some developers make the general assumption that the table prefix will be unchanged and use default value of wp_. A basic example of the wrong way to do this is illustrated in the snippet below:

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM wp_posts LIMIT 10');

This is of course is an over simplification of what a plugin will be actually doing, but this example shows how quickly things can go wrong. What if our users change the table prefix to something else? This can be easily fixed by replacing the wp_ string with actual properties provided by using prefix. The above code can be made portable by applying the changes as below:

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix . 'posts LIMIT 10');

Even better, if you’re dealing with WordPress’ default tables, you can skip the prefix part and instead, directly address them as properties in wpdb. Every default WordPress table is represented by a custom property in the wpdb class with the same name of table without the prefix. For example, assuming that the table prefix is wp_:

• $wpdb->posts will correspond to wp_posts table
• $wpdb->postmeta will correspond to wp_postmeta table
• $wpdb->users will correspond to wp_users table

And so on. The above code can be improved further, since we’re querying the posts table by doing things this way instead:

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->posts . ' LIMIT 10');

Use Specific Helper Methods for Database Operations

Although the query method is designed to handle any SQL queries, it’s preferable to use more appropriate helper methods. This is usually provided by methods such as insert, update, get_row and others. Besides that it is more specific to our use cases, it’s also safer as the escaping and other grunt work is taken care of. Let’s take a look at this example:

$global wpdb;

$post_id    = $_POST['post_id'];
$meta_key   = $_POST['meta_key'];
$meta_value = $_POST['meta_value'];

$wpdb->query("INSERT INTO  $wpdb->postmeta
                ( post_id, meta_key, meta_value )
                VALUES ( $post_id, $meta_key, $meta_value )"
            );

Apart from the unsafe nature of this snippet, it should run just fine with proper values. However, this snippet can be improved further by using the insert method instead. The above code can be changed to look like the following:

$global wpdb;

$post_id    = $_POST['post_id'];
$meta_key   = $_POST['meta_key'];
$meta_value = $_POST['meta_value'];

$wpdb->insert(
            $wpdb->postmeta,
            array(
                'post_id'    => $_POST['post_id'],
                'meta_key'   => $_POST['meta_key'],
                'meta_value' => $_POST['meta_value']
            )
        );

If we’re not supplying the format as a third parameter for insert method, all of the data provided in the second parameter will be escaped as a string. Plus, we can easily know what this code does at a glance, since the method name is clearer.

Properly Debugging Database Queries

By default, error reporting is turned off. However, wpdb provides two methods that can be used to toggle the state of error reporting. To turn on the error reporting feature, simply run this code.

$wpdb->show_errors();

And to turn it off:

$wpdb->hide_errors();

Another thing to note is that if we’re setting both WP_DEBUG and WP_DEBUG_DISPLAY to true, the show_errors method will be automatically called. There is another useful method that can be used that deals with errors, namely print_error:

$wpdb->print_error();

As the name suggests, it will only show the error for the most recent query, regardless of the state of error reporting. Another neat trick is to enable SAVEQUERIES in wp-config.php. This will store all of the database queries that run, times taken, and where it’s originally called from into a property called queries in the wpdb class. To retrieve this data, we can do the following:

print_r( $wpdb->queries );

Note that this will have a performance impact on our site, so use it only when necessary. Most of the time, these functions will be enough to debug what is going wrong with our code. However, for more extensive debugging and reporting, there’s always the Query Monitor plugin that can help debugging more than database queries.

Securing Queries Against Potential Attacks

To completely secure our code from SQL injection, wpdb also provides another helpful method called prepare that will take a string of an SQL statement and data that needs to be escaped. This is relevant whenever we’re dealing with methods like query or get_results.

$wpdb->prepare( $sql, $format... );

The prepare method supports both syntax of sprintf and vsprintf. The first parameter, $sql is an SQL statement that’s filled with placeholders. These placeholders can exist in three different formats:

• %s for string
• %d for integer
• %f for float

$format can be a series of parameters for sprintf like syntax, or an array of parameters that will be used to replace the placeholder in $sql. The method will return the SQL with properly escaped data. Let’s take a look at how we can achieve the process of deleting meta_key in wp_postmeta for a specific post ID:

$global wpdb;

$post_id = $_POST['post_id'];
$key     = $_POST['meta_key'];

$wpdb->query(
                "DELETE FROM $wpdb->postmeta
                WHERE post_id = $post_id
                AND meta_key = $key"
        );

Note that this is not the recommended way to delete a record in the database using wpdb. It’s because we’re leaving the code open to SQL injection, since the user input is not properly escaped and used directly in the DELETE statement. However, this can be easily fixed! We simply introduce the prepare method before doing the actual query, so that the generated SQL is safe to use. This can be illustrated in the snippet below:

$global wpdb;

$post_id = $_POST['post_id'];
$key     = $_POST['meta_key'];

$wpdb->query(
            $wpdb->prepare(
                "DELETE FROM $wpdb->postmeta
                WHERE post_id = %d
                AND meta_key = %s",
                $post_id,
                $key
            )
        );

Connecting to Separate Databases

By default, the $wpdb variable is an instance of the wpdb class that connects to the WordPress database defined in wp-config.php. If we want to interact with other databases, we can instantiate another instance of wpdb class. This benefits us greatly, because methods like insert, update and get_results are available. The wpdb class accepts four parameters in construct, which are username, password, database name and database host, in that order. Here’s an example:

$mydb = new wpdb( 'username', 'password', 'my_database', 'localhost' );

// At this point, $mydb has access to the database and all methods
// can be used as usual

// Example query
$mydb->query('DELETE FROM external_table WHERE id = 1');

If we’re using the same username, password and database host, but only need to change the selected database, there’s a handy method called select on the global $wpdb variable. This is achieved internally by using the mysql_select_db/mysqli_select_db function.

$wpdb->select('my_database');

This is also particularly useful when we want to switch to another WordPress database, but still want to retain the functionality of functions like get_post_custom and others.

Using Custom Database Tables

The WordPress default tables usually suffice to handle most complex operations. Utilizing custom post types with post metadata, and custom taxonomies and term metadata, we can do almost anything without the need of using custom tables. However, custom tables might be useful whenever we want to have finer control over the data our plugin can handle. Benefits of using custom tables include:

• Total control of the data structure – Not all types of data fits into the structure of a post, so when we want to store data that does not make any sense as custom post type, a custom table may be a better option.
• Separation of concerns – Since our data is stored in a custom table, it won’t interfere with the wp_posts or wp_postmeta tables as opposed to if we were using custom post types. Migration of our data to another platform is easier since it is not restricted to how WordPress structures its data.
• Efficiency – Querying data from our specific table will be definitely much faster than scouring over the wp_posts table that also contains data unrelated to our plugin. This is an apparent problem when using custom post types to store lots of metadata that can bloat the wp_postmeta table.

dbDelta to the Rescue

Instead of using wpdb to create custom database table, it’s recommended using dbDelta to handle all of your initial table creation, as well as table schema updates. It’s reliable, since WordPress core uses this function as well to handle any database schema updates from version to version, if any. To create a custom table initially on plugin install, we need to hook our function to the register_activation_hook function. Assuming that our main plugin file is plugin-name.php inside a plugin-name directory, we can put this line directly into it:

register_activation_hook( __FILE__, 'prefix_create_table' );

Next, we need to create the function prefix_create_table that does the actual table creation on plugin activation. For example, we can create a custom table called my_custom_table that will be used to store simple customer data such as first name, last name and their email address.

    function prefix_create_table() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE my_custom_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            first_name varchar(55) NOT NULL,
            last_name varchar(55) NOT NULL,
            email varchar(55) NOT NULL,
            UNIQUE KEY id (id)
        ) $charset_collate;";

        if ( ! function_exists('dbDelta') ) {
            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        }

        dbDelta( $sql );
    }

To maximize compatibility, we retrieve the database charset collate from wpdb. Plus, the SQL statement needs to abide by some rules to make sure it works as intended. This is taken directly from the Codex page on Creating tables with plugin:

• You must put each field on its own line in your SQL statement.
• You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
• You must not use any apostrophes or backticks around field names.
• Field types must be all lowercase.
• SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
• You must specify the length of all fields that accept a length parameter. int(11), for example.

It’s also generally a good idea to store the database version into the options table, so that we can compare them during a plugin update in case our custom table needs updating. In order to do so, we simply add this line right after we create our table using the dbDelta function:

add_option( 'prefix_my_plugin_db_version', '1.0' );

Updating the Table Schema

Using the same example as above, let’s say during development, we change our mind and we also want to store our customer phone number inside our table. What we can do is trigger a table schema update during our plugin update. Since register_activation_hook will not be fired during a plugin update, we can hook into the plugin_loaded action instead, to do our database version checking, and update the table schema if necessary. First, we add our custom upgrade function to the plugin_loaded hook:

add_action( 'plugin_loaded', 'prefix_update_table' );

The actual functions needs to do a few things:

1. We need to get the stored database version.
2. Compare them with our current database version.
3. If it’s newer, we run the dbDelta function again.
4. Finally, we store the updated database version to the option table.

For the most part, we can actually reuse the prefix_create_table function like we did above, with some minor modifications:

function prefix_update_table() {
    // Assuming we have our current database version in a global variable
    global $prefix_my_db_version;

    // If database version is not the same
    if ( $prefix_my_db_version != get_option('prefix_my_plugin_db_version' ) {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE my_custom_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            first_name varchar(55) NOT NULL,
            last_name varchar(55) NOT NULL,
            phone varchar(32) DEFAULT '' NOT NULL, //new column
            email varchar(55) NOT NULL,
            UNIQUE KEY id (id)
        ) $charset_collate;";

        if ( ! function_exists('dbDelta') ) {
            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        }

        dbDelta( $sql );

        update_option( 'prefix_my_plugin_db_version', $prefix_my_db_version );
    }
}

Notice that we do not need to use the ALTER statement, since dbDelta will take our SQL statement, compare it against existing tables and make the modification accordingly. Pretty handy!

Conclusion

WordPress is not limited to creating simple websites, as it’s rapidly moving to a full-fledged application framework. Extending WordPress via custom post types and custom taxonomies should be our main priority. However, when we need finer control of our data, it’s reassuring to know that WordPress itself provides various functions and classes like wpdb for developers to utilize. This is what makes WordPress a mature solution.

Frequently Asked Questions (FAQs) about Working with Databases in WordPress