Updating a list of values
Posted: Sat Apr 16, 2005 9:45 pm
I've got a form which has a series of checkboxes displayed. Each checkbox corresponds to an item listed elsewhere in the database. That part all works well though.
The issue is that when you get the values from the checkboxes (they are in an array) you get an array of the items which were checked, while the unchecked items are effectvely ignored.
So, if I had three checkboxes in a control array called item, and two of them were checked, I would get an array with two items, the two that were checked.
Saving this is fine because I just add a row to a table for each checked item. What I am having trouble with, is the best way to handle updates.
I could just delete all items from the database checked list and then re-write the new entries, but somehow it seems like the lazy approach. I also worry about running out of range in my primary key (although they are int 11, so I guess I needn't qworry for a while).
If I don't take this approach, then I end up with a situation which is a bit more akward. To update, I would need to get my array of selected items and compare it against a list of items in the database. If the item exists already, then do nothing. If it doesnt, add it.
This leaves only the problem that any de-selected items are still present.
I could either then made a delete all query where ID <> any of the checked values, or I could do a reverse compare of the two lists, and anything that exists in database but not in the checked list would be deleted.
Clearly to my mind, any of the options apart from the first would use transactions. This is fine as I already have my class geared towards that.
Anyhow, my question is, what is the best way to do it in regards to database time, and just from a 'good practice' standpoint?
Cheers
The issue is that when you get the values from the checkboxes (they are in an array) you get an array of the items which were checked, while the unchecked items are effectvely ignored.
So, if I had three checkboxes in a control array called item, and two of them were checked, I would get an array with two items, the two that were checked.
Saving this is fine because I just add a row to a table for each checked item. What I am having trouble with, is the best way to handle updates.
I could just delete all items from the database checked list and then re-write the new entries, but somehow it seems like the lazy approach. I also worry about running out of range in my primary key (although they are int 11, so I guess I needn't qworry for a while).
If I don't take this approach, then I end up with a situation which is a bit more akward. To update, I would need to get my array of selected items and compare it against a list of items in the database. If the item exists already, then do nothing. If it doesnt, add it.
This leaves only the problem that any de-selected items are still present.
I could either then made a delete all query where ID <> any of the checked values, or I could do a reverse compare of the two lists, and anything that exists in database but not in the checked list would be deleted.
Clearly to my mind, any of the options apart from the first would use transactions. This is fine as I already have my class geared towards that.
Anyhow, my question is, what is the best way to do it in regards to database time, and just from a 'good practice' standpoint?
Cheers