Working with Databases in WordPress

Share this article

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. wordpress-database 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

Key Takeaways

  • WordPress provides many functions to interact with databases, including the WP_Query class and related functions. However, for more complex needs, developers may use the wpdb class or create custom tables.
  • The wpdb class is a crucial tool for directly dealing with databases in WordPress. It’s essential to avoid hardcoding table names into SQL queries. Instead, developers should use the prefix property provided by WordPress to make the code more portable.
  • The wpdb class also provides helper methods for database operations, such as insert, update, and get_row, which are safer and more specific to use cases than the general query method.
  • To secure code from SQL injection, the wpdb class provides a method called ‘prepare’ that takes an SQL statement string and data that needs to be escaped. This is especially important when dealing with methods like query or get_results.
  • When needing finer control over data, creating custom tables in WordPress can be useful. The dbDelta function is recommended for creating custom database tables and handling table schema updates. It’s also important to store the database version into the options table for future reference during updates.

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

How can I secure my WordPress database?

Securing your WordPress database is crucial to protect your website from potential threats. Start by using a strong username and password that are hard to guess. Regularly update your WordPress version, themes, and plugins to their latest versions as they often come with security patches. Also, consider using security plugins that offer database security features. Regularly backup your database so in case of any security breach, you can restore your website to its previous state.

How can I optimize my WordPress database?

Over time, your WordPress database can accumulate unnecessary data such as spam comments, post revisions, and trashed posts, which can slow down your website. You can optimize your database by regularly cleaning up this unnecessary data. There are several plugins available that can help you with database optimization. Also, consider limiting post revisions, removing unused plugins and themes, and regularly updating your WordPress core, plugins, and themes.

What is the role of the wpdb class in WordPress?

The wpdb class is a set of PHP classes provided by WordPress to interact with the database. It provides methods to perform SQL queries, protect against SQL injection attacks, and fetch results. It’s a powerful tool that allows developers to interact with the database without writing raw SQL queries.

How can I backup my WordPress database?

Regularly backing up your WordPress database is crucial to prevent data loss. You can use a plugin like UpdraftPlus or BackWPup to automate the backup process. Alternatively, you can manually backup your database through phpMyAdmin. Remember to store your backups in a secure location.

How can I restore my WordPress database from a backup?

If you have a backup of your WordPress database, you can restore it using a plugin like UpdraftPlus or through phpMyAdmin. Remember to backup your current database before restoring from a backup in case something goes wrong.

How can I migrate my WordPress database to a new server?

Migrating your WordPress database to a new server can be done using a plugin like Duplicator or All-in-One WP Migration. These plugins create a copy of your website that you can then upload to your new server. Alternatively, you can manually migrate your database using phpMyAdmin and FTP.

How can I troubleshoot database connection errors in WordPress?

Database connection errors in WordPress can be caused by incorrect database credentials, a corrupted database, or issues with your hosting provider. Start by checking your database credentials in your wp-config.php file. If the problem persists, try repairing your database, or contact your hosting provider for assistance.

How can I change the prefix of my WordPress database tables?

Changing the prefix of your WordPress database tables can help protect your website from SQL injection attacks. You can change the prefix manually by editing your wp-config.php file and renaming your database tables in phpMyAdmin. Alternatively, you can use a security plugin that offers this feature.

How can I run custom SQL queries in WordPress?

You can run custom SQL queries in WordPress using the wpdb class. The wpdb class provides several methods to run SQL queries and fetch results. Remember to use the prepare method to protect against SQL injection attacks.

How can I connect to a remote database in WordPress?

To connect to a remote database in WordPress, you need to edit your wp-config.php file. Replace the database name, username, password, and host with the details of your remote database. Remember to backup your wp-config.php file before making any changes.

Firdaus ZahariFirdaus Zahari
View Author

Firdaus Zahari is a web developer who comes all the way from Malaysia. His passion revolves around (but is not limited to) WordPress and front-end development.

ChrisBdatabasedbDeltaWordPressWordPress databasewpdb
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week