As much as I am a fan of certain WordPress APIs for querying the database such as:

I’m also a fan of querying directly against the database when needed.

As you’ll notice, there’s a consistent way that I go about doing this and since there are times where I’ve been doing this more frequently, as of late, here is a basic format for working with database queries in WordPress.

If I do this, though, there are generally do things that I keep in mind:

  1. always make sure the query is parameterized (especially in the case of user input),
  2. return in the information in the form of an associative array.

The first case is for security; the second is more or less for convenience to make iterating through the data simpler via PHP array functions.

Database Queries in WordPress

First, it’s important to understand two concepts:

  1. parameterization,
  2. array_column

These are the core of how I go about writing my queries and working with the results of them.

1. Parameterization

You can read more about this in the Codex, but parameterization is a way that we’re able to write queries that work to prevent against SQL injection.

Database Queries in WordPress: Parameterization

From the Codex page:

All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. The prepare method performs this functionality for WordPress, which supports both a sprintf()-like and vsprintf()-like syntax.

That is to say that the method supports tokens such as %s, %d, and %f when passing information into the query. I’ll show an example of this later in the post.

2. Working with Array Columns

Results of such queries can be returned in a variety of different ways. Namely, as an object, a numerically indexed array, or an associative array. More often than not, I like to use arrays (and I’ll show how to do this in WordPress later in this post).

Database Queries in WordPress: array_column

The reason, though, is so I can take advantage of array_column. From the PHP manual:

array_column() returns the values from a single column of the input, identified by the column_key. Optionally, an index_key may be provided to index the values in the returned array by the values from the index_key column of the input array.

This means that if you have an array and it has a bunch of results each of which have the same column, you can easily simply the information by passing the array into a method and then passing the column name into a method.

The result? A numerically indexed array of the values. This makes it much easier to iterate through the data through array_map, using for, or using foreach.

A Stub For Querying the Database

With that rationale in place, here’s the stub that I normally use whenever I’m working directly with database queries:

And here’s an example with a couple of different piece of information so you can see how it works:

Querying against the wp_user_meta table might seem silly since get_user_meta is a valid API function, but I thought it’d be an easy query to understand given what it is I’m trying to explain in this past.

Anyway, I can’t provide the query nor can I handle the case when the results may come back with multiple columns (in that case, you’ll have to work with something different than array_map, but that should be easy enough, right?)

Category:
Articles
Tags: