I have to admit, writing a title like “WordPress and SQL” is pretty scary. I am by no means an expert. I’m very much a beginner. But I’d like to briefly walk through an introduction to working directly with the WordPress database, because as long as you know how to respect it, working directly in the database can be quicker and easier than other methods.
Edit: Based on great comments by Konstantin Kovshenin and Tom McFarlin, I realised I should have noted that when you can use WordPress functions to achieve what you need to do, do so. That way, WordPress APIs won’t be ignored by your changes (see Konstantin’s examples). But for certain tasks that you know will not have negative effects, what I describe here can be a good solution. Just be sure to test before you do these things in a live environment.
The first time you use a tool like phpMyAdmin or a third party tool like Sequel Pro (my preference), it’s a bit nerve racking. Once you make a change, there’s not an undo button.
So start off right: backup your database. A simple export should do the trick or you can use a tool like BackupBuddy.
In this simple example, we’re going to do two things:
selectquery that makes seeing what’s in the database easier. The code snippet below selects all posts where comment status is
- The second line runs an update in the posts table to change all posts that have closed comments to “open”.
The code should speak for itself. Just read it out loud, and it will help make sense. You would only run one line at a time, of course. In Sequel Pro, you have the option to run a selection, so in the query tab of Sequel Pro, I just highlight the line I want to run, and then run that selection.
The asterisk (*) in the first line means that you want to select the entire row of the given table. So we are selecting all rows in the
wp_posts table where the
comment_status field is set to
open, and limited those we select to published posts of the
We run this
select query because we want to see how many rows will be affected when we actually update. So, after we do that, we know what we should be changing with our
With the update, we are actually going to
comment_status field to
open, but only if they are currently
closed, and only for published posts of the
Once you get comfortable with this idea, you can see that much more can be done. You can replace essentially anything you want within the database with this format. However, beware, because you have a lot of power working in the database, and of course a lot of responsibility. But for changing comment status, post status, post author, various post meta fields, etc, it can be really useful.
It’s worth noting most of what you would do from a SQL client can also be done in WordPress functions, using the wbdb class. The codex article has quite a few nice snippets that you can use.
So: be careful, but have fun!