migrate_wordpress_db_values_to_new_vhost.php

#!/usr/bin/env php
<?php

/* BEGIN CONFIG for this script */

$globalSearchStrings = [
  'relevance.com',
];

$newDomain = 'localhost';

/* URLs */

$oldUrls = [
  'http://relevance.com',
  'http://www.relevance.com',
];
$newUrl = 'http://' . $newDomain;

/* File Paths */

$oldFilePaths = [
  '/var/www',
];
$newFilePath = '/Applications/MAMP/htdocs/relevance_wp';

/* END CONFIG */

$findAndReplace = [];

foreach($oldFilePaths as $oldFilePath) {
    $findAndReplace[$oldFilePath] = $newFilePath;
}

foreach($oldUrls as $oldUrl) {
    $findAndReplace[$oldUrl] = $newUrl;
}

//$findAndReplace['somethingcustom'] = 'replacecustom';

error_reporting(E_ALL & ~E_WARNING);

ini_set('xdebug.var_display_max_depth', 5);

#require_once __DIR__ . '/../env.php';

$conn = new mysqli("localhost", "root", "root", "wp_relevance");

// Delete "transient" options
$conn->query("DELETE FROM wp_options WHERE option_name LIKE '%transient%'");

$res = $conn->query("
SELECT
  option_id,
  option_name,
  option_value
FROM
  wp_options
WHERE
  option_value LIKE 'a:%'
  AND (option_value LIKE '%" . implode("%' OR option_value LIKE '%", $globalSearchStrings) . "%')
");

$rows = [];

while ($row = $res->fetch_assoc()) {
    $rows[$row['option_id']] = $row['option_value'];
}

foreach ($rows as $option_id => $option_value) {
    $unserialized = unserialize($option_value);

    array_walk_recursive($unserialized, function(&$item) use($findAndReplace) {
        foreach ($findAndReplace as $find => $replace) {
            $item = str_replace($find, $replace, $item);
        }
    });

    $replacements[$option_id] = $unserialized;

    $newValue = serialize($unserialized);

    if ($newValue === $option_value) {
        continue;
    }

    $stmt = $conn->stmt_init();

    $stmt->prepare("UPDATE wp_options SET option_value=? WHERE option_id=?");
    $stmt->bind_param('si', $newValue, $option_id);
    $stmt->execute();
    echo "Updated wp_options #$option_id (serialized value)\n";
}

foreach ($findAndReplace as $find => $replace) {
    $stmt = $conn->stmt_init();

    $stmt->prepare("
        UPDATE
          wp_options
        SET
          option_value = replace(option_value, ?, ?)
        WHERE
          option_value LIKE '%" . implode("%' OR option_value LIKE '%", $globalSearchStrings) . "%'"
    );

    $stmt->bind_param('ss', $find, $replace);
    $stmt->execute();
}

foreach ($oldUrls as $oldUrl) {
    $conn->query("UPDATE wp_posts SET guid = replace(guid, '$oldUrl', '$newUrl')");
    $conn->query("UPDATE wp_posts SET post_content = replace(post_content, '$oldUrl', '$newUrl')");
    $conn->query("UPDATE wp_postmeta SET meta_value = replace(meta_value, '$oldUrl', '$newUrl')");
}