PHP Tutorial: Mass search and replace database field contents

Leave a comment Standard

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']);
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s