Please note that a newer version of this code is now available from my Interconnect’s site over at https://interconnectit.com/search-and-replace-for-wordpress-databases/
Ever needed to migrate a database to a new server or website (especially with WordPress and other PHP applications) and been stuck because when you do a search and replace some of the data seems to get corrupted?
Serialized PHP Arrays Cause Problems
In PHP one of the easiest ways of storing an array in a database is to use the serialize function. Works a treat, but the downside is that you’re not storing data with a cross platform method. In many product development environments this would get you a stern talking to, but in the world of web development where deadlines are tight and betas are the norm, this seems to be overlooked somewhat.
So what we have are tables full of data that can’t be easily edited by hand. For example:
;a:3:{s:5:"title";s:17:"This Week\'s Poll";s:18:"poll_multiplepolls";s:0:"";s:14:"multiple_polls";N;}
Say you had thousands of records like the one above, and the word ‘multiple’ needs to be changed to ‘happy’. Two bits would change – poll_multiplepolls would now read poll_happypolls and multiple_polls would read happy_polls. In both cases you would have three characters fewer to deal with.
Fine, you may think, but you can only do the change by hand because where it says s:18:"poll_multiplepolls" it now has to say s:15:"poll_happypolls" – see the difference? S18 spells out the length of the following string, and it has to be changed to s:15
I’ll say right now, that that was a pain. For simple arrays I wrote the straightforward PHP Serialization fixer code, which got me out of many a pickle – do the search and replace without worrying, and then run the script. Fixed about 90% of problems.
Multidimensional Array Problem
Sadly those 10% of problems left were a real pain. I needed something more robust. Something more powerful. And finally today it was a Bank Holiday in the UK – that means no phone calls… I could have a quiet day of coding and concentrate on the best solution to this problem.
What I’ve done is to write a database search and replace utility in PHP that scans through an entire database (so use with care!) which is designed for developers to use on database migrations. It’s definitely not what you’d call an end-user tool, though I may sanitize it at some point and turn it into an easy to use WordPress plugin. Thing is – this is dangerous code – sometimes I think it’s better to make it deliberately a bit tricky, don’t you?
It’s not that bad though – if you can manually install WordPress, you can easily configure the database connection settings.
What the code does is to look at the database, analyse the tables, columns and keys, and then starts reading through it. It will attempt to unserialize any data it finds, and if it succeeds it will modify that data then reserialize it and pop it back in the database where it found it. If it finds unserialized data it will still carry out the search and replace.
Use in WordPress
In most WordPress migrations you tend to have the primary problem of changing the domain name entries in content, settings and widgets – you simply need to put in the $search_for string the old domain address (including the http if it’s there) as seen on the database, and the new one into $replace_with. Then put this script onto your server, and run it by visiting it in your browser or inputting the appropriate command line – depending on your server configuration.
Other things you may want to check are for plugins or themes that have made the mistake of storing the full server path to the installation – cFormsII does this, for example. You will need to find out your old and new server paths and use those, in full, for another iteration of this script.
After less than a second of running, you should have a freshly edited database. It may take a little longer on slow or share hosting, or if you have a very large database, but on my laptop I can manage around 60,000 items of data per second.
I’ve just used the script to migrate, in its entirety, with content, settings, 87 widgets (yes, really!) and hundreds of images to my localhost server. It took moments, and the site is perfectly preserved.
Search and Replace Database download
BIG WARNING: I take no responsibility for what this code does to your data. Use it at your own risk. Test it. Be careful. OK? Here in the North we might describe the code as being as “Rough as a badger’s arse.” Never felt a badger’s arse, but I’ll take their word for it.
Hi Dave
Thank you for making this script. It just made migrating a BuddyPress local install to an online server a whole lot easier. Thanks again. Have a great day.
Oliver
thanks for the script dave.
this one saved a lot of of messing about when i had to move a wordpress site to a different url/path to a new location.
but I had to modify it a little to allow for some wierd escape slashes for the quoted strings. but this might have been a once off with some particular wordpress modules we used.
before $unserialized:
$escapedstuff = 0;
if(preg_match(‘/:\”/’,$data_to_fix) && preg_match(‘/\”;/’,$data_to_fix))
{
$data_to_fix = preg_replace(‘/:\”/’,’:”‘,$data_to_fix);
$data_to_fix = preg_replace(‘/\”;/’,'”;’,$data_to_fix);
$escapedstuff = 1;
}
and after $edited_data:
if($escapedstuff == 1)
{
$edited_data = preg_replace(‘/:”/’,’:”‘,$edited_data);
$edited_data = preg_replace(‘/”;/’,'”;’,$edited_data);
}
Hi Dave!
Much thanks for your clever resolutions for both serialization fixer and Search Replace…(I’ve tried yet the previous but, surely I’ll need the latter).
You saved me a lot of works and annoyance.
Really much thanks to share your code with us.
iplnts
Sweet,you just saveD me a ton of headaches. I have a Search and Replace plugin installed but that only goes through posts and whatnot, it doesn’t help with home directory, upload directory, or widgets. Widgets was my biggest pain but not anymore.
THANK YOU!
Hello Dave,
You’re a life-saver. How many more hours would I have spent barking up the urlencode tree!? :)
Many thanks,
Monu
Dear Dave,
I love you.
This script just brought tears of joy to my eye when I thought all hope was lost.
I used the WP Table Reloaded plugin on a WPMU install with 6 subdomains and 194 tables being managed through it and had references to the dev domain in each table. When I search and replaced the SQL file to make the domain switch, it hosed everything and broke all the tables because they used serialized arrays.
…from the bottom of my heart…THANKS!
Heh – glad to hear it helped Adam – it was for the same reason that I wrote the script in the end – was just despairing of the solution. I’m not a coder any more, but sometimes needs must!
Thank you for making this script. It just made migrating a BuddyPress local install to remote complete. Thanks a million!
quit using a proprietary method in PHP for serializing…instead store the data in JSON format
I would agree (or simply create db structures to suit) but I didn’t write WP…
Beggars can’t be choosers. Myself, I love JSON… but more importantly love a well written db schema that does not require such things. But if you aren’t going to write you own content management system, and you have to use WP (or something similar) you have to deal with the issue that arise from their coding.
Personally I love developing for WP, creating a fourm with an nonce, having an html input naming schema that will automatically serialize and store all of my fields for me… it’s pretty nice. then just call a single get_option() to pull all of my plugin’s settings… yeah, it’s nice.
@Dave: You’re amazing. I had the unfortunate (literal) nightmare of dreaming how I was going to code what you already did. Instead I googled for: mysql replace function php serialized multidimensional arrays. And there you were like a godsend.
The fact is, storing absolute paths to stuff in the database is terrible practice and should be avoided at all cost, especially when storing them as serialized objects/arrays.
PHP has a million ways to get paths without having to query the database. These methods should be employed because they’re faster and have less overhead.
Screw wordpress.
this is an absolute godsend. However as long as you create a database backup before hand you should have nothing to worry about, its just a pain in the a** trying to migrate properly.
Your tool really saved the day, more likely a bunch of days in the future too. Very useful stuff, thanks a bunch.
Thanks!!! Your script save my day and night =) I’ll talk about and share it with all my friends, very very useful, I was tryed to develop it but many problems occurs with Multidimensional Arrays. After run your script (332 tables verified) all my problems was fixed
Thank you very much! Working perfectly and so useful!
Worked PERFECTLY!!! WOW… You helped my business dave!!
The link that I’m seeing across the web is now 404, and I’ve found a more simple solution right there in the WordPress Plugin directory: http://wordpress.org/extend/plugins/wp-migrate-db/ will solve your problems This was the only thing I could find out there that worked for me.
It looks like this script has moved to http://interconnectit.com/products/search-and-replace-for-wordpress-databases/
Hi there,
I have some stdClass in my array and the script seems ignore them, any idea how to fix this.
Cheers,
Mary
Brilliant work :) Thanks a lot David,
your script save me a considerable time to update wordpress postmeta table where meta_value is save as serialized array.
Keep up the great job.
thank you for the work I’ve imagined yet never did :) … :) ..
The spectacu.la link is dead, and after a bit of searching it looks like the current/maintained version is at https://github.com/interconnectit/Search-Replace-DB
You’re right. Gosh… I long forgot about Spectacu.la and I have a lot of stuff lying around in need of a tidy up! Always the issue with old content!
wp-cli does this and many other things
It does indeed! We even got a credit for our code that’s used in it :-)
But if you can’t access a command line it’s still a handy script at https://interconnectit.com/search-and-replace-for-wordpress-databases/
It does. The script’s algorithm was used and is credited for in wp-cli. I was there first!
The script is also useful when you can’t access the command line and need to do a migration.