[PHP] massive find/replace on MySQL db
Hello, I've inherited a MySQL db (4MB on disk) that's riddled with inconsistent storage of character entities from being pasted from Word. At very least I hope to convert all quotes #145; - #148; to HTML's #34; and #39;. I imagine I'll run a big query, save the id's needing edit in a file than have a script hit all those, pulling text/varchar fields, using strtr() to make changes and updating. I suppose I could have the script just do the 1st 10 in the file, remove those from the file, then I'd recall the script. I imagine this might take 10/20 minutes. Am I missing some internal MySQL feature that would do this? Hmm, what about running strtr() on a mysqldump-ed file? Any advice/links appreciated. Please CC me. Thanks a lot! Steve -- [EMAIL PROTECTED] ** http://mrclay.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] massive find/replace on MySQL db
SC I've inherited a MySQL db (4MB on disk) that's riddled with SC inconsistent storage of character entities from being pasted from SC Word. At very least I hope to convert all quotes #145; - #148; SC to HTML's #34; and #39;. For each entity you wanted to replace, you could: update tablename set fieldwithcrap = replace(fieldwithcrap, 'oldstring', 'newstring'); Keep an eye on all your escaping and what not. And do a dump before you do all your replacing, just in case something goes awry. If that doesn't work well, then I'd probably dump the data, run it through a string replacing script, and load it all back in. That wouldn't take too long either. - Julie -- Julie Meloni -- [EMAIL PROTECTED] -- www.thickbook.com Find Sams Teach Yourself MySQL in 24 Hours at http://www.amazon.com/exec/obidos/ASIN/0672323494/thickbookcom-20 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP] massive find/replace on MySQL db
Saturday, June 15, 2002, 11:11:49 AM, Julie wrote: JM update tablename set fieldwithcrap = replace(fieldwithcrap, JM 'oldstring', 'newstring'); Julie, lovely solution and shockingly fast. Thanks. Steve -- [EMAIL PROTECTED] ** http://mrclay.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] massive find/replace on MySQL db
Yeah, do it all with regular queries. You shouldn't have to do any replacing in PHP. You can even use the MySQL regular expressions in your query for some more complex matching and replacing... It's all in the manual... ---John Holmes... -Original Message- From: Julie Meloni [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 15, 2002 11:12 AM To: Steve Clay Cc: PHP-GENERAL Subject: Re: [PHP] massive find/replace on MySQL db SC I've inherited a MySQL db (4MB on disk) that's riddled with SC inconsistent storage of character entities from being pasted from SC Word. At very least I hope to convert all quotes #145; - #148; SC to HTML's #34; and #39;. For each entity you wanted to replace, you could: update tablename set fieldwithcrap = replace(fieldwithcrap, 'oldstring', 'newstring'); Keep an eye on all your escaping and what not. And do a dump before you do all your replacing, just in case something goes awry. If that doesn't work well, then I'd probably dump the data, run it through a string replacing script, and load it all back in. That wouldn't take too long either. - Julie -- Julie Meloni -- [EMAIL PROTECTED] -- www.thickbook.com Find Sams Teach Yourself MySQL in 24 Hours at http://www.amazon.com/exec/obidos/ASIN/0672323494/thickbookcom-20 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
