PHP Tutorial: Mass search and replace database field contents
So recently I needed to remove a bunch of URLs from my member’s content on my games because of spyware/malware notifications Chrome users were getting. It took me a bit to figure out how to do it so here’s a nice and nifty little script that will hopefully save you the same trouble.
<?php
/******
* Purpose: Mass search/replace all database fields
* Author: design1online.com, LLC
* License: GNU
******/
//connect to your database
$host = "localhost";
$username = "your_username_goes_here";
$pass = "your_pass_goes_here";
$database = "your_database_name_goes_here";
//the values you want to find/replace
$find = "what_you_want_to_find";
$replace = "what_you_want_to_replace_it_with";
//spit out the SQL without running it
$test_mode = true; //change this to false and the queries will be run automatically
//if you want to limit the replace to specific tables uncomment this line and enter them below
//$tables_list = array("table_name_1", "table_name_2", "table_name_3");
if ($tables_list)
$limit_tables = " AND table_name IN ('" . explode("', '", $tables_list) . "')";
//loop through all the tables and all the columns and change $find to $replace
$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}'
{$limit_tables}")
or die ('cannot loop through database fields: ' . mysql_error());
while ($query = mysql_fetch_assoc($loop))
{
if ($test_mode)
echo "{$query['s']}<br/>";
else
mysql_query($query['s']);
}
