Jan 01, 2009

When developing web sites, sometimes things change from how they were in development to how they need to be in production, especially when it comes to machine names and IP addresses, etc.  If these values are stored in a database, chances are you’ll need some quick SQL magic to help finalize the migration.

For those of you that just want the info, here you go.  This is a quick and easy way to directly replace one string with another for all rows in a column/field.  DISCLAIMER: If you don’t know what you are doing, you can really screw up your database.  Please take care to do a backup and by all means, if you can’t fix what you break, don’t attempt this.

UPDATE MyDB.MyTable
SET MyDB.MyTable
= REPLACE(MyDB.MyTable.MyField,'OldString','NewString')
WHERE MyDB.MyTable.MyField like '%OldString%';


Real World Usage:

I just ran into this issue when implementing our new Milowerx Media website.  My brother and I have been setting up the site on a development server that was hosted as a sub domain of the milowerx.com root.  Doing so allowed us to keep up the old site as www.milowerx.com while we created the new site.  When we were finished with the new site, all I needed to do was change the DNS value for the www entry and tell the new server that it was www and not the development subdomain wpdev.

As I’ve probably mentioned before, I use WordPress to do just about any web related task these days, and the new Milowerx site is no exception.  There are several nice things about using WordPress as a Content Management System.  It is extremely flexible, has a great user community that contributes tons of useful plugins and finally it is extremely stable.

But, this post is about SQL, not WordPress.  I bring up WordPress however because it is a completely dynamic Web Content Publishing platform that is driven entirely by PHP files reading values in a database.  One of the plugins that we used was WordTube, which we chose to help manage the oodles and oodles of media that we have generated over the years.  Its a great plugin, but unfortunately it uses explicit URLs that are stored in the database to generate it playlist values.  When migrating the server from the dev environment to production, all of the references were calling the wrong URL.

Essentially, I needed to find every instance of the development server name wpdev and replace it with www for all of the WordTube tables.  To do so, I ran two MySQL queries that did the trick.

UPDATE MyWPDB.wp_wordtube
SET MyWPDB.wp_wordtube.image
= REPLACE(MyWPDB.wp_wordtube.image,'http://wpdev.','http://www.')
WHERE MyWPDB.wp_wordtube.image like '%http://wpdev.%';


and

UPDATE MyWPDB.wp_wordtube
SET MyWPDB.wp_wordtube.file
= REPLACE(MyWPDB.wp_wordtube.file,'http://wpdev.','http://www.')
WHERE MyWPDB.wp_wordtube.file like '%http://wpdev.%';


The first query updates the thumbnail images; the second updates the actual movie files.  Presto, change-o, it all works. 

Now, for those of you who know DNS and WordPress, you may be thinking “Why didn’t you just mirror wpdev to www so that any call to wpdev would end up pointing in the new, right location?”  Well, good question.  The answer is that WordTube seems to handle local files one way and remote files another.  Leaving the wpdev reference changed the way thumbnails and movie files were handled in the playlist.  So, technically it would have worked, but the outcome wasn’t optimal.  And, two easy SQL statements was much easier than slogging through the code of WordTube.