I wrote this because I often need to move data-driven sites from development to production and addresses change. (Wordpress sites, Wordpress MU, Drupal, and so on.) This means I have to go through and change every occurrence of the url as it appears in the database. So when the first few solutions for changing the site url aren’t doing the trick, I have to resort to the following MySQL snippet for updating every single table.
To find a string in a certain field and replace it with another string:
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
Well, I got tired of doing that and wrote a handy snippet to find and replace across an entire database.
There are no checks or safeguards! This is a quick and dirty script to be used at your own risk!
But if it’s useful, please feel free to say so in the comments.
UPDATE
Added two checkboxes:
- Set the script to never timeout (use with caution!)
- Do a case-insensitive find and replace. (use with caution!)
Download the updated .rar here
Thanks for this. I installed it and tried it, about 3/4 of the way through it threw out some error (I cleared the screen so I don’t remember). Anyway it disabled my site and I had to restore it from a backup. I will save this though and hopefully it’ll work in the future.
Hi there, sorry about the delay in answering. If your database is large, the script probably timed out and that’s *probably* the error you saw. Try adding set_time_limit(0); to the top of the script. Thanks!
[...] you need a database-wide search-and-replace, you could try this script (I haven’t tested/used it [...]
This is excellent. I only have one problem, when i want to replace a word it is case sensitive. For example when i want to replace Dog with Cat, I type in “Dog” then it does replace with “Cat” but i want it to replace all occurrences of “Dog”: dog, Dog, DOG. PLEASE HELP.
Providing you’re using INNODB tables, it’s handy to roll the whole operation into a transaction so that you don’t have to do a restore just because the transformation didn’t finish all the way through.
@Lyle: Try converting the first parameter of the replace function to lower case:
replace(LOWER([field_name]),’dog’,'cat’)
Now if you wanted to replace ‘Dog’ with ‘Cat’ and ‘dog’ with ‘cat’, you’ll just have to write as many variations as you need…
MySQL has regexp support, but I don’t see a regexp-supporting variation of replace(), which would have been handy.
Won’t hurt to mention that if you use it on your web server, it’s a good thing to remove it when you’re done….
Oh, and… This script, slightly modified, would also be handy to run from the command line:
$ php db-search-and-replace.php ‘dbname’ ‘host’ ‘user’ ‘pass’ ‘find-string’ ‘replace-string’
Simplest way to get the values of the passed parameters is in the $argv array (automatically populated when running from command line), a more elegant way is to use a PEAR class (I use Console_Getargs but there are others), or to prompt the user after starting the script.
Hi Lyle, Hi Mattais,
Those are great suggestions. I added an option to cause the script to run indefinitely. You should use this with great caution, and will only work if your web host supports set_time_limit(0)
The other is to do a search and replace that is case-insensitive.
I consolidated the scripts to a single file, for my own benefit, but Mattias is right. Very little tweaking, one can run the script from the command line. If anyone is interested in this version, I’d be happy to scribble that out.
Thanks!
irms
File not found. Please look into this
Hi there Ayden,
Should be fixed now.
Thanks!
irms
BEAUTIFULLY simple script. Thank you SO very much for putting that out there. Just saved me a massive amount of time on a Wordpress move.
Thanks Steve! Glad you found it useful!