Updating a WordPress database with new domain details

Further to my original quick db update queries, I found the need for a more in-depth approach.

A WordPress site my father works on has weekly backups, but he wanted to see that they actually worked.
To do so, I set up a subdomain vhost on my bytemark server and set about getting the back to work with it.

One of the most annoying/lazy/strange things about WordPress is that it uses serialised arrays within database fields. This makes a simple search and replace fail if you need to do anything more than update the two fields here.

This script works for the database set-up for my father. It may require further tweaks if additional plugins store absolute paths or URLs.

It also could do with a heavy dose of refactoring, but it works for my needs at this point in time 🙂

 'wp3/wp-config.php');
if (!isset($argv[1]))
{
	die("Please tell me where wp-config.php isn");
}

$oldURL = 'http://oldDomainName.com';
$newURL = 'http://backup.of.my.wordpress.site.com';

$oldPath = '/home/chem9598/public_html/';
$newPath = '/var/www/backup_wordpres_site/codebase/htdocs/';

$configFile = $argv[1];
include_once($configFile);

$db = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

/*
* 1.1) wp_options - simple options
*/
echo "
1.1) wp_options - simple options: "; $updateOptionsSql = "UPDATE wp_options SET option_value = REPLACE(option_value, '" . $oldURL . "', '" . $newURL . "') WHERE option_name IN ('siteurl', 'home');"; $updateOptionsRes = mysql_query($updateOptionsSql, $db); if (!$updateOptionsRes) echo mysql_error($db) . "
"; else echo mysql_affected_rows ($db) . " rows affected
"; /* * 1.2) wp_options - serialised array options */ echo "
1.2) wp_options - serialised array options: "; $selectOptionsSql = 'SELECT * FROM wp_options WHERE option_value LIKE "%' . $oldURL . '%"'; $selectOptionsRes = mysql_query($selectOptionsSql, $db); if (!$selectOptionsRes) echo mysql_error($db) . "
"; $options = array(); while($selectOptionsRes && $row = mysql_fetch_assoc($selectOptionsRes)) { $options[] = $row; } foreach ($options as $option) { if (substr($option['option_value'], 0, 2) == 'a:') { $optionValue = unserialize($option['option_value']); updateArray($optionValue, $oldURL, $newURL); $option['option_value'] = serialize($optionValue); $updateOptionsSql = "UPDATE wp_options SET option_value = '" . mysql_escape_string($option['option_value']) . "' WHERE option_id = " . $option['option_id']; $updateOptionsRes = mysql_query($updateOptionsSql, $db); if (!$updateOptionsRes) echo mysql_error($db) . "
"; else echo "."; } } echo "
"; /* * 1.3) wp_options - file path */ echo "
1.3) wp_options - file path: "; $selectOptionsSql = 'SELECT * FROM wp_options WHERE option_value LIKE "%' . $oldPath . '%"'; $selectOptionsRes = mysql_query($selectOptionsSql, $db); if (!$selectOptionsRes) echo mysql_error($db) . "
"; $options = array(); while($selectOptionsRes && $row = mysql_fetch_assoc($selectOptionsRes)) { $options[] = $row; } foreach ($options as $option) { if (substr($option['option_value'], 0, 2) == 'a:') { $optionValue = unserialize($option['option_value']); updateArray($optionValue, $oldPath, $newPath); $option['option_value'] = serialize($optionValue); $updateOptionsSql = "UPDATE wp_options SET option_value = '" . mysql_escape_string($option['option_value']) . "' WHERE option_id = " . $option['option_id']; $updateOptionsRes = mysql_query($updateOptionsSql, $db); if (!$updateOptionsRes) echo mysql_error($db) . "
"; else echo "."; } } echo "
"; /* * 2.1) wp_posts - guid */ echo "
2.1) wp_posts - guid: "; $updatePostsSql = "UPDATE wp_posts SET guid = REPLACE(guid, '" . $oldURL . "', '" . $newURL . "');"; $updatePostsRes = mysql_query($updatePostsSql, $db); if (!$updatePostsRes) echo mysql_error($db) . "
"; else echo mysql_affected_rows ($db) . " rows affected
"; /* * 2.2) wp_posts - post_content */ echo "
2.2) wp_posts - post_content: "; $updatePostsSql = "UPDATE wp_posts SET post_content = REPLACE(post_content, '" . $oldURL . "', '" . $newURL . "');"; $updatePostsRes = mysql_query($updatePostsSql, $db); if (!$updatePostsRes) echo mysql_error($db) . "
"; else echo mysql_affected_rows ($db) . " rows affected
"; function debug($var) { echo "<" . "pre>"; print_r($var); echo "<" . "/pre>"; } function updateArray(&$array, $find, $replace) { foreach ($array as $key => &$value) { if (is_array($value)) { updateArray($value, $find, $replace); } else { $array[$key] = str_replace($find, $replace, $value); } } }

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.