Using WP-CLI to Batch Update Serialized wp_options Settings

More WordPress plugins and themes are storing their settings as serialized options in the wp_options table. Sometimes we need to batch update these serialized settings for one reason or another and before it was only possible to update these programmatically with WP-CLI if the setting in the database was a simple non-serialized version. Luckily WP-CLI created the patch and pluck commands in version 1.4.0 so now any setting type can be updated with the power of the WP-CLI tool.

Using WP-CLI to Explore and Update Serialized wp_options

In this example I am using WP-CLI to investigate the Social Warfare plugin settings.

We need to get the option_names associated with the plugin first, this is from my other blog post about easily identifying WordPress plugin options.

wp option list --field=option_name | grep warfare

You will get a list like this

social_warfare_dismissed_notices
social_warfare_settings

Using the option get command we get inspect the settings in more detail

wp option get social_warfare_settings

Output

array (
  'location_archive_categories' => 'below',
  'location_home' => 'none',
  'location_post' => 'below',
  'location_page' => 'below',
  'float_location_post' => 'on',
  'float_location_page' => 'off',
  'total_shares' => true,
  'network_shares' => true,
  'twitter_id' => false,
  'swp_twitter_card' => true,
  'button_shape' => 'flatFresh',
  'default_colors' => 'full_color',
  'single_colors' => 'full_color',
  'hover_colors' => 'full_color',
  'float_default_colors' => 'full_color',
  'float_single_colors' => 'full_color',
  'float_hover_colors' => 'fullColor',
  'float_style_source' => true,
  'float_size' => 1,
  'float_alignment' => 'center',
  'button_size' => 1,
  'button_alignment' => 'fullWidth',
  'transition' => 'slide',
  'float_screen_width' => 1100,
  'ctt_theme' => 'style1',
  'ctt_css' => '',
  'twitter_shares' => false,
  'floating_panel' => true,
  'float_location' => 'bottom',
  'float_background_color' => '#ffffff',
  'float_button_shape' => 'default',
  'float_vertical' => 'center',
  'float_button_count' => 5,
  'custom_color' => '#000000',
  'custom_color_outlines' => '#000000',
  'float_custom_color' => '#000000',
  'float_custom_color_outlines' => '#000000',
  'recover_shares' => false,
  'recovery_format' => 'unchanged',
  'recovery_protocol' => 'unchanged',
  'recovery_prefix' => 'unchanged',
  'decimals' => 0,
  'decimal_separator' => 'period',
  'totals_alignment' => 'total_sharesalt',
  'google_analytics' => false,
  'bitly_authentication' => false,
  'minimum_shares' => 0,
  'full_content' => false,
  'frame_buster' => false,
  'analytics_medium' => 'social',
  'analytics_campaign' => 'SocialWarfare',
  'swp_click_tracking' => false,
  'order_of_icons_method' => 'manual',
  'og_post' => 'article',
  'og_page' => 'article',
  'pinterest_image_location' => 'hidden',
  'pin_browser_extension' => false,
  'pinterest_fallback' => 'all',
  'pinit_toggle' => false,
  'pinit_location_horizontal' => 'center',
  'pinit_location_vertical' => 'top',
  'pinit_min_width' => '200',
  'pinit_min_height' => '200',
  'pinit_image_source' => 'image',
  'pinit_image_description' => 'alt_text',
  'utm_on_pins' => false,
  'pin_browser_extension_location' => 'hidden',
  'float_mobile' => 'bottom',
  'force_new_shares' => false,
  'cache_method' => 'advanced',
  'order_of_icons' =>
  array (
    'twitter' => 'Twitter',
    'linkedIn' => 'LinkedIn',
    'pinterest' => 'Pinterest',
    'facebook' => 'Facebook',
    'google_plus' => 'Google Plus',
  ),
  'last_migrated' => '3.0.5',
)

We can grab these items from the serialized array with the pluck command.

wp option pluck social_warfare_settings order_of_icons

Output, notice the array!

array (
  'twitter' => 'Twitter',
  'linkedIn' => 'LinkedIn',
  'pinterest' => 'Pinterest',
  'facebook' => 'Facebook',
  'google_plus' => 'Google Plus',
)

You can also grab nested keys which are key names within a parent key name. These are also shown as arrays inside of the parent array when viewing without the json format.

wp option pluck social_warfare_settings order_of_icons twitter

And we get just that nested item 2 levels deep

Twitter

Updating/changing the serialized options can be done with the patch command, here we are updating the force_new_shares value to true

wp option patch update social_warfare_settings force_new_shares true

Similarly you can do this for nested options as well, here we are updating the Twitter value to Twitters for the twitter key-name

wp option patch update social_warfare_settings order_of_icons twitter Twitters

Now let’s explore how we can combine these techniques with some Linux bash scripting to batch update serialized options.

Using WP-CLI and bash to Batch Update Serialized wp_options

We are going to batch update a bunch of Social Warfare settings because it lets you choose the location for each post type. If you have 50 post types you don’t want to update them all manually!
WP-CLI has a very useful --format flag to output in yaml which can make the settings easier to parse and manipulate in bulk using bash scripting.

wp option get social_warfare_settings --format=yaml

This is the partial output of all of the Social Warfare serialized options. I only want the options between last_migrated and float_before_content

pinterest_fallback: all
pinit_toggle: false
pinit_location_horizontal: center
pinit_location_vertical: top
pinit_min_width: "200"
pinit_min_height: "200"
pinit_image_source: image
pinit_image_description: alt_text
utm_on_pins: false
pin_browser_extension_location: hidden
float_mobile: bottom
force_new_shares: false
cache_method: advanced
order_of_icons:
  facebook: facebook
  twitter: twitter
last_migrated: 3.0.5
location_event_forms: none
float_location_event_forms: 'off'
location_evo-tix: none
float_location_evo-tix: 'off'
location_instagram: none
float_location_instagram: 'off'
location_ajde_events: none
float_location_ajde_events: 'off'
location_product: none
float_location_product: 'off'
location_sfwd-courses: none
float_location_sfwd-courses: 'off'
location_sfwd-lessons: none
float_location_sfwd-lessons: 'off'
location_sfwd-topic: none
float_location_sfwd-topic: 'off'
location_sfwd-quiz: none
float_location_sfwd-quiz: 'off'
location_sfwd-certificates: none
float_location_sfwd-certificates: 'off'
location_sfwd-transactions: none
float_location_sfwd-transactions: 'off'
location_project: none
float_location_project: 'off'
location_gb_xapi_content: none
float_location_gb_xapi_content: 'off'
location_mailmunch_page: none
float_location_mailmunch_page: 'off'
location_vtprd-rule: none
float_location_vtprd-rule: 'off'
location_sfwd-essays: none
float_location_sfwd-essays: 'off'
location_sfwd-assignment: none
float_location_sfwd-assignment: 'off'
location_news_post: none
float_location_news_post: 'off'
location_ignitewoo_event: none
float_location_ignitewoo_event: 'off'
location_event_organizer: none
float_location_event_organizer: 'off'
location_event_venue: none
float_location_event_venue: 'off'
location_event_sponsor: none
float_location_event_sponsor: 'off'
location_event_speaker: none
float_location_event_speaker: 'off'
float_before_content: 'off'
pinterest_id: none
facebook_publisher_url: none
facebook_app_id: none
pro_license_key: ""
pro_license_key_timestamp: 1533584336

I want to chop these so I am using awk to split the fields by : and only print the first one since that is the nested option_name‘s key (the second field is the value for the key referenced in the first field).

Using sed we can chop all lines starting from 0 up to and including the line containing last_migrated

Since I do not want the options float_before_content and the ones after, I can delete that and those listed afterwards until the end with $d

wp option get social_warfare_settings --format=yaml | awk -F ':' '{print $1}' | sed '0,/last_migrated/d' | sed '/float_before_content/,$d'

Then i get

location_event_forms
float_location_event_forms
location_evo-tix
float_location_evo-tix
location_instagram
float_location_instagram
location_ajde_events
float_location_ajde_events
location_product
float_location_product
location_sfwd-courses
float_location_sfwd-courses
location_sfwd-lessons
float_location_sfwd-lessons
location_sfwd-topic
float_location_sfwd-topic
location_sfwd-quiz
float_location_sfwd-quiz
location_sfwd-certificates
float_location_sfwd-certificates
location_sfwd-transactions
float_location_sfwd-transactions
location_project
float_location_project
location_gb_xapi_content
float_location_gb_xapi_content
location_mailmunch_page
float_location_mailmunch_page
location_vtprd-rule
float_location_vtprd-rule
location_sfwd-essays
float_location_sfwd-essays
location_sfwd-assignment
float_location_sfwd-assignment
location_news_post
float_location_news_post
location_ignitewoo_event
float_location_ignitewoo_event
location_event_organizer
float_location_event_organizer
location_event_venue
float_location_event_venue
location_event_sponsor
float_location_event_sponsor
location_event_speaker
float_location_event_speaker

We can also use another sed command from this StackOverflow answer

wp option get social_warfare_settings --format=yaml | awk -F ':' '{print $1}' | sed '/^last_migrated$/,/^float_before_content$/{//!b};d'

Here is the option command pluck again for reference

wp option pluck <key> <key-path>

Using  social_warfare_settings as the <key>, location_event_forms as the <key-path> the command looks like this

wp option pluck social_warfare_settings location_event_forms

Output

both

We want it to be none so let’s do that with the patch command as shown earlier.

wp option patch update <key> <key-path> <value>

Using social_warfare_settings as the <key>, location_event_forms as the <key-path> and none as the <value> the command looks like this

wp option patch update social_warfare_settings location_event_forms none

Output

Success: Updated 'social_warfare_settings' option.

Let’s do the other one for the float location

wp option pluck social_warfare_settings float_location_event_forms

Output

on

We want it to be off so this patch command will take care of that.

wp option patch update social_warfare_settings float_location_event_forms off

Now let’s put it all together in a loop.

Note: the sed command is different because I needed to use double quotes "" for the variable to expand, as such I had to escape the $ with \

If you prefer to use the simpler sed command you can hardcode the variables instead so you don’t have to worry about messing around with the single and double quotes.

WPPATH=""
WPFLAGS="--path=${WPPATH} --skip-plugins --skip-themes --allow-root"

OPTIONNAME="social_warfare_settings"
FIRSTSTRING="last_migrated"
LASTSTRING="float_before_content"
#unique string the separates the option value pairs
DISTINCT="float"

#create array, grab all the settings, delete everything up to and including FIRSTRING, delete everything after and including last_string, then show only the distinct values
OPTIONLISTA=($(wp option get ${OPTIONNAME} --format=yaml | awk -F ':' '{print $1}' | sed "0,/${FIRSTSTRING}/d" | sed "/${LASTSTRING}/,\$d" | grep -v ${DISTINCT}))
OPTIONLISTB=($(wp option get ${OPTIONNAME} --format=yaml | awk -F ':' '{print $1}' | sed "0,/${FIRSTSTRING}/d" | sed "/${LASTSTRING}/,\$d" | grep ${DISTINCT}))

OPTIONAVALUE="none"
OPTIONBVALUE="off"

# Option A logic
for OPTIONA in ${OPTIONLISTA[@]};
do
    echo -e "Updating ${OPTIONA}"
    wp option patch update ${OPTIONNAME} ${OPTIONA} ${OPTIONAVALUE} ${WPFLAGS}
done

# Option B logic
for OPTIONB in ${OPTIONLISTB[@]};
do
    echo -e "Updating ${OPTIONB}"
    wp option patch update ${OPTIONNAME} ${OPTIONB} ${OPTIONBVALUE} ${WPFLAGS}
done

I hope this inspires you to automate updating these otherwise monotonous tasks!

Sources

Select lines between two patterns
Ignore lines before match in bash
Delete lines starting from matching line
Resolving sed unexpected character errors