Every so often I’m asked by clients to move their wordpress/ Joomla websites (which requires me to find and replace URLs) in PHPmyAdmin and MYSQL between hosts, and in doing so, I tend to test their new wordpress database installations using temporary URLs so that their current live website doesn’t experience any development down time which will cost time and money (unhappy visitors!).
Here’s a quick and easy way to find and replace content in MySQL using the PHPmyAdmin tool. I believe you can use this same command line in other Database editing tools but I’m throwing that… “I’m a designer, not a developer” card with this one as I only use PHPmyAdmin which suffices for my requirements.
I hope you find this as helpful as I do as I like to keep a little reminder for myself on how to do this.
Here’s how to find and replace in PHPmyAdmin with MySQL:
- Log in to your phpMyAdmin account area.
- Within the left-hand side panel, select the database you wish find and replace in. (You may have multple websites/databases so make sure you choose the correct one.)
- Click on the ‘SQL’ tab at the top as pictured below.
- Copy and Paste the following code into the textarea remembering to replace your-table & yourtablefield with the specific tables you want to replace from within.
- Click ‘Go’ to action this script.
UPDATE `your-table` SET yourtablefield = replace(yourtablefield,"http://www.yourdomain.com","http://www.your-new-domain.com");
Find and replace for wordpress example
Example used for replacing the domain/ URL with a new domain across all wordpress posts and pages. Follow the steps above and just copy this code instead remembering to change yourdomain.com and your-new-domain.com to your own preferred choice.
This can be changed with anything you desire such as fixing typos or image filenames etc.
UPDATE `wp_posts` SET post_content = replace(post_content,"http://www.yourdomain.com","http://www.your-new-domain.com");
Find and replace GUID in WordPress using phpMyAdmin
This handy find and replace will update all of the posts’ guid column with your new production URL. If, like me sometimes you build and design website in a staging/ test/ development environment then this can be missed. And when you carry the database over to production the old URL will be in place. the guid is used for a lot of things internally, but one of impacts with SEO is that if a user mis-types the URL and the website has permalinks turned on, WordPress will redirect the user to the guid url if there is a minor typo. So it can be quite crucial.
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldwebsite.co.uk', 'http://www.newwebsite.co.uk');
As always, I appreciate your comments and feedback. This find and replace script for phpmyadmin can be tailored for many uses.