- Word of the day: Ouch.
- I’m done with the cancer, but the cancer isn’t done with me
- Goodbye, my dopey dog.
- So long, Alex King
Polluting the internet since 2004
April 3, 2009 | Web Design & Development
MySQL always amazes me with the breadth of functionality that it has. Too often we just use it to push and pull information from the database. So when I hit an occasion where I can so in a single SQL statement that would require a time consuming PHP I get excited.
Here, I had to so some simple string replacement on some database values. I had some PDF files in a WordPress MU database that didn’t get updated by one means or another when upgrading from WordPress MU 2.6 to 2.7. WordPress 2.6 stores attachment location data will full file paths, so that when the database is moved to another server the file paths are wrong and need updating. This was fixed in 2.7 by storing just the path relative to the upload root. What that also means, though, is that when WordPress MU encounters that full file path that permalink generation borks.
So, with the scene set, here’s the code. Its pretty simple. We simply walk the database table using the
REPLACE() function (found on the MySQL.com string functions page) where we encounter our string. Example:
Yeah, its that simple. Not much more to say except that I know all the DB admins that run across this are gonna want to welcome me to 1998 😉
Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.
Comments are closed