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!

FacebookTwitterGoogle+PinterestRedditEmailShare

Tags: , , ,

Move over TechStars, soon there’s going to be another big incubator in town, well, 25 minutes from town (if you live in Boulder, CO that is). Plug and Play, which is best known as one of the Silicon Valley greats, is set to finalize on the opening of their new facility here in Denver within the next year. Named The Innovation Pavilion located over in the Tech Center it will contain all the various aspects Plug and Play has become famous for. This four story, 80,000 square foot startup mecha will house both a co-working facility which unlike most currently here in Colorado will be setup for both the 1-3 person freelancer/small-business as well as much larger, closed off office areas for more established firms. In the same building will also be the new HQ for Plug and Play Colorado, bringing their unique style of startup funding to the Rockies.

Quoting the Denver Post, unlike most Colorado-based incubators like The Foundry Group or TechStars, their primary focus is not on just budding, early-stage startups but rather of a combination of long-established companies, government resources, media centers and of course entrepreneurs. With the firm’s past investment record and current roster of mentors it’s definitely having a lot of young startups catching themselves drooling thinking about what lays ahead in the upcoming months.

While LA and San Diego might be called Silicon Beach, It’s safe to say we’ve claimed the title of Silicon Mountain when you take a look at the firms already here and now with one of the largest Incubator firms in the US currently in-route to our snow capped Rockies it shouldn’t even be an argument.

Once finished the new Innovation Pavilion is going to try and give the currently massive Plug and PLay Tech Center located out in Silicon Valley a run for it’s money (or in the least, a close version but with a Colorado ‘twist’). It at any time can house and support nearly 300 startups, including a massive network of parnters and mentors who frequent the various groups. Regardless, if you’ve ever had any doubt that Boulder/Denver wasn’t as strong of a tech incubators paradise like NYC or Seattle, this should change your mind.

The entire building should be open and fully built out within a year, however they currently have a small office already working out of the city awaiting their new digs.

It’s a good sign for Colorado indeed. Welcome Plug and Play to Colorado.

Tags: , ,

This is nuts. Acoustic live version by Michael Winslow of Whole Lotta Love originally by Led Zeppelin though via beatbox, acoustic guitar + almost perfect matching of Robert Plants’ voice on tv talkshow Senkveld med Thomas og Harald…friggin amazing.

Tags: , , , , , ,

« Older entries