Understanding and using $wpdb object in WordPress

WordPress being a highly customizable platform one can create a large number of plugins on top of it. These plugins can range from simple functionality to a completely complex plugin which performs a large number of tasks. WordPress provides a large number of functions to perform different tasks which read or write data in the WordPress database like reading the contents of a post or creating new categories in your WordPress.

These functions mostly suffice the needs of most of the plugins built on top of WordPress. But in some cases we need direct access to the database to perform some queries or operations directly on the database. In such cases WordPress does allow us to interact with the database. In this article we are going to see how we can directly interact with the database.

Understanding wpdb and $wpdb

For performing database operations WordPress provides a class wpdb which is present in the file – wp-includes\wp-db.php. This class abstracts the database functions for WordPress and most WordPress functions directly or indirectly use this class. This class is based on the ezSQL class. You can create an object of this class to perform database operations but WordPress creates an object of this class during the load of WordPress.

This object is $wpdb and is a global object. So in case we want to perform any database operation we should use this global $wpdb object and call functions on them. So in case you want to use $wpdb in some function you should declare it as a global variable before using it as follows.

[php]
function someFunction() {

global $wpdb;

//………………

//Some Operation on database using $wpdb

//………………

}[/php]

Fetching some values from database

There are a lot of functions on $wpdb to fetches values from the database. Some of these functions are specialized to get one value, one row or one column from the database table.

get_var

The get_var function takes in a query and returns one value which can be directly assigned to a variable.

So in case we want to find out the number of rows in the posts table we can use the following code which uses get_var

[php]$numberofpost = $wpdb->;get_var( "SELECT COUNT(*) FROM $wpdb->;posts;");

echo "The number of rows in posts table are:" .$numberofpost;[/php]

get_var just returns one value but it caches the entire row in the $last_result variable of the class.

get_row

This method is useful to get a complete row from a query. This method takes in a query and also takes a parameter to indicate in which format the output should be.

If we want to fetch the row from the posts table with ID = 1 we can do it with get_row as follows

[php]$post = $wpdb->;get_row("SELECT * FROM $wpdb->;posts WHERE ID = 1");

print_r($post);[/php]

The first parameter to get_row is a query and the second parameter can be one of the following values

• OBJECT ( this is the default value ) – In this case the object is an objects with the columns of the table as the members of the object.
• ARRAY_A – In this case the result is returned in for of an associative array.
• ARRAY_N – in this case the result is in for of an numerically indexed array.

get_col

This method is useful to get one column from a query. The output of this function is an array. So in case we want to get all the titles of the post we can use the following code

[php]$posttitles = $wpdb->;get_col( "SELECT post_title FROM $wpdb->;posts;" );

echo "The titles are";

foreach ( $posttitles as $title )

{

echo $title;

}[/php]

get_results

This method is used to get an output of a generic query which has output as multiple rows and multiple columns. The default output for this function is array of objects in which each object represents one row of the result.So in case we want to write a query to fetch all the post which have a particular meta key defined on them we can do it using get_results as follows

[php]$result =$wpdb->;get_results("SELECT * FROM $wpdb->;posts , $wpdb->;postmeta where $wpdb->;posts.ID = $wpdb->;postmeta.post_id

AND post_status=’publish’ AND meta_key=’meta_key_name’");

print_r($result);[/php]

Using the prepare method

Sometimes we might take some data from the user in case of a form or something else and make it as a part of the query. This can cause problems in case there is SQL injection on the query. In SQL injection the attacker injects a query in a forms value which you will use to create a database query. This queries can be as hazardous as dumping the complete database to deleting the database. For more details on SQL injection you can visit https://en.wikipedia.org/wiki/SQL_injection.

The wpdb prepare method should be used to protect oner against SQL injection. The prepare method is very similar to the printf method which takes in a query and then parameter values to be substituted.

So in case we want to get the details of the post of a particular ID in which the ID is got from a form field we should use the prepare function to sanitize the query as shown below.

[php]function get_the_post_details($id)

{

global $wpdb;

$query = "SELECT * FROM $wpdb->;posts WHERE ID = %d";

$post = $wpdb->;get_row($wpdb->;prepare($query,$id));

print_r($post);

}[/php]

Insert and update data using $wpdb

We have till now seen how can you retrieve data from the WordPress database using the wpdb class. Now we are going to see what are the functions provided by wpdb to insert and update data by the $wpdb class.

Insert function

The insert function of wpdb lets you insert one row in the database table. The insert function takes the arguments as the name of the table, an array of values and an optional array of formats.

To use the insert function to add a meta value for a particular post can be done with a following code

[php]
$wpdb->;insert(

$wpdb->;postmeta,

array(

‘post_id’ =>; 1,

‘meta_key’ =>; ‘price’,

‘meta_value’ =>; ‘500’

),

array(

‘%d’,

‘%s’,

‘%s’

)

);[/php]

The above code inserts a row in the postmeta table with the values for post_id as 1 , meta_key as price and meta_value as 500.

Update function

The wpdb also provides an update method using which you can update some values in a database table. The update function takes the arguments as the name of the table, array of values to change, array of values to use in where clause, optional format for values to change, optional formats for where clause.

In case we want to change meta value which we inserted above in the postmeta table can be done as follows.

[php]
$wpdb->;update(

$wpdb->;postmeta,

array(

‘meta_value’ =>; ‘750’

),

array(

‘post_id’ =>; 1,

‘meta_key’ =>; ‘price’,

)

);[/php]

In this we are making the meta value as 750 for the post_id as 1 and the meta_key as price.

Some other useful functions or wpdb

The class wpdb also provides some other useful functions which are as follows

• flush – This function is used to flush the result of previous query.

• show_errors – This function is used to enable showing database errors.

• hide_errors – This function is used to disable showing database errors.

• tables – This function returns all the tables of WordPress.

• replace – This function is used to replace a row in the database table.

• delete – This function is used to delete rows from a database table.

Conclusion

WordPress with its wide variety of functions and functionality has very few and specialized tasks left when one has to perform a query or operation on the database. In case your plugin has to create a new table in that case you might need to write wrapper functions which perform operations on that table using the $wpdb object. Database operations can be costly as well as dangerous if not performed with care. So directly database operations should be used with high caution. Regarding the tables which are installed by WordPress it is highly recommended that you use a function which is provided by the WordPress itself. So in case you find yourself writing a database query you should do a research on the internet and some WordPress forums to know if there is a WordPress function available for the same and do a direct query only when no better option is available.