Find and Replace text in the entire table using a MySQL query
For a single table
update
UPDATE `table_name` SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')
From multiple tables
-
If you want to edit from all tables, best way is to take the dump
and then find/replace
and upload it back.
The easiest way I have found is to dump the database to a text file, run a sed command to do the replace, and reload the database back into MySQL.
All commands below are bash on Linux.
Dump database to text file
mysqldump -u user -p databasename > ./db.sql
Run sed command to find/replace target string
sed -i 's/oldString/newString/g' ./db.sql
Reload the database into MySQL
mysql -u user -p databasename < ./db.sql
Easy peasy.
Put this in a php file and run it and it should do what you want it to do.
// Connect to your MySQL database.$hostname = "localhost";$username = "db_username";$password = "db_password";$database = "db_name";mysql_connect($hostname, $username, $password);// The find and replace strings.$find = "find_this_text";$replace = "replace_with_this_text";$loop = mysql_query(" SELECT concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s FROM information_schema.columns WHERE table_schema = '{$database}'")or die ('Cant loop through dbfields: ' . mysql_error());while ($query = mysql_fetch_assoc($loop)){ mysql_query($query['s']);}