Category: Programming

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);
		}
	}
}