Today I Learned: Update serialized WordPress data with WP CLI

Today I was working through some QA issues related to migrating a WordPress website from one server to another. The main restriction for fixing these issues was to try and do it with as few code changes as possible, and to update the data instead.

The issue

One of the pages on the site, My Account, has a handful of linked tiles on it that are simply an image and some text. On the migrated site, these images were not being displayed and the img tag was being printed with a blank src attribute.

An inspection of the options page where these tiles are set up revealed that everything seemed to be in order. Images were set, and the images did exist on the server.

Finding the cause

The next step was to figure out why the src attribute was blank, despite an image being set on the option screen. It turns out that the code was expecting there to be an image_id in the tile array, which it then uses to generate a URL with the wp_get_attachment_image_src function.

This image_id did not have a field of its own, and so it was probably being saved as part of the image upload.

Fixing the issue

All the data for the tiles was being saved to the database into a single serialized data field. Serialized data is notorious for being incredibly sensitive to mistakes and very easy to break and looks something like this (this is an example of the active_plugins option that WordPress uses to keep track of what plugins are activated on the site):

a:6:{i:0;s:31:"query-monitor/query-monitor.php";i:1;s:57:"accesspress-instagram-feed/accesspress-instagram-feed.php";i:2;s:19:"easy-captcha/easy-captcha.php";i:3;s:43:"google-analytics-dashboard-for-wp/gadwp.php";i:4;s:33:"instagram-feed/instagram-feed.php";i:5;s:19:""really-simple-captcha/really-simple-captcha.php";}

I’m sure you can imagine that this will get hairy quicky for an option that saves the field ID and the field value for six fields for every tile (and this site has four tiles set).

WP CLI to the rescue!

And this is where WP CLI comes in. It turns out that you can retrieve options in a number of different formats with the option get command. By default, this will use var_export, but you can pass it the format flag to export the option value as JSON. And since we are using the CLI, we can use > to save the output to a text file, eg.:

wp option get active_plugins --format=json > active_plugins.txt

Now you have a JSON file that can be easily modified with the updated data, in this case the IDs for the images that were saved, and then another WP CLI command can be used to update the option with the new value:

wp option update active_plugins --format=json < active_plugins.txt

Success! Many, many thanks to lalo for sharing this on https://wordpress.stackexchange.com/a/199315. Saved me a lot of stress writing a MySQL UPDATE query to (hopefully) update this option manually.