I’m about ready to have a stroke with my checkboxes!!
I have a member Interest form with 10 interests on it. On the backend I have a junction table that looks like this…
- id
- interest_id
- member_id
- checked_on
- unchecked_on
If checked_on exists, and unchecked_on is NULL then you have an actively checked checkbox. And over time there could be several records as a person checks, unchecks, rechecks, re-unchecks, and checks a checkbox - if you can follow that?!
Having this logging structure makes things tricker to code, but what seems to be doing me in is how HTML only sends over the checkboxes that are checked.
Why is this an issue?
Because if Suzy checked 3, 6, and 8 and that gets sent over in the $_POST array, I have no easy way of knowing if she also unchecked 1 or if it was never checked to begin with?!
If 1 was indeed checked - although I can’t tell this from the $_POST - then in the database I need to run an UPDATE and add an unchecked_on value.
If 1 was never checked in the first place - and again I can’t tell this by an absence of 1 in the $_POST - then I don’t have to touch my table.
See?
It is the missing values in the $_POST for checkboxes that make things complicated, because I don’t want conflicting values in my database.
Not sure if any of this is making sense, but I could use some advice or direction…
Do you have a “snapshot” status of all the checkboxes that you can use to compare the values after the form has been submitted? So in your example can you retrieve all the interest statuses from somewhere, and then see that if item 3 is not in the POST array but was checked in your snapshot, then it must have been unticked.
I’ve seen a workaround somewhere that involves a hidden form field of the same name as the checkbox, I don’t know whether that would be browser-specific. It appears to involve something like:
But that is silly, you can do that by querying the table again before you process the form. And using that technique you don’t have to worry about someone using Dev Tools to alter the data.
Do you actually need to log the history of when the checkboxes were selected? If you do, you could, y’know, create a table for logging the selections (or a log file or indeed any other of the myriad logging solutions out there)
Which would leave your join table cleaner for simply maintaining the current state of user selections. (i.e. drop the data fields in the join table, or at least the unchecked_on one if you’re interested in keeping that little piece of data handy - depends on use case
Your process would then be:
When you build the form, you know the current selections made by Suzy.
When you receive the form, you wipe out all of Suzy’s previous selections and replace them with a new INSERT (or however you’re achieving adding rows - ORM?) - As long as this section stage is wrapped in a database transaction, the integrity of your data should remain true.
If you are querying the table to add “sticky” checked=“checked”, (like in your other post using $Userinterests array) you should already have the array of past data.
You then compare POST interests to $Userinterests array in both directions.
IF $Userinterests not in array POST interests delete old record.
IF POST interests not in array $Userinterests insert new record.
NOTE: But I have also done it like megazoid suggested many times. Delete all records for this user, add new ones. Works fine.
No. It’s still easy.
You just need another table, let’s call it Log, with 3 fields:
member_id
interest_id
revision_id
Each time when member updates his interests list you do steps i described in previous post BUT also store new values in a Log table incrementing revision_id. So the first table will contain only current list for each member, while Log table will contain the history. And you can retrieve it easily by revision_id. It will be even possible to make diffs between revisions.
One thing you need to keep in mid when dealing with checkboxes is that when a checkbox isn’t check, nothing will be sent to the server (not even false) for that field
Now Suzy wants to update her list. We need data to populate the form. So we just select all rows with member_id=53 and MAX(revision_id). Then Suzy unchecks interests 1 and 2, but checks 8 instead. Interest 5 wasn’t modified. We insert these new values with incremented revision_id:
Now we can easily select two sets of rows (current revision and previous) and make a simple comparison with array_diff() to see which interests was unchecked last time and which wasn’t modified and/or still checked.
Yeah, I saw your earlier response and you have an interesting approach.
The only thing I see with your latest example is that you give “5” a revision 2 which technically isn’t correct since it remained unchanged - by your own words.
I have been hacking away at this all afternoon in TextEdit and think I have a solution - albeit a real PITA.
Do you actually only need to track the delta? in @megazoid 's example, you could actually calculate the delta after the fact so to speak [he did mention that you’d be able to diff between one group of choices and another)- @megazoid 's example tracks the history of the selections (thumbs up from me on that one). You need a diff, you can calculate it.
The only thing that I’d change in the example given is that, rather than going for an incrementing integer revision id, I’d use microtime(true) based on UTC
This would
a) Give you a timestamp for the revision / change set
b) Still be sortable in an order that makes historical sense (e.g. latest first)
c) Still as reliable as an integer id (unless your users manage to make multiple form posts within the same 1000th of a second)
d) You could still map revisions to a nominal integer value in code if you really wanted to. i.e. oldest set gets #1, next gets #2 etc
e) remove the need for an additional query to find the previous highest revision number to increment
a) The revision 2 in megazoid’s example is a way to attach a label to the ‘set’ of selections that Suzy made. This way, you can effectively recreate what was Suzy’s $_POST array at any point in time. It’s a way of saying “At this point in time, this is what Suzy selected”. Because you would also have a full set of Suzy’s previous choices, you could work out what changed by using code ( array_diff() ). This is the better option (less work, more storage)
b) …because if you only record the things that change each time, in order to find out what Suzy had selected at a particular point in time, you would have to start right at the beginning and then reapply every change that you recorded until you reach the point in question. (more work, less storage)
Option a is certainly the better bet unless you have a specific need to only record the changes each time (and calculating those changes on the fly isn’t appropriate)
