Find and Replace in MySQL Database

WordPress and many of her plugins store data in serialized arrays.   Plugin authors do this to save coding time, as it is minimal coding to create you option pages with array-based name inputs.  For example:

<input type=”text” name=”myplugin[its_name]” />
<input type=”text” name=”myplugin[its_version]” />
<select name=”myplugin[is_cool]>…</select>

Well, before the Settings API was introduced, there was:

<?php wp_nonce_field(‘update-options’); ?>
<input type=”hidden” name=”action” value=”update” />
<input type=”hidden” name=”page_options” value=”myplugin” />

See, page_options was a comma-separated list of values you would be posting to the server.  This made for quick coding of options pages, but also made for easy getting the options via $options = get_option(‘myplugin’,get_myplugin_defaults());  Then you could use $options['its_name'] just like that.

The problem with serializing these options is that many plugins also serialize the URL of the website as one of the options.  This can be problemmatic if you wish to move the location of your website.  Your serialized option may look something like this: a:2:{i:0;s:13:"yoursite.com";i:1;a:2:{i:0;s:13:"yoursite.com";i:1;R:3;}}

The s:13 above indicates yoursite.com is 13 char[].  Now when moving websites you think that you might just be able to run MySQL UPDATE queries using the MySQL REPLACE() when doing a search and replace… Or you may believe you will use sed or perl -pi -w -e 's/search/replace' or whatever search & replace you can think of (I am sure there are more) to replace a mysqldump file.  Well, unless you new domain has the same number of characters that does not work very well.   Your serialized string becomes: a:2:{i:0;s:13:"yournewsite.com";i:1;a:2:{i:0;s:13:"yournewsite.com";i:1;R:3;}}

Where’s the light at the end of the tunnel?  Below is a script I created (well, modified) to include my previously-posted script in a way that will find & replace all instances (serialized or otherwise) in a MySQL Database.  You can hard code credentials directly into the script under the setup/default or you can pass it as an argument via command line.  You can also use -v for verbosity that will tell you how long the script took to run, how many items were replaced, etc.  You can use –help for more info

#!/usr/local/bin/php -q
<?php
    //  Start TIMER
    $stimer = explode( ' ', microtime() );
    $stimer = $stimer[1] + $stimer[0];

    include_once 'include/commandline.class.php';
    $args = CommandLine::parseArgs($argv);

    if ($args['help'] || $args['?']){

        echo <<<HELP

F&R Database
by David Coveney

Command Line Interface with arguments by:
Chris Heney

Options
    --db-name      Name of the database to be used in the search
    --db-host      Location of the host (default: localhost)
    --db-user      Username to use in connecting to the database
    --db-pass      Password to connect to the database
    --find         *Required - The term to search for to be replaced
    --replace-with *Required - Term for replacing the find term
    --verbose,-v   Be noisy


HELP;

        die();
    }


    if (!$args['find'] || !$args['replace-with'] || !$args['db-name'])
        die("You need to at least supply a --find=\"Search Term\", a --replace-with=\"Replace Term\" and a --db-name=\"mydatabase\".  Type --help for details.\n");

    $defaults['db-host']      = 'localhost';
    $defaults['db-user']      = 'cheney';
    $defaults['db-pass']      = 'Atmr815T@%';
    $defaults['verbose']      = false;

    $options = array_merge($defaults,$args);

    if ($args['v'])
        $options['verbose'] = true;

/**
 * START HELPER FUNCTIONS
 **/
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);
    }

}
		
/**
 * END HELPER FUNCTIONS
 **/

/**
 * DO NOT EDIT PAST THIS POINT UNLESS UR L33t
 **/

$cid = mysql_connect($options['db-host'],$options['db-user'],$options['db-pass']) or die("Connecting to DB Error: " . mysql_error() . "\n");
mysql_select_db($options['db-name'],$cid) or die("Connecting to DB Error: " . mysql_error() . "\n");

// First, get a list of tables
$SQL = "SHOW TABLES";
$tables_list = mysql_query($SQL,$cid);

if (!$tables_list) {
	if ($options['verbose'])
		echo("\nERROR: " . mysql_error() . "\n$SQL\n");

	die(); exit;
}

// Loop through the tables
while ($table_rows = mysql_fetch_array($tables_list)) {
	$count_tables_checked++;
	$table = $table_rows['Tables_in_'.$options['db-name']];

	$SQL = "DESCRIBE ".$table ; // fetch the table description so we know what to do with it
	$fields_list = mysql_query($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 ;

	}

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

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

	if (!$data) {
		if ($options['verbose'])
			echo("ERROR: " . mysql_error() . "\n$SQL\n");

		die(); exit;
	}

	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;
	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];

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

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

			if ($unserialized) {
				recursive_array_replace($options['find'], $options['replace-with'], $unserialized);
				$edited_data = serialize($unserialized);
			} else {
				if (is_string($data_to_fix)) $edited_data = str_replace($options['find'],$options['replace-with'],$data_to_fix);
			}

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

				$count_items_changed++;

				// if this isn't our first time here, add a comma
				if ($need_to_update != false) $UPDATE_SQL = $UPDATE_SQL.',';
				$UPDATE_SQL = $UPDATE_SQL.' '.$current_column.' = "'.mysql_real_escape_string($edited_data).'"' ;

				// only set if we need to update - avoids wasted UPDATE statements
				$need_to_update = true;

			}

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

		if ($need_to_update) {
			$count_updates_run;

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

			$UPDATE_SQL = $UPDATE_SQL.$WHERE_SQL;

			$result = mysql_query($UPDATE_SQL,$cid);

			if (!$result && $options['verbose']) {
				echo("ERROR: " . mysql_error() . "\n$UPDATE_SQL\n");
			}
		}
	}
}

// Report

$report = $count_tables_checked." tables checked; ".$count_items_checked." items checked; ".$count_items_changed." items changed;";

if ($options['verbose'])
	echo "Database Find & Replace Reports: ".$report."\n\n";

mysql_close($cid);

//  End TIMER
//  ---------
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];

if ($options['verbose'])
	printf( "Script timer: %f seconds.\n\n", ($etimer-$stimer) );

?>

Too Good For BASH? Love PHP? Read On!

So you know PHP inside and out, you are very proficient. You also like the system commands that can be executed with bash, such as sed, and many other command-line tools. Imagine having all of the flexibility of being able to parse command line arguments with php, and use the brute strength of bash, tie them both together without all the hassle of creating a view layer and having to access it with a browser.

Now a find and replace for a certain term, even when serialized in a database can be done with a 1-liner that looks like: root@host [~]: db-fr.php --find="searchterm" --replace="replaceterm" --db="mydb" --dbuser="user" --dbpass="dbpass"

Now I know your screaming: What?! $argv[]?! What a pain in the genitals. But not with the code below. While searching all over I found this beautiful script that a posted linked to on PHP.net that parses command-line arguments sent to PHP scripts, and places them accessible name-value pair arrays!

The short of it -abc is translated $option['a'], $option['b'] and $option['c'] are (bool)true, while –foo=”bar” is translated $option['foo'] = (string)”bar”. The class is heavily documented but can be integrated with just 2 lines into any application you want:

include_once 'include/commandline.class.php';
$option = CommandLine::parseArgs($argv);


With that said, enjoy the command line parser.

<?php
/**
 * CommandLine class
 *
 * @package             Framework
 */
/**
 * Command Line Interface (CLI) utility class.
 *
 * @author              Patrick Fisher 
 * @since               August 21, 2009
 * @package             Framework
 * @subpackage          Env
 */
class CommandLine {

    public static $args;

    /**
     * PARSE ARGUMENTS
     *
     * This command line option parser supports any combination of three types
     * of options (switches, flags and arguments) and returns a simple array.
     *
     * [pfisher ~]$ php test.php --foo --bar=baz
     *   ["foo"]   => true
     *   ["bar"]   => "baz"
     *
     * [pfisher ~]$ php test.php -abc
     *   ["a"]     => true
     *   ["b"]     => true
     *   ["c"]     => true
     *
     * [pfisher ~]$ php test.php arg1 arg2 arg3
     *   [0]       => "arg1"
     *   [1]       => "arg2"
     *   [2]       => "arg3"
     *
     * [pfisher ~]$ php test.php plain-arg --foo --bar=baz --funny="spam=eggs" --also-funny=spam=eggs \
     * > 'plain arg 2' -abc -k=value "plain arg 3" --s="original" --s='overwrite' --s
     *   [0]       => "plain-arg"
     *   ["foo"]   => true
     *   ["bar"]   => "baz"
     *   ["funny"] => "spam=eggs"
     *   ["also-funny"]=> "spam=eggs"
     *   [1]       => "plain arg 2"
     *   ["a"]     => true
     *   ["b"]     => true
     *   ["c"]     => true
     *   ["k"]     => "value"
     *   [2]       => "plain arg 3"
     *   ["s"]     => "overwrite"
     *
     * @author              Patrick Fisher 
     * @since               August 21, 2009
     * @see                 http://www.php.net/manual/en/features.commandline.php
     *                      #81042 function arguments($argv) by technorati at gmail dot com, 12-Feb-2008
     *                      #78651 function getArgs($args) by B Crawford, 22-Oct-2007
     * @usage               $args = CommandLine::parseArgs($_SERVER['argv']);
     */
    public static function parseArgs($argv){

        array_shift($argv);
        $out                            = array();

        foreach ($argv as $arg){

            // --foo --bar=baz
            if (substr($arg,0,2) == '--'){
                $eqPos                  = strpos($arg,'=');

                // --foo
                if ($eqPos === false){
                    $key                = substr($arg,2);
                    $value              = isset($out[$key]) ? $out[$key] : true;
                    $out[$key]          = $value;
                }
                // --bar=baz
                else {
                    $key                = substr($arg,2,$eqPos-2);
                    $value              = substr($arg,$eqPos+1);
                    $out[$key]          = $value;
                }
            }
            // -k=value -abc
            else if (substr($arg,0,1) == '-'){

                // -k=value
                if (substr($arg,2,1) == '='){
                    $key                = substr($arg,1,1);
                    $value              = substr($arg,3);
                    $out[$key]          = $value;
                }
                // -abc
                else {
                    $chars              = str_split(substr($arg,1));
                    foreach ($chars as $char){
                        $key            = $char;
                        $value          = isset($out[$key]) ? $out[$key] : true;
                        $out[$key]      = $value;
                    }
                }
            }
            // plain-arg
            else {
                $value                  = $arg;
                $out[]                  = $value;
            }
        }
        self::$args                     = $out;
        return $out;
    }

    /**
     * GET BOOLEAN
     */
    public static function getBoolean($key, $default = false){
        if (!isset(self::$args[$key])){
            return $default;
        }
        $value                          = self::$args[$key];
        if (is_bool($value)){
            return $value;
        }
        if (is_int($value)){
            return (bool)$value;
        }
        if (is_string($value)){
            $value                      = strtolower($value);
            $map = array(
                'y'                     => true,
                'n'                     => false,
                'yes'                   => true,
                'no'                    => false,
                'true'                  => true,
                'false'                 => false,
                '1'                     => true,
                '0'                     => false,
                'on'                    => true,
                'off'                   => false,
            );
            if (isset($map[$value])){
                return $map[$value];
            }
        }
        return $default;
    }
}
?>

Syntax Highligher for the Admin Code Editor

Ever have that guy on your team that just can’t seem to use a syntax highlighter… and insists (s)he’s good enough to use the browser html form-post method of editing code via the theme editor or the plugin editor?  We’ll everyone has one.  Or maybe there are just people who have a lot of confidence when it comes to that kind of thing.

Personally I prefer emacs or vim (enhanced) for doing any kind of code editing as opposed to web form based editing because:

• There is no undo after you have submitted
• It is easy to make mistakes, especially for those who code at 75 WPM or have a blurry contact lens
• Textareas are pretty featureless (unless you are using chrome, then you can resize)

So I decided, for those who want to use a textarea for their editing, why not make their lives a little better too… I mean, maybe their hosting company wants you to send in 3 sets of ID for a shell account… who knows.  I am sure someone will use this idea I have devised.

Remember Mozilla’s Besbin web based code editor and syntax highlighter?  If you don’t, and you are dying to know what it was about, read this wikipedia entry.  I was dying for it to come out, especially with google-doc style multi-user capabilities.

Well, while I was waiting the guys at CodeMirror made a sick editor.  But really, it was their tagline that won me over: “   /* In-browser code editing made bearable */ “.

So I figured that would be cool for the guys on my development team, so I spent 3 or 4 hours creating a WordPress Plugin to integrate it.  I still haven’t named it yet, or made a readme, but I want to distribute it now.  One of my favorite pieces is it offers code completion!

The syntax highlighter does PHP, JavaScript and CSS.  I will be uploading to wordpress.org in moments.  The whole point of this blog is I ran into a required field plugin/author/whatever website, and realized, I don’t have one.