You are currently browsing articles tagged wordpress.

How to mass-edit wordpress using SQL

I was asked to share a little snippet/bit of code that I recently used to update the video embed tags that were on a good 1,013 of the posts i’ve written over the last 5 or so years here on brentter.com. I know there are a few plugins which use regex commands to go through the posts but I learned the hard way back when working on WordPress-based datafeed stores that they not only bring the server to a screeching halt, but usually result in a few posts being either skipped or edited incorrectly. Keep in mind that back then these stores were typically on their own fully dedicated server with nothing on them but the store, I can only imagine what type of calls I would have gotten had I been on some sort of shared-hosting environment and tried the same plugin method – I would have taken out not only my own site but every site hosted by other clients on the same box (just imagine if you woke-up to find your website was down because some strangers’ blog plugin maxed out the server IO/capacity on their own hosting account which meant the entire server, including your websites & email were down while the datacenter was hopefully working on getting the entire server back online). Having been the victim of others knocking me offline with run-away scripts let’s just say it’s not a great way to start out your day. Especially when dealing with large companies where you might not have someone able to fix the problem for hours because shared-hosting accounts are typically at the bottom of any sysadmins list when dealing with a large client-base. Needless to say, this method is easy, but more importantly won’t cause your site to blow up, in fact will take very little effort & time to complete without having to rely on a 3rd party plugin.

So let’s get started, don’t worry, it’s rather simple and i’ve included code examples to help you. If you are hosting your wordpress blog on a vps or dedicated server you can use the same commands using the SQL terminal prompt, but if you’re more comfortable with CPANEL or any other type of GUI backend, phpmyadmin or any similar type script will allow you to query the database just as easily. So for the sake of those unsure of how mysql works, i’m going to walk you through using an application installed on almost every shared-hosting & web-based control panel called phpmyadmin. If you’re using Cpanel it looks like this:

Cpanel view of phpmyadmin

What you’re going to want to then do is select the database that WordPress uses (it typically starts with ‘wp’ if you used an auto-installer) then hit the button up top that says SQL. I’ve included another screenshot in case this is your first time manually editing a database.

Click On The SQL Tab

Now for the fun part (BTW, before attempting this you should always backup your database just in case there’s a mistake). You’re first going to be issuing it a series of locate commands to find both the start of the embed script as well as the end. In this case it started with
<script type="text/javascript">

Then at the end of the video embed code would be the line which actually calls the player script –
<script src="http://mypreviousvideohost.com/video_player.js" type="text/javascript"></script>

So now that we know what to tell the server to look for as far as a beginning and end of the video code we’re going to use three simple MYSQL functions named replace, substr and locate.

The format of the first portion is telling it to locate the beginning portion of the script in the “post_content” database table (essentially the content of every single post). That’s just:

LOCATE ('<script type="text/javascript">', post_content)

We then need to tell it when the script ends which will take a little counting on your part as you have to feed it not only the ending line but also the number of characters between the start and end of the code you want to replace. That part looks like this (in my situation, as I previous mentioned, I found 112 characters between the start and end, your situation will most likely be different):

LOCATE('<script src='http://mypreviousvideohost.com/video_player.js" type="text/javascript">', post_content) + 112

Now that you’ve got both the start, end as well as how many characters are in-between you’re ready to use the SUBSTR function which will extract out only that portion of code in each post and replace it with whatever you want (or even delete the code if that’s what you’d prefer). Here’s what it looks like once it’s all put together:

substr(post_content, locate('<script type="text/javascript">', post_content), ((locate('<script src="http://mypreviousvideohost.com/video_player.js" type="text/javascript">', post_content) + 112) - (locate('<script type="text/javascript">', post_content))))

So now we’ve got it looking for the start, middle and end of the old code we don’t want anymore, now it’s time to add the final function which will update the tables(posts) using the replace function to either substitute it with the new code or delete it by replacing it with a blank space. Here’s the full code that you’ll type into the SQL query box in phpmysql that will go through each post saving us hours of manual labor as well as quickly getting the job done with hopefully only a minimal increase in server load:

UPDATE wp_posts SET post_content = REPLACE(post_content, substr(post_content, locate('<script type="text/javascript">', post_content), ((locate('<script src="http://mypreviousvideohost.com/video_player.js" type="text/javascript">', post_content) + 93) - (locate('<script type="text/javascript">', post_content)))), ' CODE_WE_WILL_BE_REPLACING_THE_OLD_WITH_GOES_HERE');

It should look similar to this once you’re ready to hit go:
The completed SQL query

After typing that in the SQL query box and hitting go it should respond with a green checkmark letting you know just how many rows it altered as well as how long it took to complete the search & replace. If it brings up an error you probably either are missing or have an extra character somewhere in the command you just used. That’s where the backups come in handy – Also remember that when counting characters that includes any spaces or line breaks.

If you followed the instructions and were successful then congratulations! You’ve just mass-edited your entire blog in less time than it probably took you to read this post.

Hope it helps. Btw, for those of you advertisers who run any type of store for your clients (or even yourselves), all you need is the name of the table which houses the content you’re looking to change and this will work on non-Wordpress setups as well. Good luck!


Tags: , , ,