A nice shortcut I came across to update content in a mySQL-table without writing a script to do it – which was the first thing that came to my mind…
Case: I had many HTML-snippets in a CMS table called ‘freepages’ containing hard coded domain-names in URLs to images etc. Now guess what happened as we switched the overall site domain from ‘www.mydomain.de’ to ‘info.mydomain.de’? Much of the content seemed to have gone.
This snippet was the elegant way of solving this directly in mySQL without headaches:
UPDATE freepages SET body=REPLACE(body, 'www.mydomain.de', 'info.mydomain.de');
or in case you forgot to switch off magic quotes and have all sorts of noisy data like this “Mary\\\\\’s house” with a random number of backslashes. Use this multiple times:
UPDATE applicant_publication SET title=REPLACE(title, "\\'", "'");
UPDATE applicant_publication SET authors=REPLACE(authors, "\\'", "'");
UPDATE applicant SET research_skills=REPLACE(research_skills, '\\"', '"');
Of course you need one line for each field with that problem.