Bulk Updating Serialized WordPress Meta Values

Published by John on August 14, 2015 Under PHP, Wordpress

Recently, a client asked me to launch their new site, which was built using a premium WordPress template. If you haven’t ever used one of these templates, they typically replace WordPress’s default editor with an often buggy editor that lets you add their custom shortcodes.

In most cases, the steps in my post on useful WordPress mysql commands would be enough to launch the site. However, in this case there were a lot of serialized values in the wp_postmeta and wp_options tables, which were used on the pages.

Serialized data is basically a textual representation of php variables like arrays, which could not otherwise be stored. You can call serialize on an array to convert it to a save-able string and then when you are ready to use it, you can call unserialize to convert it back into an array*.

* When discussing PHP’s serialize function, it is important to mention that json_encode and json_decode are typically a better solution, especially when dealing with user submitted data. Serializing and unserializing un-trusted data opens you up to several vulnerabilities and json_encode is a safer alternative.

Update: If you need a non-wordpress solution, see here.

The Problem

Data stored in WordPress’s postmeta and options tables contains an old url(like from a development site) and needs to be updated to the new url. However, the data is serialized, so if you just do a normal Mysql replace, it will break the serialized data.

A Fix

Before you do anything, backup your database

No really, backup your database! It is possible the below could cause a problem. If you don’t make a backup now and something goes wrong, you may loose data!

The below script can be used to update wordress’s wp_postmeta and wp_options tables. It checks for an old_url and then replaces the data, unserializing and serializing as necessary.

There are three variables that you will need to change:

require_once("../wp-load.php");

Update this with the path to your wordpress wp-load.php file. In my case, I just put this file in a folder in the web root, so the above works.

$old_url = 'your_old_url';

Unless you are moving from non-http -> https or really need to, I would recommend putting just the url it self here. For example, I would just use www.knightdale-computer-repair.com if I was doing it on this site.

$new_url = 'your_new_url';

Again, I would avoid using the http:// portion of your url here, unless you also do it above. However, make sure to include ‘www.’ if that is the url you use on your new website.

The below code can be downloaded here: kcr_meta_update_2015-08-14.zip

After uploading and adding these files to your server, visit it in your web-browser. Make sure to remove it afterwards, as I’m not checking to make sure you are logged in.

It should handle serialized data and process arrays recursively. A similar approach would work for json_encoded data, although you would have to do a different check to determine if it is json_encoded, as I am using wordpress’s is_serialized function to check if it is serialized.

<?php

/* 
 * Safely update wp_postmeta table with a new url
 * Author: John
 * Author Url: http://www.kcrnc.com
*/

require_once("../wp-load.php");

global $wpdb;

$old_url = 'your_old_url';
$new_url = 'your_new_url';

$query = "select * from {$wpdb->postmeta} where meta_value like %s";
$where_fields = array('%'.$old_url.'%');
$sql = $wpdb->prepare($query, $where_fields);

$update_post_meta = $wpdb->get_results($sql , ARRAY_A );

$result_count = count($update_post_meta);

echo "<p>Starting Check for: {$old_url} on {$result_count} results</p>";

foreach($update_post_meta as $meta_data){
	
	$original_meta = $meta_data['meta_value'];
	$is_serialized = false;
	
	if(is_serialized($original_meta)){
		$original_meta = unserialize($original_meta);
		$is_serialized = true;
	}
	
	$new_meta =  kcr_process_meta_values($original_meta, $old_url, $new_url);
	
	if($is_serialized){
		$new_meta = serialize($new_meta);
	}
	
	$update_result = $wpdb->update($wpdb->postmeta, array("meta_value"=>$new_meta),array("meta_id"=>$meta_data['meta_id']), array("%s"), array("%d"));

	if($result === false){
		echo "Error Updating {$meta_data['meta_id']}";
		
		if(!empty($wpbd->last_error)){
			echo "Mysql Error: Updating {$wpbd->last_error}";
		}
		echo "<br />";
	}
	else {
		echo "Updated Meta ID :  {$meta_data['meta_id']}<br />";			
	}	
}


function kcr_process_meta_values($meta_values, $old_url, $new_url){

	if(!is_array($meta_values)){
		return str_replace($old_url, $new_url, $meta_values);		
	}

	foreach($meta_values as $meta_key => &$meta_value){	
		$meta_value = kcr_process_meta_values($meta_value, $old_url, $new_url);		
	}

	return $meta_values;

}

1 Comment |