Find/Replace in PHPMyAdmin

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.

1. First search for the string you’re needing to change. You do this by clicking on the “Search” tab in your database, entering the string, then “Select All” tables, and then “Go”. The results will show you where you need to go within the table structure to make your changes. I generally do this one table at a time.
2. Click on “Browse” for one of the tables your search indicated has the string you need to change. Once you’re in that table, you’ll likely find several fields with various sorts of content or information. It’s usually pretty obvious which field holds the string you need to find.
3. From this individual table, find the “Search” tab at the top and click on that. Here is the basic structure of the search/replace command:

   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');

4. Click ‘Go’. That’s it. Repeat these steps for each table the string appears in, and you should be all fixed up.

Here are some things to keep in mind though:

• THERE IS NO UNDO!! Once you make this change, there is no Ctrl-Z. There is no command in PHPMyAdmin that will take back the action. So read through your command a couple of times before you hit ‘Go’ to make sure you understand exactly what you have typed and that it’s absolutely correct. This is where sites get broken.
• Always have a recent (maybe from just before you began working in the database) of the full site before you begin doing this, unless you’re really comfortable working in databases (in which case, I doubt you’d be here reading my little tutorial in the first place).
• The database is not affected by caching issues. Your changes are immediate. They will appear immediately.

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!