I’ve recently had need of hunting down content throughout a WordPress site – URL’s that didn’t get updated automatically in a site migration, changing the way an acronym is capitalized, etc. The best way to ensure that this sort of thing is caught throughout a site run by a backend database system is actually fairly simple. Don’t be scared about digging into the database. While you can definitely break things pretty easily, as long as you have a program like Backup Buddy running on your website creating regular backups for you, you shouldn’t have too much trouble restoring to a recent working version if you happen to totally break things.
In this very brief tutorial, I’m assuming that you already know how to gain access to your mysql database through your hosting account.
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
Translation for WordPress users:
[table_name]– This is the name of the table that you’re wanting to update. Add to this…
[field_name]– This first instance of the field name tells MySQL where to make the update and where you’re looking for that bit of info to reside (this is the field within the table that you’ve just located through your basic search for the string you want to change).
[field_name]– This is the second instance of the field name, where you’re telling the function to find it, and this is the place that you want to make the change
[string to find]– This is the specific text that you are looking to REMOVE/CHANGE.
[string to replace]– This is what you want to replace the previous string with.
Just make sure that you remove the brackets.
We’re going to assume, for the purposes of this tutorial, that you’re wanting to change a URL that didn’t happen to use the ‘www’ prefix so that it does use it (for the sake of resolving a common “Error Too Many Redirects” issue). We have also found from our search that the majority of these occurrences are in the wp_posts table (you may have a different table prefix depending on your installation), and in the post_content field of that table. Your command line will look like this:
update wp_posts set post_content = replace(post_content, 'http://yoursitename.com','http://www.yoursitename.com');
Here are some things to keep in mind though:
EDIT: One of my programming guru friends pointed out that there actually is an UNDO feature, if you’re using “transactions”. Here’s a link to tell you how to take advantage of that feature. By default, though, be prepared for not having access to any sort of UNDO, unless you follow the instructions in the link I just gave.
Was this article helpful? Do you have another way of accomplishing the same thing that you’d like to share? Leave a comment below. We’d love to hear from you!