A colleague helped another user with a similar issue (which relates to using checkboxes fields as Views filters, which isn't working when zero is saved to the database).
He produced the following script, which you can try on your own site.
1. Make a backup before running the script.
If you are not familiar with it, Types 3.1 added the ability to add custom code to your site without the need to edit your theme's functions.php etc, which you can read about here: https://toolset.com/documentation/adding-custom-code/
This feature lets you add a code snippet and run it once only ("Run now"), which is what you need here.
2. Add the code below to a custom code snippet on your site, first editing the variables at the top of the script for the post type the checkboxes field is assigned to, as well as the meta keys of the checkboxes custom fields (don't forget the 'wpcf-' prefix).
3. Run the code once
4. Confirm on some sample posts that the checkboxes options appear to be correctly set
5. Confirm that the report issue is no longer present
Here is the code
<?php
/**
* One time code to 'save nothing' for existing unchecked checkboxes options
* currently saved as zero
**/
function change_checkboxes_value_zero_to_nothing() {
/**
* This part has to be adapted to your specific case
* Please make sure to cahnge post type slug and field slugs as you need them
* $affected_post_type = The slug of the post type in question
* $affected_custom_fields_meta_keys = comma separated list of Custom Fields, which are Checkboxes, and belong to the above type, and save 0 when nothing is checked
*/
$affected_post_type = 'artist';
$affected_custom_fields_meta_keys = "'wpcf-instrument','wpcf-music-genres'"; //you may add more Checkboxes Fields belonging to the Post Type set above
/**
* This part has not to be touched.
* It gets all posts IDs of our type
*/
$affected_post_ids = get_posts(array(
'numberposts' => -1, // get all posts.
'post_type' => $affected_post_type,
'fields' => 'ids', // Only get post IDs
));
/**
* This part has not to be touched.
* It loops over each post, gets it's checked checkbox value for each custom field and updates the field by saving only the checked values, nothing if 0
*/
if ($affected_post_ids) {
global $wpdb;
$affected_post_ids = implode(',', $affected_post_ids);
$sql = "SELECT meta_key, meta_value, post_id FROM $wpdb->postmeta WHERE post_id IN ($affected_post_ids) and meta_key IN ($affected_custom_fields_meta_keys)";
$postmeta = $wpdb->get_results($sql, ARRAY_A);
if (!empty($postmeta)) {
foreach ($postmeta as $mKey => $mVal) {
$serialized_meta = unserialize($mVal['meta_value']);
$returned_options = [];
if (!is_array($serialized_meta)) continue;
foreach ($serialized_meta as $key => $value) {
if (is_array($value)) $returned_options[$key] = $value;
}
/**
* Here the actual update to the Database happens.
* If you first want to make sure the program gets the correct data, you can uncomment this var_dumps and comment the below update_post_meta()
*/
// var_dump($postmeta[$mKey]['post_id']);
// var_dump($postmeta[$mKey]['meta_key']);
// var_dump($returned_options);
update_post_meta($postmeta[$mKey]['post_id'], $postmeta[$mKey]['meta_key'], $returned_options);
}
}
}
}
change_checkboxes_value_zero_to_nothing();
Let me know how you get on. Don't forget that backup.
