WordPress and SQL: baby steps

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:

  • A select query that makes seeing what’s in the database easier. The code snippet below selects all posts where comment status is closed.
  • 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 post post_type.

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 update.

With the update, we are actually going to set the comment_status field to open, but only if they are currently closed, and only for published posts of the post post_type.

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!

6 thoughts on “WordPress and SQL: baby steps

  1. The only trouble with direct SQL queries to WordPress tables (whether via $wpdb or not) is that you’re by-passing everything: actions and filters defined by core, plugins and themes. For example, if you delete all comments to a post via direct SQL, the comment count in wp_posts is not updated for you. If you delete a post via SQL, WordPress won’t bust your page cache, XML sitemap cache, etc. If you change your post status from anything to publish, your Jetpack won’t send e-mails to your users about your new posts, and won’t cross-post that article to Twitter, etc.

    Worse is when you have persistent object caching turned on, because it will store the entire wp_options table to memcached or wherever, so any updates to the options table via SQL will not be reflected. Same for posts, pages and other data stored in object cache.

    Bottom line: it’s okay to do SQL, but you should be extremely careful and always test your queries locally, before running them in a production environment.

    1. Konstantin, thanks for chiming in. All of your points are really great. I should probably make further note (I tried to already, but see I can more) that people should be careful with what they try to do.

      I use direct SQL queries mostly with changing some things out in a development environment. And usually with sites that have never been in WordPress before. So I’ve not had to worries about changing post status before. But I try not to delete anything through direct SQL.

      Most of what I’m trying to say is useful is changing simple things, and I used the comment example because that’s the most common thing I have to change. For instance, I just launched a site where comments were closed on every post because they had previously hard coded FB comments in their theme. Since there were thousands of posts, the query in my example was the best way to turn comments on so I could switch to the official Facebook plugin.

      But yeah, I completely agree with you, especially on your bottom line :)

  2. First off, props on putting yourself out there and showing some of the things you’re learning (regardless of level of experience).

    At the risk of self-promotion (which I promise I’m trying to more relevant than promotional ;), this kind of hits on some of the comments I had in this post where developers where talking about being hesitant to share their work.

    That said, Konstantin is right. Editing the tables using a SQL frontend or even inline queries may not yield the result that you’d expect – that’s why I’m a big fan of using the API’s.

    Case in point: This week, I had someone want to change meta values for all all of their posts. Sure, writing a query woud’ve been easy, but I instead wrote a WP_Query, looped through the results, and saved the data that way.

    So, I guess, my question is are you doing this as an exercise in learning SQL or as an alternative way of updating database records to circumvent the API?

    1. Definitely! I would always recommend someone use a built in API if possible. Your example of changing that meta data is a good one. I’ve had similar instances before and that’s exactly how I handled it. But I’ve also had instances where WP_Query wasn’t handling it well, so using that method I had to query 500 posts at a time and offset and run again. It was a little “janky”.

      So, while for things that need to go through the standard WordPress api’s, I agree with you and will note it in the post. But for some simple things – and only for when you know you won’t screw up other stuff, I think direct SQL queries can be useful. Thanks for your comment, Tom!

Leave a Reply