[Resolved] Update custom checkbox field formerly using “save 0 to the database”

[Author]

Posts

[helenmaryC]

Given the problems which seem to arise using Views and Forms when a custom checkbox field is set to "save 0 to the database" I am planning to switch all of my checkboxes to "don't save anything to the database."

Trying to do this manually would be impractical, so I would like to do it programatically. This post (https://toolset.com/forums/topic/ajax-filter-not-working-properly/page/6/#post-620904) mentions appropriate code, but it is not provided. Can I get the code or at least the proper syntax for update_post_meta() so I can write something myself?

Thanks!

[Beda]

I wrote the reply in that post personally, hence I recall the code is provided, however, it might be you cannot see the link to it since I forgot to make it public.
I did so now. It's a link to a page template, please use the thread you mentioned as instruction about how to apply it.

Please do not forget to fully back up your website before you apply that script, and test your site thoroughly after, so to report any issues in case something goes wrong.

[helenmaryC]

Hi Beda -

Thank you for making the link public.

I thought it all made sense and I was following the instructions correctly, but this does not seem to update any records. I have a function on my site where I export the members cpt (the one I am testing on) to a csv file and the wpcf-exclude-from-directory field is still exporting "0" for unchecked records.

I decided to try setting up a test on my local machine to see if I could figure out what is happening. I set up the members cpt, all fields, including wpcf-exclude-from-directory set to "save 0 to database," and added 3 records, 2 with unchecked fields and 1 with the field checked. I then ran the code in debug mode and followed the process. Everything seems to work fine until I get to line 42. $serialized_meta is returning false for all values, so the continue statement is skipping lines 43-54. That doesn't seem right. I would expect that records where the value is 0 would need to be updated, right?

I must be missing something, but I am not sure where to look next. Any ideas would be appreciated!

Helenmary

[helenmaryC]

Good Morning!

While I am still fairly new to WordPress, I have a lot of experience with asp.net, so I tend look at things from that perspective. Since the code you supplied didn't seem to be working as expected, my asp.net experience led me to take a look at the actual structure of the data being saved in the postmeta table.

From what I am seeing, this is how I think checkbox field data is being saved. First of all, it does not appear to be serialized, at least not in the way I am understanding serialization. For each post_id, there is a collection of records in postmeta, each one containing a meta_key corresponding to the custom field created by Types. If the data was serialized, I would expect there to be one entry per post per custom field group (saved in the meta_key) and the data contained in an array in the meta_value field. What I think is being saved is as follows.

As and example, let's say I have a cpt with an associated custom field called 'checkbox' and an associated post with post_id = 10.

Case A - checkbox has "save nothing to the database" enabled
There are two possibilities for entries in postmeta, 1) post_id = 10, meta_key = 'wpcf-checkbox', and meta_value = 1 OR 2) No record for post_id = 10, meta_key = 'wpcf-checkbox'

Case B - checkbox has "save 0 to database" enabled
There seem to be 3 possibilities for entries in postmeta. 1) post_id = 10, meta_key = 'wpcf-checkbox', and meta_value = 1 OR 2) post_id = 10, meta_key = 'wpcf-checkbox', and meta_value = 0 OR 3) post_id = 10, meta_key = 'wpcf-checkbox', and meta_value = empty. This third possibility is what is causing problems and why we need to switch to Case A.

Since I am comfortable with working in php_admin, I think I can affect a fix as follows:

In WP Dashboard
1) Put site in maintenance mode
2) Backup database
3) For all checkbox fields, change them to "save nothing to database"
In php_admin
4) Run a series of sql queries (one for each checkbox field) as follow: DELETE FROM `wp_postmeta` WHERE `meta_key` = 'wpcf-checkbox-name' AND `meta_value` <> '1';
Via ftp
5) Update any code that uses a checkbox field
In WP Dashboard
6) Update any Toolset Views that use a checkbox field
7) Verify that everything is working
8) Take site out of maintenance mode

Does this all make sense, or am I missing something and this is going to completely trash my site?

Thanks for your help.

[Christian Cox]

For each post_id, there is a collection of records in postmeta, each one containing a meta_key corresponding to the custom field created by Types. If the data was serialized, I would expect there to be one entry per post per custom field group (saved in the meta_key) and the data contained in an array in the meta_value field.

This would be the case if the entire custom field group was serialized together, but that is not how it works in Toolset. Serialization occurs for a "group of checkboxes", but not for a "single checkbox" and not for an entire custom field group (which can include other fields like select, radio, text, etc). A group of checkboxes can have multiple selected values for the same field, hence the need for serialization. The terminology is a little confusing, and I think that is the main source of confusion here. Please refer to the screenshot.

I think your outlined process for updating seems accurate, though I would probably use the GUI to select and delete all the "0" entries for the corresponding meta key because I prefer the visual confirmation of the correct selection. Whatever method you prefer should be fine.

[helenmaryC]

Ah! Now I see! Yes, I am working with several single checkboxes, not groups of checkboxes. Using serialization as you have described it for groups makes perfect sense.

I will proceed as I described and close this ticket once I am sure everything is up and running as it should be.

Thanks for clarifying and checking my logic!

[Christian Cox]

Sounds good, I'll stand by for your update.

[helenmaryC]

OK, I got all of updates made and all of my queries are working, but my Views are not. I can select for "The field Deceased is a string that is equal to Constant 1, (i.e. checked values), but I can't seem to select for unchecked values. I have tried using both string and boolean and different from Constant 1. I've also tried equal to Constant with no value. When I am doing a wp_query I have to use 'compare' => 'NOT EXISTS' to select for unchecked values, but there doesn't seem to be an equivalent when setting up a View.

Any ideas?

[Shane]

Hello,

Christian is currently not available today but will be back tomorrow to continue with you.

Thanks,
Shane

[Christian Cox]

Yes that's correct, there is not a simple way to filter by an unchecked checkbox. You have a couple of options here:
1. Use a post ID exclusion filter
- Create a View of posts filtered by the checked checkbox. In the Loop, output the post IDs as a comma-separated list like 1,2,3,4 and remove the wrapping div from the output.
- Create another View of posts and add a post ID filter to exclude posts by ID, set by a shortcode attribute like "ids"
- Place View 1 inside the "ids" shortcode attribute of View 2. Now you have effectively filtered out all checked checkboxes, leaving only unchecked checkboxes in the results.

2. Use custom code to modify the query using "NOT EXISTS" as you mentioned. We have the wpv_filter_query API available for modifying the meta_query criteria programmatically. https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query

[helenmaryC]

The wpv_filter_query worked like a charm, once I remembered to add the priority to it. 😉

Thanks for your help.