When retrieving information from the WordPress database I prefer to avoid using direct database queries, but there are times in which they can be useful (and it’s important that it’s done right).
One use case in which it might be helpful is when you need to retrieve a set of data of a single type. Case in point: Say you need to load all WordPress custom fields.
At the time of this writing, there’s no function of which I’m aware that will do this, so here’s how I query the database to do so.
Load All WordPress Custom Fields
There are several things to note about custom fields that are associated with a given post:
- custom fields are stored in the post meta data,
- custom fields are considered private, so they are prefixed with an underscore,
- the WordPress Coding Standards will not like this (so you may need to ignore the standards)
You can read all about them in the Codex.
And yes, it’s easy to get all of the custom information for a single post. But if you’re working on a project where you need to retrieve all of the custom fields, namely those that are private, then you need to make sure you’re querying for strings that begin with an underscore.
So to load all WordPress custom fields in the database, here’s the query I use:
A few things to note about the above query:
- I’m using the prepare function prior to get_results. This is for parameterization and security. If it’s new to you, I recommend reading this article.
- This returns all of the meta keys and meta values in an array,
- Depending how many records you’re looking to return, this may take some time (and should maybe be done in batches).
Finally, for those who are curious, indexes do not exist on the meta_value column but do on the meta_key column.
So if you opt to modify the query and are looking to use columns that have indexes, refer to meta_id, post_id, and meta_key.