How to Perform Bulk Operations with wpdb in WordPress Plugin

Posted on 21st June 2023

Introduction

In WordPress, the wpdb class is used for interacting with the database. This class is located in /wp-includes/wp-db.php. In order to use it, you first need to include this file.

The wpdb class can be used to perform various database operations such as insert, update, delete, select etc. In this article, we will see how to perform bulk operations using the wpdb class.

Bulk operations are database operations that affect multiple rows in a table. For example, if you want to update the post_status of all posts in a particular category, you will need to perform a bulk operation.

Similarly, if you want to delete all posts in a particular category, you will need to perform a bulk operation.

Bulk operations are very efficient as they reduce the number of database queries. In WordPress, the wpdb class provides two methods for performing bulk operations.

They are:

$wpdb->query() $wpdb->get_results()

The first method, $wpdb->query(), is used to execute a SQL query. The second method, $wpdb->get_results(), is used to fetch the results of a SQL query.

We will see how to use both these methods for performing bulk operations.

$wpdb->query()

The $wpdb->query() method can be used to execute any SQL query. The syntax of this method is as follows:

$wpdb->query( $query, $args );

The first parameter is the SQL query. The second parameter is an array of arguments. These arguments will be substituted for placeholders in the SQL query.

The $wpdb->query() method returns an integer value. This value represents the number of rows affected by the SQL query.

If the SQL query is successful, the $wpdb->query() method will return the number of rows affected. If the SQL query fails, the $wpdb->query() method will return false.

Let us see how to use the $wpdb->query() method for performing bulk operations.

Suppose we want to update the post_status of all posts in a particular category. The SQL query for this would be as follows:

UPDATE wp_posts SET post_status = ‘publish’ WHERE post_category = ‘1’

We can use the $wpdb->query() method to execute this query. The code for this would be as follows:

$wpdb->query( “UPDATE wp_posts SET post_status = ‘publish’ WHERE post_category = ‘1’” );

Similarly, we can use the $wpdb->query() method to delete all posts in a particular category. The SQL query for this would be as follows:

DELETE FROM wp_posts WHERE post_category = ‘1’

We can use the $wpdb->query() method to execute this query. The code for this would be as follows:

$wpdb->query( “DELETE FROM wp_posts WHERE post_category = ‘1’” );

$wpdb->get_results()

The $wpdb->get_results() method can be used to fetch the results of a SQL query. The syntax of this method is as follows:

$wpdb->get_results( $query, $output );

The first parameter is the SQL query. The second parameter is the output type. The output type can be one of the following:

OBJECT – This will return the results as an object.
ARRAY_A – This will return the results as an associative array.
ARRAY_N – This will return the results as a numeric array.

The $wpdb->get_results() method returns an array of results. Each element in the array represents a row in the database table.

Let us see how to use the $wpdb->get_results() method for performing bulk operations.

Suppose we want to fetch all posts in a particular category. The SQL query for this would be as follows:

SELECT * FROM wp_posts WHERE post_category = ‘1’

We can use the $wpdb->get_results() method to execute this query. The code for this would be as follows:

$posts = $wpdb->get_results( “SELECT * FROM wp_posts WHERE post_category = ‘1’”, ARRAY_A );

The $posts variable will now contain an array of results. Each element in the array will represent a row in the database table.

Similarly, we can use the $wpdb->get_results() method to fetch all posts in a particular category. The SQL query for this would be as follows:

SELECT * FROM wp_posts WHERE post_category = ‘1’

We can use the $wpdb->get_results() method to execute this query. The code for this would be as follows:

$posts = $wpdb->get_results( “SELECT * FROM wp_posts WHERE post_category = ‘1’”, ARRAY_A );

The $posts variable will now contain an array of results. Each element in the array will represent a row in the database table.

We can now loop through this array and perform any operation we want on each post. For example, we can update the post_status of each post. The code for this would be as follows:

foreach ( $posts as $post ) { $wpdb->query( “UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = ‘” . $post[‘ID’] . “‘” ); }

Similarly, we can use the $wpdb->get_results() method to fetch all posts in a particular category. The SQL query for this would be as follows:

SELECT * FROM wp_posts WHERE post_category = ‘1’

We can use the $wpdb->get_results() method to execute this query. The code for this would be as follows:

$posts = $wpdb->get_results( “SELECT * FROM wp_posts WHERE post_category = ‘1’”, ARRAY_A );

The $posts variable will now contain an array of results. Each element in the array will represent a row in the database table.

We can now loop through this array and perform any operation we want on each post. For example, we can delete each post. The code for this would be as follows:

foreach ( $posts as $post ) { $wpdb->query( “DELETE FROM wp_posts WHERE ID = ‘” . $post[‘ID’] . “‘” ); }

Conclusion

In this article, we saw how to use the wpdb class for performing bulk operations. Bulk operations are very efficient as they reduce the number of database queries.

The wpdb class provides two methods for performing bulk operations. They are:

$wpdb->query() $wpdb->get_results()

The $wpdb->query() method can be used to execute any SQL query. The $wpdb->get_results() method can be used to fetch the results of a SQL query.

Both these methods are very powerful and can be used to perform various bulk operations.