The Webmaster’s Weekly #12 is out & the Quick Tip tells you about a simple way of doing a search-replace in a MySQL table.
Well, it might be something new for Chrispian but I’ve been doing this for ages, infact I don’t know of any other search-replace technique for MySQL when you don’t need to replace the data of the whole column of a record, but just some text from between it, like in “hello world, today is wednesday” you want to replace “today is wednesday” with “yesterday was thursday”. This is ofcourse quite a simple example which can be done with a simple SQL. The tip however says that its quite simple to just download the data as SQL using phpMyAdmin, open it in a text editor, search-replace and then upload back after saving.
But being someone who’s done this way too many times & still does it, I wouldn’t recommend doing it to anyone unless the data you’ll be downloading is not too much and if there’s no easy way of doing it via an SQL UPDATE. The reason is why I prefer SQL in this case is because the data I work with, in which I’ve to perform search-replace, is always more than 10MB usually. So ofcourse downloading is a problem & then uploading is also a problem as I’ll have to split it into files each weighing less than 2MB(phpMyAdmin won’t upload files bigger than that, its the default max size allowed by PHP). 🙁 Quite a headache really!! :dizzy: On the otherhand, doing an SQL won’t take much time, and even writing the SQL would take up a bit of less time than downloading & uploading, and the additional benefit is that you get more fluent in SQL, so the next time it’ll be easier to do this. 😉
This is why, I wouldn’t recommend doing this download-search-replace-upload technique to anyone if using an SQL UPDATE is an option!! 😀
You can get the number of rows affected by your replace using the PHP function mysql_affected_rows() but I don’t know of any ways of knowing number of replaces done as a row might have more than one replace done on it! 🙂
I messed up the special characters in my wordpress blog when I finally defined the database charset as utf-8.
I’m trying to search-and-replace the unexpected character strings
UPDATE wp_posts SET post_content =REPLACE post_content ,’’’,’’’
My error message alters the string of unwanted characters”: #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘post_content ,’’’,’’” at line 1
What am I missing?