How to Handle Database Connections with wpdb in WordPress Plugin

Posted on 19th June 2023

Introduction

If you are developing a WordPress plugin, sooner or later you will need to interact with a database. In this article, we will show you how to use wpdb class to interact with your WordPress database. WordPress comes with a $wpdb global object which allows you to run direct SQL queries, but it is recommended that you use wpdb class instead.

Creating a Database Connection

The first thing you need to do is create a database connection. You can do that by adding the following code to your plugin:

$wpdb = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

Replace DB_USER, DB_PASSWORD, DB_NAME, and DB_HOST with your database connection details. If you are unsure what these are, then you can find them in your wp-config.php file.

Running SQL Queries

Once you have created a database connection, you can run SQL queries on that connection. The $wpdb object comes with query() and get_results() methods to run SQL queries. These two methods are very similar, the only difference is that query() method returns the number of rows affected while get_results() method returns the results of the SQL query as an object.

The following example shows how you can use query() method to insert some data into your database:

$wpdb->query(
    "INSERT INTO {$wpdb->prefix}options (option_name, option_value) VALUES ('my_option', 'my_value')"
);

In the example above, we are using $wpdb->prefix variable to get the table prefix. This is a good practice as it allows your plugin to work with WordPress installations with different table prefixes. You can learn more about that in our article on how to change the WordPress table prefix.

If you want to retrieve some data from the database, then you can use get_results() method. The following example shows how you can use get_results() method to get all rows from wp_options table:

$options = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}options" );

The example above will return an array of objects. Each object will represent a row from the database. You can then loop through this array and access each column using the column name as the object property, like this:

foreach ( $options as $option ) {
    echo $option->option_name;
    echo $option->option_value;
}

Sanitizing User Input

When running SQL queries, you need to properly sanitize any user input. Failure to do so would open up your plugin to SQL injection attacks. WordPress comes with $wpdb object which has a set of helper functions to safely sanitize user input.

The following example shows how you can use $wpdb->prepare() method to safely sanitize user input before running a SQL query:

$wpdb->query(
    $wpdb->prepare(
        "INSERT INTO {$wpdb->prefix}options (option_name, option_value) VALUES (%s, %s)",
        'my_option',
        'my_value'
    )
);

In the example above, we are using %s placeholder for string values. You can also use %d for integers and %f for floats. You can learn more about different placeholder types in WordPress.

Conclusion

We hope this article helped you learn how to use wpdb class to interact with your WordPress database. You may also want to see our beginner’s guide on how to install a WordPress plugin.

If you’re not using the $wpdb global variable directly in your WordPress plugin, you may not need to worry about this section. However, if you are, it’s important to know how to properly handle database connections.

First, you’ll need to instantiate the $wpdb class. You can do this by calling the wpdb() function:

$wpdb = wpdb();

Once you have an instance of the $wpdb class, you can use it to run database queries. For example, if you want to retrieve all posts from the WordPress database, you would use the following code:

$posts = $wpdb->get_results( “SELECT * FROM {$wpdb->posts}” );

If you need to run a custom database query that doesn’t use the $wpdb class, you’ll need to use the $wpdb->query() method:

$wpdb->query( “YOUR QUERY HERE” );

Finally, when you’re finished running database queries, you need to call the $wpdb->close() method to close the database connection:

$wpdb->close();

This is how you properly handle database connections in a WordPress plugin. By following these steps, you can ensure that your plugin doesn’t run into any errors when connecting to the WordPress database.

Assuming you have a plugin that uses the WordPress database:

The $wpdb class is used to interact with the WordPress database. In your plugin, you’ll need to first create a new $wpdb object, and then you can run any database queries you need on it.

To create a new $wpdb object, you’ll need to pass in the following information:

The database hostname
The database username
The database password
The database name

You can either hard-code this information into your plugin, or you can use the WordPress constants that are defined in your wp-config.php file. For example:

$wpdb = new wpdb( DB_HOST, DB_USER, DB_PASSWORD, DB_NAME );

Once you have a $wpdb object, you can run any database queries you need on it. For example, to get all posts from the database, you would use the following query:

$posts = $wpdb->get_results( “SELECT * FROM $wpdb->posts” );

To learn more about the $wpdb class and how to use it, check out the WordPress documentation.