Advanced WordPress Queries, Part 2

In the last post, we too a look at the basic WordPress database schema, queries, and how to get started with retrieving information from the WordPress database. We also took at a look at WordPress wpdb class which makes it incredibly easy to begin running queries against the database. If you’ve not read the last article, be sure to check it out as this series builds on it.

In this post, we’re going to take a look how to how to track any errors that occur while running our queries and the variety of ways for retrieving data from a WordPress database.

A Word on Error Reporting

One of the most frustrating things about programming is having to deal with various errors and warnings that occur while working on our projects. But they’re a necessary evil, right? They give us a heads up as to when we’ve done something wrong so that our users don’t experience it.

The thing is, they aren’t solely limited to application code such as PHP or client-side code like JavaScript. Databases can generate errors, too. Since we’re currently in the business of working with databases, it makes sense to turn on SQL logging.

Thanks to the wpdb object, it’s really easy to toggle on error messages when working with database queries in WordPress.

Make sure that you’ve declared your wpdb variable as global and then simply call the following function:

show_errors(); 
?>

A simple working example of viewing an SQL error is generated by the following code. Add this to any of the your theme’s PHP files (index.php or single.php would be easy) and then load the page up in a browser:

show_errors();
$result = $wpdb->get_results('SELECT * FROM $wpdb->post');
?>

Helpful, isn’t it?

If you’re done with development and testing and ready for deploy, you can disable error reporting by calling hide_errors().

Grab a Single Value

When it comes to reading data from a database, it’s possible to pull back an entire row into an array, loop through the array until you find the value that you need, and then continue working. The thing is, there are better ways to go about retrieving values from both the standpoint of code clarity and performance.

To pull back a single value, there’s no need to retrieve an entire row. Instead, you want to query just the single value. The WordPress API provides a function get_var() specifically for that purpose.

For example, say that you want to retrieve the title of the most recent post. To do this, the query will need to pull back a single post ordered by the most recent date.

get_var("SELECT post_title FROM $wpdb->posts WHERE post_status = 'publish' ORDER BY post_date DESC");
echo $last_title;
?>

Easy, huh?

Clearly, the code shows that you’re only trying to retrieve a specific variable (or value) and you’re not having to spend cycles on the overhead of looping through a collection or pulling more information than you actually need.

Retrieve an Entire Row

On the flip side, let’s say that you actually want to retrieve an entire row (also called a record) of data. Perhaps you want to do some type of display on all of the information associated with something in the database or maybe you need to examine several values related to a single record.

Similar to the get_var() function, WordPress also provides the get_row() function that is used exactly for this purpose: it retrieves a single row of data and returns it in one of three formats – an object, an associative array, or a numerically indexed array.

For purposes of this example, we’re going to retrieve the results into an associative array where the value is accessible by keying off of the column name.

In keeping consistent with our previous example, let’s say that we want to pull back all of the information related to the last post. Note that we’re adding ARRAY_A as a second parameter – this controls how the results are returned:

get_row("SELECT * FROM $wpdb->posts WHERE post_status = 'publish' ORDER BY post_date DESC", ARRAY_A);
?>

From here, we can print out certain values:

Print the entire array:

Or even loop through the results:

Pulling back an entire row is ideal when you need to get multiple pieces of information or all of the information associated with a single row.

Read an Entire Column

Of course, sometimes retrieving a row is not at all what we’re after. Perhaps we’re creating an archives page and are looking for an easy way to pull back all of the post titles for the given blog.

In this case, you’re looking to retrieve an entire column. Similarly, WordPress provides a get_col() function exactly for that.

So, as mentioned above, let’s attempt to pull back all of the post tiles that exist in the system:

get_col("SELECT post_title FROM $wpdb->posts WHERE post_status = 'publish' ORDER BY post_date DESC");
?>

The results are returned in a numerically indexed array such that we can loop through the or access them by their numeric keys:

What about Generic Results

Obviously, pulling back values, rows, and columns from the WordPress database isn’t terribly complicated; however, there are times during which we’re looking to pull back a variety of results. On top of that, there are always going to be times where we need to loop through information to display it on the screen.

To that end, WordPress provides a generic get_results() function that is ideal for this situation. Specifically, the get_results() function will return an array in which each row is located at an index in the array.

Let’s that we’re looking to display the titles and publish date for all of the posts that are currently scheduled to go live but aren’t actually published and then display their information on the screen:

get_results("SELECT post_title, post_date FROM $wpdb->posts WHERE post_status = 'future' ORDER BY post_date DESC", ARRAY_A);

foreach($scheduled_posts as $post) {
	echo $post['post_title'];
	echo $post['post_date'];
}
?>

get_results() provides amazing flexibility especially when you couple it with doing more advanced queries such as joining with other tables.

Coming up next…

Having this kind of access and flexibility with the WordPress database really takes theme and plugin development to a new level. Taking this and mixing it with custom post types, taxonomies, and other aspects of WordPress can make for some really powerful tools.

But we’re not limited to reading data, either. In the next post, we’ll take a look at how we can manipulate data that already exists in the database and how we can introduce new values of our own.