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 🙂

<?php
$argv = (isset($argv)) ? $argv : array(1 => '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 "<br />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) . "<br />";
else
	echo mysql_affected_rows ($db) . " rows affected<br />";


/*
* 1.2) wp_options - serialised array options
*/
echo "<br />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) . "<br />";

$options = array();
while($selectOptionsRes &amp;&amp; $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) . "<br />";
		else
			echo ".";
	}
}
echo "<br />";


/*
* 1.3) wp_options - file path
*/
echo "<br />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) . "<br />";

$options = array();
while($selectOptionsRes &amp;&amp; $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) . "<br />";
		else
			echo ".";
	}
}
echo "<br />";


/*
* 2.1) wp_posts - guid
*/
echo "<br />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) . "<br />";
else
	echo mysql_affected_rows ($db) . " rows affected<br />";


/*
* 2.2) wp_posts - post_content
*/
echo "<br />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) . "<br />";
else
	echo mysql_affected_rows ($db) . " rows affected<br />";


function debug($var)
{
	echo "<" . "pre>";
	print_r($var);
	echo "<" . "/pre>";
}

function updateArray(&amp;$array, $find, $replace)
{
	foreach ($array as $key => &amp;$value)
	{
		if (is_array($value))
		{
			updateArray($value, $find, $replace);
		}
		else
		{
			$array[$key] = str_replace($find, $replace, $value);
		}
	}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

<?php

$argv = (isset($argv)) ? $argv : array(1 => '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 Cancel reply