Mostly, we use manual find option to replace a content in MySQL database using PHPMyadmin. But when it comes to find and replace in a large table, we feel like an ant against a mountain. The only question arises in our mind is, ‘is there any shortcut for this?’. Well, the answer is yes. We do by simply executing a query to find and replace a text with new text in the entire table in MySQL using PHPMyadmin.
I had an live WordPress installation of which I need a copy in my local so that I can tweak few items. Yes, I know WordPress repo has a lot of plugins to do this job but what if the same table is not associated with WordPress. Then, the below code comes handy.
Option 1: The Code
UPDATE `wp_posts` AS `toUpdate`,
(SELECT `ID`,REPLACE(`guid`,'http://domainname.ext','http://localhost/project') AS `guid`
LIKE 'http://domainname.ext%') AS `updated` SET `toUpdate`.`guid`=`updated`.`guid` WHERE `toUpdate`.`ID`=`updated`.`ID`;
The are two or more table that needs a find and replace. Then above noted SQL option is limited to one table at a time. To solve this the below solution work fantastically.
Option 2: dump
If we want to edit a content from all tables in a database, best way is to take a dump of the database and then do a find and replace. Once satisfied, upload the dump back to the database.
Warning: Always take an untouched backup of the database you are going to play with. Else, there is no use of crying after loss.