Throughout this series, we've been looking at the various facilities that make it possible to treat WordPress as a foundation for web application development.

Thus far, we've covered a lot of ground:

• We've talked about how WordPress is more of a foundation rather than a framework.
• We've discussed the nature of the the Event-Driven Design Pattern.
• There's been a discussion of Email, User Management, Saving Data, Retrieving Data
• ...and more.

In the most recent articles, we've been talking a look at how to handle queries against the WordPress database through the use of WP_Query and WP_User_Query.

In this article, we're going to round out the discussion by talking about how we can run direct SQL queries against the database.

Direct Queries Against The Database

Specifically, we're going to look at the standard operations for SELECT, UPDATE, INSERT, DELETE, and more, we'll take a look at examples of each. Then, we'll finalize our discussion by talking about parameterization so that we're able to write secure queries.

With that said, let's look at the available operations, examples of each, and how we can incorporate them into our work.

SELECT

For those of you who are new to writing SQL queries, then it's important to understand the terms for each of the types of queries that we're doing to be executing. first, we're talking about the SELECT statement.

Simply put, SELECT statements are responsible for retrieving data from the database so that we can read the information.

One of the most basic examples that we can give is how to retrieve a post title from the wp_posts table:

// Always globalize $wpdb
global $wpdb;

// Select a single variable - the post title of the first post
$title = $wpdb->get_var( "SELECT post_title FROM $wpdb->posts WHERE ID = 1;" );
echo $title;

Granted, this assumes that you're familiar with the database schema, but we've covered this in past articles.

Of course, within the context of WordPress, we can't simply define a query and have it execute—instead, we have to make sure that we're passing it to the proper API. Enter $wpdb.

Directly from the Codex:

WordPress provides a global variable, $wpdb, which is an instantiation of the class already set up to talk to the WordPress database.

The $wpdb object can be used to read data from any table in the WordPress database (such as custom plugin tables), not just the standard tables that WordPress creates.

So there's a caveat here that we've not yet encountered in our work up to this point: The $wpdb global variable is global which means that anytime that we want to access it, we need to make sure that we prefix the global keyword before it.

global $wpdb;

// Select an entire row of information from the first post
$info = $wpdb->get_row( "SELECT * FROM $wpdb->posts WHERE ID = 1;" );
print_r( $info );

// Get all of the titles of posts (and pages and custom post types) that have an ID less than 10
$titles = $wpdb->get_col( "SELECT post_title FROM $wpdb->posts WHERE ID < 10;" );
print_r( $titles );

// Retrieve a generic result set of post IDs and post titles from the posts table where posts have an ID less than 10
$results = $wpdb->get_results( "SELECT ID, post_title FROM $wpdb->posts WHERE ID < 10;" );
print_r( $results );

If this is new to you, no worries—we'll be taking a look at exactly how to do this in this article.

Those who are more advanced are more than capable of handling more complex queries.

Additionally, take close notice of which type of queries return single variables, and which type of queries return collections as this will dictate how you can manage the information once it's retrieved. Perhaps you echo it back to the page, or perhaps you end up looping through it.

INSERT

Of course, retrieving information is but one way in which data is managed within the WordPress database. After all, in order to retrieve something, you have to have data that's actually stored within the database tables.

Although the WordPress API makes this relatively easy to do from a programming perspective (especially through the use of some of the APIs we've just reviewed in the past two articles), there may be times where writing your own queries to insert information is the way to go.

Note that in all of the examples that we'll be looking at, we're taking a look at some relatively simple queries. This is done so that those of you who have never written SQL within the context of WordPress (or in any context, really) are able to easily follow along.

Inserting data is something that needs to be done with care, not simply because you're writing table to one (or multiple) tables, but because to want to make sure you aren't going to collide with data that already exists.

Although databases can have restrictions that allow you to do this, I find that it's always safe to take preventative measures at the code level to, say, check if something exists before inserting it. That way, you can perform an update rather than an insert (which we'll take a look at momentarily).

But if you're sure that you're ready to insert information, here are an examples to get you started.

global $wpdb;

// Into the posts table, insert a published post with a title and content with the arbitrary post ID of 9999
$wpdb->insert(
	'wp_posts',
	array(
		'id'	       => 9999,
		'post_title'   => 'Inserted Post',
		'post_status'  => 'publish',
		'post_content' => 'Example content for a post inserted via direct query.'
	),
	array( '%d', '%s', '%s', '%s' )
);

Just as with the SELECT queries, these examples are meant to be foundational—beginners should be able to pick them up easily, advanced users should understand how to take this to next level with very little effort.

UPDATE

As mentioned in the last section, there may be times where we want to update data rather than insert new data. In cases like that, we're actually looking for the UPDATE operation as this will allow us to take an existing row of information, update the data, and then save it to the database.

Essentially, this is ideal in cases where existing information needs to be tweaked.

global $wpdb;

// Update the title and the post content of the post table row hat has the ID of 9999
$wpdb->update(
	'wp_posts',
	array(
		'post_title'   => 'Inserted Post (Updated)',
		'post_content' => 'Example content for a post *updated* via direct query.'
	),
	array( 'id' => 9999 ),
	array( '%d','%s', '%s' ),
	array( '%d' )
);

Of course, this leads us to one more operation—if we're not looking to read information, insert information, or update information, then what else are we looking to do?

DELETE

The DELETE operation is what makes it possible for us to remove data from the database directly without using any of the WordPress APIs. The power in doing this is that you're able to by pass the usual functions and conditionals that may be required to remove information.

The downside, however, is that it can be dangerous to directly remove information from the database, because without proper checks and defensive coding, then once the data is gone, it's gone for good.

global $wpdb;

// Delete the record from the database where the ID column has the value 9999
$wpdb->delete(
	'wp_posts',
	array( 'ID' => 9999 ),
	array( '%d' )
);

As we've looked at these operations, we've seen how we can work with the database directly by stepping over or avoiding the API (depending on how you want to look at it), but the last thing that we need to take a look at is exactly how to make sure that we're writing the most secure queries that we possibly can.

Parameterization

Up to this point, we've been passing data to the queries inline—this means that each time we've inserted or updated data into the database, we've done so without properly escaping it.

If we don't handle these types of conditions with care, then it leaves the door open for more malicious users to take advantage of our code and potentially inject malicious data into the database tables.

So how we prevent this from happening? In short, we leverage the prepare function that exists on the $wpdb object, and we use placeholders to represent our information. Of course, the easiest way for us to understand this is to see it in action, so let's take a look at a few examples.

global $wpdb;

$id    = 10000;
$title = "A Parameterized Post";
$content = "This post has been inserted using a parameterized query.";

$parameterized_result = $wpdb->query(
	$wpdb->prepare(
		"
			INSERT INTO $wpdb->posts
			( id, post_title, post_content )
			VALUES ( %d, %s, %s )
		",
        array(
			$id,
			$title,
			$content
		)
	)
);

Now if you've been following closely along with the rest of the information in this article, then you've already seen similar functionality of substitution such as %s, %d, and so on each of which represents a string and a number respectively.

The different, in this case, is that we're not only storing our values in variables prior to passing them to the query, but we're also passing the entire query into the prepare function that will take the query and perform proper SQL-escaping on the data to ensure we have properly prepared, secure queries.

The WordPress Codex has an in-depth article on Data Validation that should be read by anyone working on plugins, themes, and applications. In fact, I recommend reading this after the this particular post as it will expound on the information that we've discussed here.

Up Next

This article is meant to serve as a primer to direct WordPress database queries - it's by no means meant to be an exhaustive guide; however, by familiarizing yourself with the information that's here, reading the rest of the material in the Codex, and practicing these queries in your own work, you will further your skills as it relates to working with the underlying database.

In the next article, we're going to wrap up this series by reviewing everything we've talked about through the past few articles, as well as how to move forward with future projects—or applications—now that we've taken a look at everything that WordPress has to offer.