Database multiple search & replace

Search through your whole MySQL database and replace with preferred value. Very useful when you want replace Value1 with Value2 in every table and column in your MySQL database. It’s also possible to search & replace more than one value in a time.

Here’s the PHP-code.

Feel free to use and modify this code but please inform us.

<?php
// DATABASE MULTIPLE SEARCH & REPLACE
// Modified by Mats Djärf (md at tojoma.com), www.tojoma.com
//
// Find and replace facility for complete MySQL database
//
// Written by Mark Jackson @ MJDIGITAL
// Can be used by anyone - but give me a nod if you do!
// http://www.mjdigital.co.uk/blog

// SEARCH FOR
$search[]        = 'å';
$search[]        = 'ä';
$search[]        = 'ö';
$search[]        = 'Å';
$search[]        = 'Ä';
$search[]        = 'Ö';
$search[]        = 'é'; 

// REPLACE WITH
$replace[]    = utf8_decode('å');
$replace[]    = utf8_decode('ä');
$replace[]    = utf8_decode('ö');
$replace[]    = utf8_decode('Å');
$replace[]    = utf8_decode('Ä');
$replace[]    = utf8_decode('Ö');
$replace[]    = utf8_decode('é'); 

// DB Details
$hostname = "__DB_HOST__";
$database = "__DB_DATABASE__";
$username = "__DB_USER__";
$password = "__DB_PASSWORD__";

// Query Type: 'search' or 'replace'
$queryType = 'replace';

// show errors (.ini file dependant) - true/false
$showErrors = true;

//////////////////////////////////////////////////////
//
//        DO NOT EDIT BELOW
//
//////////////////////////////////////////////////////

if($showErrors) {
    error_reporting(E_ALL);
    ini_set('error_reporting', E_ALL);
    ini_set('display_errors',1);
}

// Create connectio to DB
$MJCONN = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database,$MJCONN);

// Get list of tables
$table_sql = 'SHOW TABLES';
$table_q = mysql_query($table_sql,$MJCONN) or die("Cannot Query DB: ".mysql_error());
$tables_r = mysql_fetch_assoc($table_q);
$tables = array();

do{
    $tables[] = $tables_r['Tables_in_'.$database];
}while($tables_r = mysql_fetch_assoc($table_q));

// create array to hold required SQL
$use_sql = array();

$rowHeading = ($queryType=='replace') ?
        'Replacing specified values in database \''.$database."'\n\nSTATUS\tROWS\tTABLE / FIELD\t\t\t\"SEARCH\"\t\"REPLACE\"\t\t(+ERROR)\n"
      : 'Searching in database \''.$database."'\n\nSTATUS\tROWS\tTABLE / FIELD\t\t\t\"SEARCH\"\t\"REPLACE\"\t\t(+ERROR)\n";
$rowHeading .= "------------------------------------------------------------------------------------------------------------------\n";
$output = $rowHeading;

$summary = '';

for($i = 0; $i < sizeof($search); ++$i)
{
    // LOOP THROUGH EACH TABLE
    foreach($tables as $table) {
        // GET A LIST OF FIELDS
        $field_sql = 'SHOW FIELDS FROM '.$table;
        $field_q = mysql_query($field_sql,$MJCONN);
        $field_r = mysql_fetch_assoc($field_q);

        // compile + run SQL
        do {
            $field = $field_r['Field'];
            $type = $field_r['Type'];

            switch(true) {
                // set which column types can be replaced/searched
                case stristr(strtolower($type),'char'): $typeOK = true; break;
                case stristr(strtolower($type),'text'): $typeOK = true; break;
                case stristr(strtolower($type),'blob'): $typeOK = true; break;
                case stristr(strtolower($field_r['Key']),'pri'): $typeOK = false; break; // do not replace on primary keys
                default: $typeOK = false; break;
            }

            if($typeOK) { // Field type is OK ro replacement
                // create unique handle for update_sql array
                $handle = $table.'_'.$field;
                if($queryType=='replace') {
                    $sql[$handle]['sql'] = 'UPDATE '.$table.' SET '.$field.' = REPLACE('.$field.',\''.$search[$i].'\',\''.$replace[$i].'\')';
                } else {
                    $sql[$handle]['sql'] = 'SELECT * FROM '.$table.' WHERE '.$field.' REGEXP BINARY(\''.$search[$i].'\')';
                }

                // execute SQL
                $error = false;
                $query = @mysql_query($sql[$handle]['sql'],$MJCONN) or $error = mysql_error();
                $row_count = @mysql_affected_rows() or $row_count = 0;

                // store the output (just in case)
                $sql[$handle]['result'] = $query;
                $sql[$handle]['affected'] = $row_count;
                $sql[$handle]['error'] = $error;

                // Write out Results into $output
                $output .= ($query) ? "OK\t" : "--\t";
                $output .= ($row_count>0) ? $row_count."\t" : $row_count."\t";
                $fieldName = $table.' / '.$field;
                $output .= $fieldName;
                $erTab = str_repeat(' ', (60-strlen($fieldName)) );
                $output .= "\t\t".$search[$i]."\t\t";
                $output .= $replace[$i]."\t";
                $output .= ($error) ? $erTab.'(ERROR: '.$error.')' : '';

                $output .= "\n";
            }
        }while($field_r = mysql_fetch_assoc($field_q));
    }
}

// write the output out to the page
echo '<pre>';
echo $output."\n";
echo '</pre>';

?>