mysql

Find and Replace Text on Every Post

| 0 comments

There have been multiple instances where I’ve needed to go over a site with a sweeping “find and replace” statement, to replace instances of one phrase and replace it with another.

For example, you can use it to replace the URLs of images in all of your posts, if you move from Blogger for instance, or change domains, or to change markup for syntax highlighting if you change plugins, to Crayon Syntax Highlighter for instance.

You can do this with a plugin like Search and Replace, but I’m going to show you the direct way to remove the need for yet another plugin.

Log into your database

The first step is to log into your database, using your web host’s tool. This is usually phpMyAdmin, especially if you have a good host, like HostGator.

At this point, I won’t permit you to go any further without taking a full backup of your database. Since you’re going to be directly modifying your database, you need to have a backup in case things go wrong, or you change your mind. So select your database and click on the Export tab, and download every single table.

Now you’re ready to make the changes. You first need to know which table to edit; if you haven’t edited your database prefix, it will be wp_. If you have, it will be whatever you set it to.

If you are working with WordPress Multisite, you’ll want to make sure you edit the correct table as there are multiple posts tables; one for each site. The master site will be wp_posts, whereas the following sites will take the syntax wp_2_posts for site 2, wp_3_posts for site 3, etc.

Execute the SQL command

To execute a SQL command, simply select the database you want to edit, and click on the SQL tab. The command you want to use takes the following format. In this example, I’m changing the domain for images from doitwithwp.com to theukedge.com in the wp_2_posts table.

For all that build-up, it only takes a couple of milliseconds for MySQL to execute the command and change the text/code on every post in your site.

What interesting situations have you used this for?

Categories: Code & Snippets, The Basics | Permalink

What next?

Hire me

If you couldn't quite manage this yourself, find it too intimidating, or just don't have the time to do it, you can always hire Dave to do it. Please get in touch so that we can discuss your needs.

Leave a comment

If you have a question, update, or comment about the tutorial, please leave a comment. I try and respond to every comment, though it may take a few days, so please check back soon.

Let a WordPress Expert help you

Do you want to try this, but feel like you need a helping hand, in case something goes wrong? My service, The WP Butler, gives you access to WordPress expertise whenever you need it. Better yet, I'll keep your site backed up, updated and secure, so that you don't have to worry about it. It's all part of the service. Use coupon DIWW and save 15% on all plans.

Visit The WP Butler

Author:

Dave has been tinkering with WordPress for many years, and he now shares his WordPress knowledge here on Do It WIth WordPress to help others realise its impressive power. He can also be hired to help with your WordPress needs. Dave, who is British, is married to his best friend, Marti, with whom he has a beautiful daughter, Ellie. When he's not dabbling with WordPress, he's probably eating Triscuits or hummus, watching an indie film or British TV show, spending time with friends or family, or exploring the world.

Leave a Reply