I had a problem recently where I had spent ages building a new CMS driven site which had loads of content – all interlinking with keyword rich anchor text etc, a WordPress blog, an email marketing system built in and a few other gismos to boot. With everything just about finished, the site SEO’d (well on-page SEO anyway) and uploaded to the final server for final tests I hear the imortal phrase “We’ve decided to change the site name and domain”.
I suppose you can guess my thoughts – 1000’s of database records across over 60+ different tables – any of them containing a reference to the name or the domain name. Oh please oh please let there be an easy way to change all these!!
In come comes the Google searches:
“replace text in database”
“replace text in whole database”
“find and replace text mysql”
etc etc
The results were coming in thick and fast but most of them just seemed to deal with individual columns or tables or you have to export all your data to text files and use find and replace functions in things like notepad – oh and of course you get the REALLY unhelpful people that just tell you how you should have done it in the first place. None of them really did what I wanted (although the export to file would have but I wanted another way!).
So – as usual I was left to figure it out myself. I thought there must be some kind person that has written a PHP script which you can put in search text and a replacement, point it to a database and it just loops through all the available tables, columns and records – then outputs a nice report to say where it had made the updates.
Could I find it? ….. nope.
So I wrote it myself. If you want it then here it is: http://mj7.co.uk/am03
Please let me know if you find it useful or if you can make it better I’d love to have it streamlined – tweet me @mjdigital or comment below.
UPDATE: Whilst this script still works and can be of use I would recommend using the script created by Interconnect/IT as it has a great user interface and handles serialized data. You can see/download the Interconnect/IT script here: https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
Eric Amundson
Hi Mark,
This is exactly what I was looking for!
I’ve been a WordPress dev for some time and there are occasions where I have to move a database that’s been customer-maintained and a simple search and replace in gedit or another text editor just won’t do the trick.
Your script worked on the first go. 🙂
When I have some time, I’ll dig into it and see if I have any questions or can make suggestions.
Cheers and thanks again!
Jay
Mark : YOU are a legend 🙂
Thank you so much. This is great script and I it is my lucky day that I finally found it after hours of searching. It would be great if can have wildcard option too. I just tested it on my local sever and it is a charm.
Thank you sooooo much.
mj7
Thanks for the comments – wildcards would be great but at the moment MySQL does not support REGEXPs within the REPLACE function which would be the nicest way to do it.
I have tweaked the file a little on line 49 to allow for uppercase letters in the DB name – it is now
$tables[] = $tables_r['Tables_in_'.strtolower($database)];MJ7
mj7
For anyone interested w8.se have updated this code allowing you to replace more than one value at a time – see: http://www.w8.se/2009/09/30/database-multiple-search-replace/
Keep your mods coming!
If you let me know then I will link to it here (not that w8.se did!)
Dustin Dempsey
Doesn’t seem to be working for me. I get a bunch of errors:
example error #1 (repeated a number of times):
Notice: Undefined index: Tables_in_database_db in path/to/file/mysql-search-replace.php on line 49
example error #2 (repeated a number of times):
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in path/to/file/mysql-search-replace.php on line 68
Any idea how to fix this?
Would love to be able to use this script.
Thanks.
Dustin
mj7
Not wanting to sound obvious but have you made sure you put in your database name and connection details correctly?
If yes then can you let me know the name of your database – I am aware that the Tables_in…database… command is a little tetchy when it comes to case sensitivity and special characters – if you want to PM me then please do (see contact page) – or you can write here.
Dustin Dempsey
I used the code found at w8.se and it works for me.
Not sure what the issue was on the original script.
Thanks for the follow up and the email message.
It’s much appreciated.
MySQL Search & Replace Tool | sewmyheadon.com
[…] searched around and found this PHP script by Mark Jackson which makes it much faster and easier to search and replace data across an entire MySQL […]
Usacttatduh
Which WP theme are you using?
mj7
I am using the Arras Theme – see: http://wordpress.org/extend/themes/arras-theme
I have made a few tweaks to alter the branding but nothing major.
Stainless
Hi MJ7, Quick THANKS!!! I went through the same avenues of Google, Bing, etc and everyone had a single field replacement script but nothing for the whole data base… very frustrating. But you saved the day as my Joomla site backend was used on a new site, so a full search replace had to occur and it worked like a charm. Here are some of the keywords that I searched for; phpmyadmin search and replace, mysql replace all, phpmyadmin find replace complete, phpmyadmin search/replace database, mysql search replace whole database I hope that these get you higher in the search engines. Again thanks! Stainless
Bluesplayer
Very sweet. Using it on my software store now and works a treat.
Thanks
Toby Adams
Great stuff, thanks for this!
Stephen R
Thank you for this! Just great.
Yeah… I too came across the people with the “Instead I'll tell you how you should have set up your database.”
Reminds me of the scene in Funny Farm:
“Hey Mac, how do you get to Redbud?”
“If I wanted to go to Redbud, I wouldn't start from here.”
Stephen R
Question: Does this handle serialised array data or will that cause problems?
mj7
Hey, thanks for your comments. I'm afraid at the moment this script does not handle serialized arrays. I am currently working on a new version which does and also allows regular expressions for your search and replacements – I'll keep you posted once I've finished it.
Devin Walker
Nice code, it could be so much more… maybe a WordPress plugin?
Nikko
Awesome! Just what I needed
Kosch
Thank you very much. Just like you I've been moving alot of databases from dev to production and production back to dev to refresh the content and trying to sort out all the find and replaces for the old URL's was a pain. This saves me so much time thank you!
Rose
Great Convenient Tool!!!! This is what i needed too and it worked great. Just felt like leaving a note saying Thanks!
mj7
You're most welcome. Glad it worked for you.
Keith Fawcett
Fantastic tool! Thanks a lot.
dandy
Thanks alot for this wonderful script. I've looking for it.
walmley website design
I recently wanted to replace a string within MySQL on the fly, but the field could contain 2 items. So I wrapped a REPLACE() within a REPLACE(), such as:
REPLACE(REPLACE(field_name, “what we are looking for”, “replace first instance”), “something else we are looking for”, “replace second instance”)
This is the syntax I used to detect a boolean value:
REPLACE(REPLACE(field, 1, “Yes”), 0, “No”)
Hope this helps!
Netflow Developments » Blog Archive » How to do a global search and replace within a DB across all Tables
[…] As part of my take over the world campaign I’ve run into the need to automatically do a global Search and replace within a Mysql Database covering all tables. For this you need a little php magic and luckily enough I found just that magic right here. […]
Guest
I used the script with GUI – many thanks for your part in this. Pure genius to a person like me, who has only a centimeter worth of knowledge (kilometers wide though), but I can still drown in code more than a line deep.
Sacameunojo
Voodoo!!!
You are my hero!
Ligi
great, tnx a million! my client changed all their flower names in a CMS. I was hoping not having to do it manually….
Ligi
Just used it on the live site, one thing to keep in mind : if the replace involves image names in the database, then you will have to adjust the filenames of the images on the server….
Guest
Thank you! You saved me load of time.
PerfectAnswer
Thank you for sharing this great script!
Runs like a charme, very well inline documentation, good looking output.
Very Happy with your script.
Regards from Hamburg/Germany
Bernd
Urselinho
Thanks alot…will save me a lot of time!!!!
alberto
Great script! thanks a lot.
I have just one suggestion.I used this script to do a search and replace in my magento db, and some column names have reserved words, so I needed backticks in order to make it work.Just changed
$sql[$handle]['sql'] = 'UPDATE '.$table.' SET
'.$field.'= REPLACE('.$field.',''.$search.'',''.$replace.'')';and
$sql[$handle]['sql'] = 'SELECT * FROM '.$table.' WHERE
'.$field.'REGEXP(''.$search.'')';on lines 88 and 90
Mysql için bul ve değiştir aracı | PROGRAMLAMA | Ceviz.Net | PROGRAMLAMA | Ceviz.Net
[…] için Mark Jackson tarafından yazılmış php betiğini […]
Neil
Hi MJdigital,
Just wanted say a huge THANK-YOU for this script. It’s a lifesaver!
Neil
Themajstor
Thx, man. U save me tons of time.
Memindahkan blog anda ke nama domain lain | Haris Web Media
[…] Di internet saya pernah menemukan sebuah tutorial beserta script PHP yang memungkinkan hal ini dapat dilakukan, berikut script php yang digunakan […]
Mohit Arora
Nice work dude.
Prashant
Thanks a ton worked like a charm !!!
Anand
Thanks for the good script.
y0d4
Hello,
great script man! 🙂
but i have little problem, when i fill up all details and click “go” i non-stop getting this info:
Cannot Query DB: No database selected
any suggestion?
thank you.
Ben
Man!!!!! You are the best! Thank you so much