rxnlabs/php-find-replace-string-cli.php

## php-find-replace-string-cli.php
<?php
ini_set('memory_limit','3200M');
// This script is to solve the problem of doing database search and replace
// when developers have only gone and used the non-relational concept of
// serializing PHP arrays into single database columns.  It will search for all
// matching data on the database and change it, even if it's within a serialized
// PHP array.

// The big problem with serialised arrays is that if you do a normal DB
// style search and replace the lengths get mucked up.  This search deals with
// the problem by unserializing and reserializing the entire contents of the
// database you're working on.  It then carries out a search and replace on the
// data it finds, and dumps it back to the database.  So far it appears to work
// very well.  It was coded for our WordPress work where we often have to move
// large databases across servers, but I designed it to work with any database.
// Biggest worry for you is that you may not want to do a search and replace on
// every damn table - well, if you want, simply add some exclusions in the table
// loop and you'll be fine.  If you don't know how, you possibly shouldn't be
// using this script anyway.

// To use, simply configure the settings below and off you go.  I wouldn't
// expect the script to take more than a few seconds on most machines.

// BIG WARNING!  Take a backup first, and carefully test the results of this code.
// If you don't, and you vape your data then you only have yourself to blame.
// Seriously.  And if you're English is bad and you don't fully understand the
// instructions then STOP.  Right there.  Yes.  Before you do any damage.

// USE OF THIS SCRIPT IS ENTIRELY AT YOUR OWN RISK.  I/We accept no liability from its use.

// Written 20090525 by David Coveney of Interconnect IT Ltd (UK)
// Modified 20140722 by De'Yonte W/rxnlabs
// http://www.davesgonemental.com or http://www.interconnectit.com or
// http://spectacu.la and released under the WTFPL
// ie, do what ever you want with the code, and I take no responsibility for it OK?
// If you don't wish to take responsibility, hire me through Interconnect IT Ltd
// on +44 (0)151 709 7977 and we will do the work for you, but at a cost, minimum 1hr
// To view the WTFPL go to http://sam.zoy.org/wtfpl/ (WARNING: it's a little rude, if you're sensitive)

// Credits:  moz667 at gmail dot com for his recursive_array_replace posted at
//           uk.php.net which saved me a little time - a perfect sample for me
//           and seems to work in all cases.

// Database Settings

// WordPress specific replacement test
$wp_loader = __DIR__.DIRECTORY_SEPARATOR.'..'.DIRECTORY_SEPARATOR.'..'.DIRECTORY_SEPARATOR.'..'.DIRECTORY_SEPARATOR.'wp-load.php';
if( file_exists($wp_loader) ){

    define('DB_HOST','127.0.0.1');
    // load the wp-load.php file to use WordPress functions. File path is assumming this is being run from the current theme folder (any theme folder would work)
    require $wp_loader;
    $host = DB_HOST;
    $usr = DB_USER;
    $pwd = DB_PASSWORD;
    $db = DB_NAME;
}
else{
    // normally localhost, but not necessarily. Make this 127.0.0.1, NOT localhost for Macs due to this erros http://stackoverflow.com/questions/4219970/warning-mysql-connect-2002-no-such-file-or-directory-trying-to-connect-vi
    $host = '127.0.0.1';
    $usr  = 'database_user_name';
    $pwd  = 'database_user_password';
    $db   = 'database_name';
}

// Replace options

$search_for   = 'text_to_replace';  // the value you want to search for
$replace_with = 'replace_with_text';  // the value to replace it with

$cid = mysql_connect($host,$usr,$pwd);

if (!$cid) { echo("Connecting to DB Error: " . mysql_error() . "<br/>"); }


// First, get a list of tables

$SQL = "SHOW TABLES";
$tables_list = mysql_db_query($db, $SQL, $cid);

if (!$tables_list) {
    echo("ERROR: " . mysql_error() . "<br/>$SQL<br/>"); }


// Loop through the tables

while ($table_rows = mysql_fetch_array($tables_list)) {

    $count_tables_checked++;

    $table = $table_rows['Tables_in_'.$db];


    $SQL = "DESCRIBE ".$table ;    // fetch the table description so we know what to do with it
    $fields_list = mysql_db_query($db, $SQL, $cid);

    // Make a simple array of field column names

    $index_fields = "";  // reset fields for each table.
    $column_name = "";
    $table_index = "";
    $i = 0;

    while ($field_rows = mysql_fetch_array($fields_list)) {

        $column_name[$i++] = $field_rows['Field'];

        if ($field_rows['Key'] == 'PRI') $table_index[$i] = true ;

    }

//    print_r ($column_name);
//    print_r ($table_index);

// now let's get the data and do search and replaces on it...

    $SQL = "SELECT * FROM ".$table;     // fetch the table contents
    $data = mysql_db_query($db, $SQL, $cid);

    if (!$data) {
        echo("ERROR: " . mysql_error() . "<br/>$SQL<br/>"); }


    while ($row = mysql_fetch_array($data)) {

        // Initialise the UPDATE string we're going to build, and we don't do an update for each damn column...

        $need_to_update = false;
        $UPDATE_SQL = 'UPDATE '.$table. ' SET ';
        $WHERE_SQL = ' WHERE ';

        $j = 0;

        foreach ($column_name as $current_column) {
            $j++;
            $count_items_checked++;


            $data_to_fix = $row[$current_column];
            $edited_data = $data_to_fix;            // set the same now - if they're different later we know we need to update

//            if ($current_column == $index_field) $index_value = $row[$current_column];    // if it's the index column, store it for use in the update

            $unserialized = unserialize($data_to_fix);  // unserialise - if false returned we don't try to process it as serialised

            if ($unserialized) {


                recursive_array_replace($search_for, $replace_with, $unserialized);

                $edited_data = serialize($unserialized);


              }

            else {

                if (is_string($data_to_fix)) $edited_data = str_replace($search_for,$replace_with,$data_to_fix) ;

                }

            if ($data_to_fix != $edited_data) {   // If they're not the same, we need to add them to the update string

                $count_items_changed++;

                if ($need_to_update != false) $UPDATE_SQL = $UPDATE_SQL.',';  // if this isn't our first time here, add a comma
                $UPDATE_SQL = $UPDATE_SQL.' '.$current_column.' = "'.mysql_real_escape_string($edited_data).'"' ;
                $need_to_update = true; // only set if we need to update - avoids wasted UPDATE statements

            }

            if ($table_index[$j]){
                $WHERE_SQL = $WHERE_SQL.$current_column.' = "'.$row[$current_column].'" AND ';
            }
        }

        if ($need_to_update) {

            $count_updates_run;

            $WHERE_SQL = substr($WHERE_SQL,0,-4); // strip off the excess AND - the easiest way to code this without extra flags, etc.

            $UPDATE_SQL = $UPDATE_SQL.$WHERE_SQL;


            $result = mysql_db_query($db,$UPDATE_SQL,$cid);

            if (!$result) {
                    echo("ERROR: " . mysql_error() . "<br/>$UPDATE_SQL<br/>"); }

        }

    }

}

// Report

$report = $count_tables_checked." tables checked; ".$count_items_checked." items checked; ".$count_items_changed." items changed;";
echo '<p style="margin:auto; text-align:center">';
echo $report;

mysql_close($cid);


//  ---------

function recursive_array_replace($find, $replace, &$data) {

    if (is_array($data)) {
        foreach ($data as $key => $value) {
            if (is_array($value)) {
                recursive_array_replace($find, $replace, $data[$key]);
            } else {
                // have to check if it's string to ensure no switching to string for booleans/numbers/nulls - don't need any nasty conversions
                if (is_string($value)) $data[$key] = str_replace($find, $replace, $value);
            }
        }
    } else {
        if (is_string($data)) $data = str_replace($find, $replace, $data);
    }

}


?>