This is not the first time I’ve mentioned this, but one of the challenges that come with talking about writing direct database queries to update information in the WordPress database is that you leave yourself open to something like:
Yeah, but there’s an API to do something just like that.
And, in many cases, that’s right. I’m a big fan of using ’em, too. But there are times where direct, parameterized database queries can be a more optimal choice.
This is, of course, contingent on the environment in which you’re working the requirements of your project.
So this is yet another post that’s going to be an illustration of how to use $wpdb to quickly update information based on metadata (without using something like WP_Query or WP_Meta_Query to handle it).
Database Queries to Quickly Update Data, Part 1
I’m going to be breaking this out into two parts because the initial function that I’m going to show you are doing more than one thing.
I’m not a fan of that.
For now, though, let me lay out the problem at hand
- I’m importing a bit of information from a third-party API.
- The information is mapped to the wp_posts table as well as the wp_postmeta table.
- There’s certain metadata that’s criteria to dictate the status of a post. Or, more directly, if a piece of metadata is present then a post should have its post status set to draft so that it doesn’t show up in the main query.
To address this, two things need to happen:
- We need the IDs of the posts,
- And we need to change the status of the posts.
Before we set up the queries and the rest of the code, let’s say the meta key we’re using is creatively called acme-status. And with that, let’s get started.
1. Grab the Post IDs with the Associated Meta Key
First, we’ll create a function called setInactivePosts. This is more generic than it would be if you’re working in a specific domain, but you get the point.
Then we’re going to set up a query that will retrieve all of the post IDs that have that specific meta key:
Here, we have an array of results. But before we do anything, we need to make sure we have data on which to operate.
2. Exit Early?
Before we move forward with updating the information, though, we should check to see if there are any results. And if there aren’t?
Then we can duck out.
3. Update the Post Status
But if there are results, we should iterate through them and update their post status:
So what’s it look like all together?
4. All Together
When we put this larger-than-necessary function together, this is what you see:
But in the next post, we’ll reorganize this a bit to make it more object-oriented.
A Note for Database Administrators
For those of you are are well-versed in SQL-fu, then you know this can likely be tackled in a single query; however, the purpose of this post and the post after that is two-fold:
- To show how to use $wpdb to achieve something quickly,
- To show how to break out a procedural method of doing something into an object-oriented method of doing something.
With that said, thanks for all the queries. 👍🏻