As much as I am a fan of certain WordPress APIs for querying the database such as:
- WP_Query,
- WP_User_Query,
- WP_Meta_Query,
- and so on
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:
- always make sure the query is parameterized (especially in the case of user input),
- 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:
- parameterization,
- 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.
All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. The
preparemethod 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).
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?)
