<?php

// A way to globally replace strings in a MySQL database, particularly useful for changing the name
// of a host when moving a WordPress installation from one server to another. This script also auto
// detects any serialized PHP objects, unserializes them, replaces the string and then saves the
// reserialized object.

if (sizeof($argv) !== 7) {
  die("usage: php mysql-replace-string.php host db-name username password old-string new-string\n");
}

$host = $argv[1];
$dbName = $argv[2];
$username = $argv[3];
$password = $argv[4];
$oldStr = $argv[5];
$newStr = $argv[6];

$db = new PDO("mysql:host=$host;dbname=$dbName;charset=utf8mb4", $username, $password, array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));

function replaceNestedStrings(&$obj, $pattern, $replacement) {
  if (is_array($obj)) {
    foreach ($obj as &$item) {
      replaceNestedStrings($item, $pattern, $replacement);
    }
  } else if (is_string($obj)) {
    $obj = str_replace($pattern, $replacement, $obj);
  }
}

foreach ($db->query('SHOW TABLES') as $tables) {

  foreach ($db->query("SELECT * FROM {$tables[0]}") as $row) {

    $updates = [];
    $where = [];
    $newValues = [];
    $oldValues = [];

    foreach ($row as $n=>$column) {
      if (!is_int($n)) {

        $oldValues[] = $row[$n];

        // echo "$n=>$column\n";
        $data = @unserialize($column);
        if ($data !== false) { // is a serialized obj?
          replaceNestedStrings($data, $oldStr, $newStr);
          $row[$n] = serialize($data);
        } else {
          replaceNestedStrings($row[$n], $oldStr, $newStr);
        }

        // Use $n to prefix so that merge below doesn't overwrite
        $newValues[$n] = $row[$n];

        $updates[] = "$n=?";
        $where[] = "$n=?";
      }
    }

    $updatesStr = implode(',', $updates);
    $whereStr = implode(' AND ', $where);

    $sql = "UPDATE {$tables[0]} SET {$updatesStr} WHERE {$whereStr}";

    $stmt = $db->prepare($sql);

    // Need to use array_values as PDO expects numeric indexes
    $stmt->execute(array_values($newValues + $oldValues));

  }

}