Continuing on with WordPress’ database manipulation functions, we’re going to be talking about a class that will allow you to do more complex SQL queries, while still utilizing the WordPress API. The WPDB class provides many functions for doing direct and assisted database interactions. It is the preferred method for access and modifying the WordPress database, as it attempts to do so in as safe as way as possible.

The functions of the WPDB class are contained in $wpdb, which may need to be globaled if you’re using it within a function or outside of the plugin or theme directories. Now, let’s go over a few of the functions within the WPDB class.

Running SQL Queries

We can use the WordPress API to easily send SQL queries to WordPress’ database. This is the preferred method to interact with the WP database when using SQL (as opposed to using mysql_query()). It works like this:

[code='php']$wpdb->query('query');[/code]

If there are any results to this query, the number of rows affected will be returned, and the result will be temporarily stored in $wpdb->last_results. If there are no results, 0 will be returned. If there is a MySQL error encountered, it will return FALSE.

Protecting Queries From SQL Injection Attacks

Before sending SQL queries to the database, we should always take precautions to protect against SQL injections. We can easily do this by using $wpdb->prepare. You should prepare your query whenever you’re using SQL to interact with the database (when you’re using a prebuilt function for easily accessing the database, it isn’t usually necessary). Here’s an example of preparing a query:

[code='php']$wpdb->query( $wpdb->prepare("
INSERT INTO $wpdb->postmeta
( post_id, meta_key, meta_value )
VALUES ( %d, %s, %s )",
10, $metakey, $metavalue) );[/code]

When using $wpdb->prepare, replace any data you’re submitting to the database through your SQL (eg. variables, string, integers) with %s for a string and %d for an integer. Then, list (in order) the strings, integers or variables you want to replace the %s and %d‘s with as parameters in $wpdb->prepare.

Next week

Next week we’ll continue to go over more functions of the WPDB class.