Mass search and replace in sql database

[jeremy860]

Hi,

I need to know how to mass search and replace some html code across a whole database?


this is what I need to do

I need to change this:
<a href="http://www.myspacegeek.net/" target="_blank"><img src="http://www.myspacegeek.net/layouts/support.gif" alt="MySpace Layouts" style="position:absolute; left:0px; top: 0px;" border="0"></a> <br>
Get more <a href="http://www.myspacegeek.net/">MySpace Layouts</a> at <a href="http://www.myspacegeek.net/">MySpaceGeek.net</a><br>

to this:
<a href="http://www.layoutsgallery.com/" target="_blank"><img src="http://www.layoutsgallery.com/support/layoutsgallerysupport.gif" alt="MySpace Layouts Gallery" style="position:absolute; left:0px; top: 0px;" border="0"></a> <br>
Visit <a href="http://www.layoutsgallery.com/">MySpace Layouts Gallery</a> at <a href="http://www.layoutsgallery.com/">LayoutsGallery.com</a><br>

Reason I want to do this is because I want to promote one of my other sites on my higher traffic site for a good week to build up some backlinks.

[geg2]

Found this through Google.

http://www.mydigitallife.info/2007/04/23/how-to-find-and-replace-text-in-mysql-database-using-sql/

Make sure you backup your MySQL before you attempt this. I would go into phpMyAdmin and export the whole database to some .sql file.

[wce]

I'm not sure if it will work, but try select replace(field,"paste your original code here","your new code here") from table... you'll probably have to escape the " 's in your html code... if that returns the correct results then you can try to update table set field=replace(field,"paste your original code here","your new code here") but be careful, don't wanna screw it all up... I probably would replace sections of it at a time, not the whole thing at once...

[niktesla]

This will perform the function on one record:

Remember:
Replace table_name with actual table name
Replace field_name with actual field_name
Replace id with actual index field name
Replace record_id with actual index field value

UPDATE table_name SET field_name=replace(field_name,"<a href=\"http://www.myspacegeek.net/\" target=\"_blank\"><img src=\"http://www.myspacegeek.net/layouts/support.gif\" alt=\"MySpace Layouts\" style=\"position:absolute; left:0px; top: 0px;\" border=\"0\"></a> <br> Get more <a href=\"http://www.myspacegeek.net/\">MySpace Layouts</a> at <a href=\"http://www.myspacegeek.net/\">MySpaceGeek.net</a><br>","<a href=\"http://www.layoutsgallery.com/\" target=\"_blank\"><img src=\"http://www.layoutsgallery.com/support/layoutsgallerysupport.gif\" alt=\"MySpace Layouts Gallery\" style=\"position:absolute; left:0px; top: 0px;\" border=\"0\"></a> <br> Visit <a href=\"http://www.layoutsgallery.com/\">MySpace Layouts Gallery</a> at <a href=\"http://www.layoutsgallery.com/\">LayoutsGallery.com</a><br>") WHERE id="record_id"

If that works this function will perform the same thing on all records in the table:

Same caveats as above

UPDATE table_name SET field_name=replace(field_name,"<a href=\"http://www.myspacegeek.net/\" target=\"_blank\"><img src=\"http://www.myspacegeek.net/layouts/support.gif\" alt=\"MySpace Layouts\" style=\"position:absolute; left:0px; top: 0px;\" border=\"0\"></a> <br> Get more <a href=\"http://www.myspacegeek.net/\">MySpace Layouts</a> at <a href=\"http://www.myspacegeek.net/\">MySpaceGeek.net</a><br>","<a href=\"http://www.layoutsgallery.com/\" target=\"_blank\"><img src=\"http://www.layoutsgallery.com/support/layoutsgallerysupport.gif\" alt=\"MySpace Layouts Gallery\" style=\"position:absolute; left:0px; top: 0px;\" border=\"0\"></a> <br> Visit <a href=\"http://www.layoutsgallery.com/\">MySpace Layouts Gallery</a> at <a href=\"http://www.layoutsgallery.com/\">LayoutsGallery.com</a><br>") WHERE id>0