How to do PHPMyAdmin Find and Replace?
Find and replace is an awesome concept. I use find & replace every day in different forms – documents, my text-editor, etc. but one that I use all the time is to run a find & replace query in the PHPMyAdmin of MySQL databases.
I use this regularly to clean up WordPress databases that I’ve migrated to my hosting service. You see, WordPress stores virtually everything in a database and, unless you enjoy going table by table, field by field, searching for that one instance of a domain name that you need to change, then you will want to keep this tool handy.
It’s really simple: In the query field on the SQL tab of PHPMyAdmin run this:
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
Here is what it means for WordPress users:
- [table_name] – This is the name of the table that you’re wanting to update and along with…
- [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
- [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
- [string to replace] – This is what you want to add in its place
Just make sure that you remove the brackets, those are there to make it a little easier to read and understand. See, it’s not so bad!
Here is what an example would look like
In this example I’m looking to replace all instances of the word “capital” a financial term, with “capitol” a term related to a place where a legislative body meets. I want this change to be made in the body content on all posts and pages so the table_name will be wp_posts and the field name would be post_content.
My statement would look like this:
update wp_posts set post_content = replace(post_content, 'capital','capitol');
All you have to do is hit “go” and PHPMyAdmin will take care of the rest.
A couple things to keep in mind:
- There is no “undo”. Make sure your statement is accurate. Otherwise, you’ll be doing a find and replace for the mistake you just made.
- Tread carefully. This is your database, and it assumes, that if you’re in here you have at least some sort of idea as to what you’re doing.
- Changes are immediate. No waiting for caches to refresh (unless you’re using some sort of caching system). Your changes should be immediately visible.
I hope that helps! I use find and replace all the time and having this tool available to you will make your edits and changes much easier.