phpMyAdmin search and replace
Here is a great tip that can save you hours of work. Sometimes you have messed up characters in your database - wrong character encodings - or you want to strip out some text in a certain table's field. In phpMyAdmin, open up the SQL dialog and use the following code (change tablename and tablefield to your name and field values):
UPDATE tablename SET tablefield = replace(tablefield, "findstring", "replacestring");
You can also tack on a WHERE statement, if you like.
I found this tip on Brian Zimmer's website.
Tested on MySQL5, PHP5 and phpMyAdmin 2.11.4.
Save time!
Just adding to your code:
UPDATE node SET body1 = replace(body1,'cat','dog'), body2 = replace(body2,'cat','dog')
Thanks again!
Other people have said it and so am I.. THANKS
For a newb like myself, guys like you really make going to sleep possible :)
b
importing data into mysql
hello I am trying to import a large .csv file into mysql. I have created a table and all the filed accordingly. But for some reason when I do import the data it only import two line, three line and sometimes four line(rows) and give me the rror message invalid filed count. I would think if the filed count was invalid it would say that on first row. Can anyone suggest what could be the possible issue( like variable mismatch, invalid charecter) basically anything that anyone can suggest I should look into
Thanks
Find & Replace
Hello,
Thanks for the post. I updated an install from phpBB2 to phpBB3. Every instance of links and reference to phpBB2 needs to be replaced with phpBB3. How would I run the command? In advance I thank you for your assistance.
B -
You need to repeat the
You need to repeat the command for each table and each table column that contains the text you need to change. There's probably a way to do this programmatically, but its simpler just to repeat it. Change "node" and "body" to the MySQL table and table column names you need.
UPDATE node SET body = replace(body, "phpBB2", "phpBB3");
This works great
THanks for the tip.
Thank you for sharing!
I recently migrated my website to Apache from H-Sphere and 900 images were broken due to new case sensitivity. I ran your code to change "thumbs/jimbo" to "thumbs/Jimbo" and PRESTO my problem was solved. Thank you very much!
Exactly what I needed...
Thank you so much.
I changed the default skin for my forum, and I needed to update all the users to use the new skin, but wasn't sure how to do it. This took me seconds to take care of it. Thanks!
You saved me hours!
I knew I'd seen an example on MySQL search and replace somewhere, but I couldn't remember where. A quick search on G and I found this page at the top with the answer I needed :)
Thanks!
Outstanding! Thank you!!!
Outstanding! Thank you!!!
Just what I was looking for .. thanks
15 min ago, I was bummed and sleepy, facing a task I didn't know how to do right off. One web search later I had the answer thanks to you and Zimmer. Thanks for posting a simple thing like this. Helps us newbies out a lot.
/sydney