Ever needed to migrate a data­base to a new server or web­site (espe­cially with Word­Press and other PHP applic­a­tions) and been stuck because when you do a search and replace some of the data seems to get corrupted?

Seri­al­ized PHP Arrays Cause Problems

In PHP one of the easi­est ways of stor­ing an array in a data­base is to use the serialize func­tion.  Works a treat, but the down­side is that you’re not stor­ing data with a cross plat­form method.  In many product devel­op­ment envir­on­ments this would get you a stern talk­ing to, but in the world of web devel­op­ment where dead­lines are tight and betas are the norm, this seems to be over­looked somewhat.

So what we have are tables full of data that can’t be eas­ily 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 thou­sands of records like the one above, and the word ‘mul­tiple’ 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 char­ac­ters 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 dif­fer­ence?  S18 spells out the length of the fol­low­ing 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 straight­for­ward PHP Seri­al­iz­a­tion fixer code, which got me out of many a pickle — do the search and replace without wor­ry­ing, and then run the script.  Fixed about 90% of problems.

Mul­ti­di­men­sional Array Problem

Sadly those 10% of prob­lems left were a real pain.  I needed some­thing more robust.  Some­thing more power­ful.  And finally today it was a Bank Hol­i­day in the UK — that means no phone calls… I could have a quiet day of cod­ing and con­cen­trate on the best solu­tion to this problem.

What I’ve done is to write a data­base search and replace util­ity in PHP that scans through an entire data­base (so use with care!) which is designed for developers to use on data­base migra­tions.  It’s def­in­itely not what you’d call an end-user tool, though I may san­it­ize it at some point and turn it into an easy to use Word­Press plu­gin.  Thing is — this is dan­ger­ous code — some­times I think it’s bet­ter to make it delib­er­ately a bit tricky, don’t you?

It’s not that bad though — if you can manu­ally install Word­Press, you can eas­ily con­fig­ure the data­base con­nec­tion settings.

What the code does is to look at the data­base, ana­lyse the tables, columns and keys, and then starts read­ing through it.  It will attempt to unseri­al­ize any data it finds, and if it suc­ceeds it will modify that data then reseri­al­ize it and pop it back in the data­base where it found it.  If it finds unseri­al­ized data it will still carry out the search and replace.

Use in WordPress

In most Word­Press migra­tions you tend to have the primary prob­lem of chan­ging the domain name entries in con­tent, set­tings and wid­gets — you simply need to put in the $search_for string the old domain address (includ­ing the http if it’s there) as seen on the data­base, and the new one into $replace_with.  Then put this script onto your server, and run it by vis­it­ing it in your browser or input­ting the appro­pri­ate com­mand line — depend­ing on your server configuration.

Other things you may want to check are for plu­gins or themes that have made the mis­take of stor­ing the full server path to the install­a­tion — cForm­sII does this, for example.  You will need to find out your old and new server paths and use those, in full, for another iter­a­tion of this script.

After less than a second of run­ning, you should have a freshly edited data­base.  It may take a little longer on slow or share host­ing, or if you have a very large data­base, but on my laptop I can man­age around 60,000 items of data per second.

I’ve just used the script to migrate, in its entirety, with con­tent, set­tings, 87 wid­gets (yes, really!) and hun­dreds of images to my loc­al­host server.  It took moments, and the site is per­fectly preserved.

down­load file

Search and Replace Data­base download

BIG WARNING: I take no respons­ib­il­ity for what this code does to your data. Use it at your own risk. Test it. Be care­ful. 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.

Book­mark It