Update posts after altering checkbox option

• waffl

• January 15, 2019 at 11:43 pm

I needed to change a checkbox option in a field that is used across a large number of posts. As an example to illustrate, let’s say I have a number of shirts with selectable colours (I know this could be a taxonomy, but this is just to illustrate my testing):

Checkbox Field: colour
– Black
– Yellow
– Orange

If I needed to update Black to Charcoal, I tried updating the field then running the mysql query:

UPDATE wp_postmeta
SET
	meta_value = REPLACE(meta_value,
		"Black",
		"Charcoal")
WHERE
	meta_key = "colour"

But this doesn’t seem to work, all checkboxes are unselected on the post now.

What is also confusing is I’ve tried testing with a new post only selecting that one field which results in an identical value in the DB like:

a:1:{i:0;s:8:"Charcoal";}

And yet a post I had updated via mysql with the exact same:

a:1:{i:0;s:8:"Charcoal";}

Does not have the box checked in the editor? Am I missing another table where this data is actually recorded?

I’ve looked through the forums and found the following relevant threads but no relevant solution:

https://support.advancedcustomfields.com/forums/topic/changing-checkbox-value-breaks-tab-functionality/
https://support.advancedcustomfields.com/forums/topic/edit-checkbox-selection-update_field/

• John Huebner

• January 16, 2019 at 1:23 am

You have 2 problems.

1) You’re using a checkbox field. Checkboxes are array fields and the values stored in the database are serialized arrays. You can’t update the values in these fields with a simple query. In fact, I don’t thing that any query directly on the DB will work. I would suggest trying this tool https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

2) You also need to update the field definition. Setting a value to a value that does not exist in the fields “choices” will cause the field to show no value the next time the page is loaded for edit. The field definition is stored as a serialized array in the “post_content” column of the “acf-field” post. If you use the tool I mentioned above, it will replace these as well.

• waffl

• January 16, 2019 at 8:25 am

Hi John,
Thanks very much for your reponse.

Regarding #2, I updated the field definition by editing the field, that should be okay correct? Or does it need to be updated elsewhere?

Regarding #1, the serialized string is identical in both cases where I manually replaced via mysql, and in a post that was saved directly.

If you simply run var_dump(unserialize('a:1:{i:0;s:8:"Charcoal";}')); you get the expected output:

array(1) {
  [0]=>
  string(8) "Charcoal"
}

So I’m just confused why updating the post_meta to something that’s identical as editing an item and saving it would produce different results unless I’m missing where this link is actually made in the db. I dumped the entire db, and searching for the term I only see it in post_meta so I can’t imagine it lingers elsewhere?

• waffl

• January 16, 2019 at 8:51 am

Okay, I believe I solved it.

The stupid apple autocapitalize caused the i to turn into capital I when I wrote my mysql query, and thus the serialized string that had been replaced in the DB was actually:

a:1:{I:0;s:8:”Charcoal”;}

Unbelievable, it’s actually insane that sequelpro allows autocapitalization/autosuggestion in a query anywhere in its interface.

So in case anyone does need to do something like this, I believe the following process does indeed work:

1. Alter the choice as you normally would by editing the custom field
2. Perform the mysql query with the length of the strings for the serialization in mind in the s:X: part of the query

UPDATE wp_postmeta
SET
	meta_value = REPLACE(meta_value,
		's:5:"Black"',
		's:8:"Charcoal"')
WHERE
	meta_key = 'colour'

• John Huebner

• January 17, 2019 at 12:14 am

:), gotta love it when the computer thinks it’s smarter than you are. Yeah, that capital would do it. Serialized data is pretty unforgiving. Any tiny error and it returns null.

• teleworm1337

• January 18, 2019 at 8:01 am

I needed to change a checkbox option in a field that is used across a large number of posts. https://downloadluckypatcher.co