How To Edit Thousands of WordPress Posts With Just One SQL Command

How To Edit Thousands of WordPress Posts With Just One SQL Command:

Owning and running a WordPress blog is a lot of fun, especially when you first get started. Designing the site or picking a cool theme, and writing up those first few posts in anticipation of the huge swarm of people that you’re certain are headed your way. It doesn’t take long for reality to hit, and before you know it you don’t like the theme, the visitor flow is no more than a trickle, and you realize that your web host doesn’t quite have the horsepower you need.

Growing a blog takes years, and a whole lot of pain and struggle – I’m sure Aibek would testify to that. Then there are the major transitions, when you realize that you have to make some major changes, and it’s going to take a whole lot of work.

There are a number of major changes I’ve had to make on my own blog, but one of the most difficult was due to the fact that I had used a certain manual method to insert Google adverts into my blog articles.

Changing Posts With A Single SQL Command

The problem is that after several years of running the blog, I had nearly 1,000 posts with that Google advert manually inserted. If I ever wanted to make a change, that’s a thousand posts I’d have to edit.

I wanted to use that cool in-post template add-on that I wrote about in 2010. This would automate the insertion of adverts moving forward, but if I used it without removing all of those existing adverts, I’d end up with two ads in the old articles.

To remove the old advert , I had to perform a little bit of SQL magic, and I’m going to share that with you today. The action takes place in phpMyAdmin, and you’ll need to select the database of your blog from the left menu.

The code that you’re going to use to strip sections of your content will be in the SQL tag. What I mean by that is the code I’m going to show you, you’ll be able to use to remove any content that exists on multiple blog posts, from 2 to 200 – or more. The only requirement is that what you want to remove has to have a common start text and an end text – like a Google advert has a common start tag and an end tag.

This is what my newer blog entries look like, with the ad manually inserted right after the “more” tag on every page, aligned on the right.

The way this code is laid out on the page looks like this:

As you can see, I have a “script type” start tag that I can use at the start of every advert, and a “script src” tag I can use at the end.

So here’s how it works. You need to tell the SQL “update” command that you want to completely wipe out that block of your page. You’ll need to understand three basic MySQL functions – replace, substr and locate.

First, use LOCATE to tell the command the locations of the start and end points. In my case, I use the strings at the start and end of the section I want to remove. The start location on the page of the beginning is:

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

The end location is trickier. Locate gives you the start of the string, so you have to add the number of characters to the very end of the string to get the ending location.

LOCATE('<script src='http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">', post_content) + 93

Next, you’re going to need to tell the “REPLACE” function exactly what text you want to replace. You do this by extracting the actual string out of the post content, using the SUBSTR function. You have to pass it the starting location (which you now have) and the length. The length you need to calculate by subtracting the starting position from the last position (which you also have).

Here is what that looks like, with the code above inserted into the SUBSTR function:

substr(post_content, locate('<script type="text/javascript">', post_content), ((locate('<script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript">', post_content) + 93) - (locate('<script type="text/javascript">', post_content))))

Looks crazy right? But if you look carefully, you’ll see there’s the three sections embedded – post content, start location, and length.

Now that you have the exact text that you want to remove from your posts, all you have to do is tell the REPLACE function to replace it with a space. Just copy the code above into the following command:

UPDATE wp_posts SET post_content = replace(post_content, string_to_replace, replacement_string);

Like this:

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

Running the script above, if all of the syntax is properly aligned, you should see the following successful results in phpAdmin.

Now, when I reload the posts in my web browser – voila! The ad has magically been stripped away.

Don’t get me wrong, it didn’t work on all ads, because I had some older ones that had slightly different formatting on the start and end. Like this page.

But with a little bit of tweaking, just changing the precise tags for start/finish, I was able to re-run the SQL and wipe those out as well.

This technique will work to remove any content at all that is in your WordPress post content. That’s the beauty of having the posts in a MySQL database, you can simply run SQL commands to manipulate thousands of posts all at once – saving a tremendous amount of manual work. Just make sure to back up your database before you start!

Do you have any other neat uses for this SQL command? Give it a shot on your own WordPress database and see how well it works for you. Share your experiences and feedback in the comments section below.

Image credits: Shutterstock