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.
UPDATE wp_2_posts SET post_content = replace(post_content, 'src="http://www.doitwithwp.com"', 'src="http://www.theukedge.com"');
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?
366
1152