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

wp-block as a shortcode

In my last post I raved about the excellent plugin wp-blocks by Keir Whitaker and then went on to extend it a little.

Time to extend it a little further, this time by adding a shortcode.

Shortcodes are the handy square bracketed code snippets which conjure up more content and functionality direct from a page or post content.

The wp-blocks plugin is currently intended to work with the theme template files in PHP. Unfortunately (or fortunately?) PHP does not get parsed within the content areas. This does stop malicious code from being included, but it does limit things when you know what you’re doing.

The following code will give you a short code to work with which looks similar to the PHP code snippit you would include in your theme template file.

  /**
   * Gets the block data by 'name' and returns it for use in a content shortcode
   * Requires http://wordpress.org/extend/plugins/wp-blocks/
   *
   * @param string $name
   * @return string
   * @author Iain Cuthbertson
   */
  function get_wp_block_shortcode( $atts ) {
    extract( shortcode_atts( array(
      'name' => '',
    ), $atts ) );

    global $wpdb;
    $block_slug = trim($name);
    $data = (array)$wpdb->get_row("SELECT * FROM {$wpdb->prefix}wpb_content WHERE name = '{$name}' AND active = TRUE");  
    if($data) return get_wrapped_block_content($data);
  }
  
  add_shortcode('get_wp_block', 'get_wp_block_shortcode');

Add that to your theme’s functions.php file and then you will be able to add blocks to your content with, for example:

[get_wp_block name="foo"]

Getting a random wp-block by regex

If you’re used to developing with a CMS such as CMS Made Simple, you would be quite used to having blocks of content separate from the main content system. These blocks can be re-used through-out existing content and within the theme templates.

One might even consider this a core function for a CMS. Sadly it’s missing from WordPress. Keir Whitaker has been working on this missing option and gives us wp-blocks.

It’s a great little plugin, though it currently has a couple of niggles:

  • Apostrophes are escaped in the output: ‘
  • One can’t call a block from inside content, it has to be from the theme template.

Despite this, I’m glad that he’s made the plugin available for public use.

Now, time to extend it!

As with global content blocks in CMS Made Simple, wp-blocks does not offer a way to fetch a random content block. I fixed this in CMS Made Simple by writing the Random Global Content Block plugin.

Now I’ve done something similar for wp-blocks:

/**
 * Gets a random block data by 'regex'
 * Requires http://wordpress.org/extend/plugins/wp-blocks/
 *
 * @param string $block_slug_regex 
 * @return string
 * @author Iain Cuthbertson
 */
function get_wp_block_random($block_slug_regex) {
  global $wpdb;
  $block_slug_regex = trim($block_slug_regex);
  $data = (array)$wpdb->get_results("SELECT id FROM {$wpdb->prefix}wpb_content WHERE name REGEXP '{$block_slug_regex}' AND active = TRUE");  
  if (isset($data) && is_array($data) && count($data) > 0)
  {
    $arrayIndex = array();
    foreach ($data as $row)
    {
      $arrayIndex[] = $row->id;
    }
    $randomIndex = mt_rand(0, count($arrayIndex) - 1);
 
    return get_wp_block_by_id($arrayIndex[$randomIndex]);
  }
}

Copy that code into your theme’s functions.php and then call from within your template with, as an example:

This will then fetch a wp-block with a name starting with testimonial_.

Mimicking wordpress.com’s image resize URIs

To follow up from last night’s entry, I was determined to remove the intermediary step of my_resize_script.php.

I’ve acheived this goal in the mod_rewrite rule, file name and query string now get passed on to timthumb.php.

Updated rules:

RewriteCond %{REQUEST_FILENAME} -f
RewriteCond %{REQUEST_URI} .(gif|png|jpg|jpeg)
RewriteCond %{QUERY_STRING} (w|h)=(.*)$
RewriteRule (.*) /full/path/to/timthumb.php?src=$1&%1=%2&%3=%4 [L]

This will pass both width and height variables into timthumb if they are declared.

Quick breakdown of the rules:

  1. Check that the URI exists as a file.
  2. Check that the URI in question is an image. Expand to other formats as needed.
  3. Check that one or both of width and height are being altered.
  4. Pass the file following to timthumb as variables:
    $1 = filename
    %1 = w or h
    %2 = value of w or h
    %3 = w or h
    %4 = value of w or h

If conditions 1 to 3 all return true, then the rewrite rule in 4 will be executed.

In summary, the URI
http://myrant.net/wp-content/uploads/2010/03/high_res_horsey.png?w=300&h=100

Will be passed on as
http://myrant.net/timtumb.php?src=wp-content/uploads/2010/03/high_res_horsey.png&w=300&h=100

Resize images on the fly without messing with image URLs

[EDIT: I’ve made this simpler with a follow up post: Mimicking wordpress.com’s image resize URIs]

Exporting a wordpress.com site for use on a standalone wordpress.org install is a joy to set up. The export and import system are simple to use and give little or no issues.

What is a problem is the automatic resizing of images that wordpress.com offers.

One can insert an image in the normal way and then append the image URI with some variables such as width (w) and height (h). This is also a d options, I assume this is for depth, but haven’t looked into it yet.

So an image URI might be http://myrant.net/wp-content/uploads/2010/03/high_res_horsey.png?w=300

This URI would display the image resized to 300px wide and retain the aspect ratio.

Currently, wordpress.org do not offer this facility (that I have seen). So I’ve been working on how it might be done:

1st off, we need to set up a mod_rewrite rule to cater for image URIs that have query string variables. This can be added to a vhost config or the site’s .htaccess file. I prefer to use .htaccess

RewriteCond %{REQUEST_FILENAME} -f
RewriteCond %{REQUEST_URI} .(gif|png|jpg|jpeg)
RewriteCond %{QUERY_STRING} (w|h)=(.*)$
RewriteRule (.*) /my_resize_script.php?file=$1 [L]

Then we need a script (my_resize_script.php) to make use of the variables.

 $_GET['file']);
foreach ($queryString as $bit)
{
	$varBits = explode("=", $bit);
	$urlVars[$varBits[0]] = $varBits[1];
}
?>

The output of this script as it is would be:

Array
(
    [file] => wp-content/uploads/2010/03/high_res_horsey.png
    [w] => 300
)

As you can see, I’m not actually making use of the variables yet. But all of the information that it does extract can be given to a proper resize script, such as TimThumb.

This is all a bit messy right now. I would much rather be able to pass variables directly into a resize script rather than have to use an intermediary one. If anybody wants to give some pointers, please do 🙂

Updating WordPress site URLs for different hosts

Why oh why does WordPress insist on storing the site URLs within the database?
Every other project I can think of either keeps it in a config file or doesn’t bother with one and just works with whatever URL it finds itself on.

This is most irritating when you have:

  • more than one developer working on the same project
  • more than one system for hosting the project (dev, test, live, etc)

One way to cope with this irritation is to have a collection of .sql files to handily update the database from the CLI.

For example, a WP site has the URL www.idophp.co.uk on a live host and uses idophp.iain on a dev host.

We would have 2 .sql files such as live_options.sql and dev_options.sql.

In live_options.sql:

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://idophp.iain', 'http://www.idophp.co.uk') WHERE option_value LIKE 'http://idophp.iain%';

In dev_options.sql:

UPDATE wp_options SET option_value = REPLACE(option_value, 'http://www.idophp.co.uk', 'http://idophp.iain') WHERE option_value LIKE 'http://www.idophp.co.uk%';

These settings can be imported over the top of a database without altering anything but the site URLs using:

$ mysql -u idophp -p idophp < live_options.sql