I have a many-to-many relationship between organizations and categories.
I am using three database tables: organizations (id, …), categories (id, cat_name), and organizations_categories(id, org_id, cat_id).
The admin needs to be able to edit the categories that are assigned to an organization.
Would the best way be to have the admin choose the entire (edited) list from the dropdown (multiple select), and when that happens have the old list items deleted from the organizations_categories table and the new items inserted (ie two steps)?
Or should the items be dealt with one at a time (ie delete, keep or add)?
I had something similar, a user account would have a list of customers that it could “see” and display sales details for. I used a javascript thing that basically displayed two list boxes - one list contained the selected customers, the other contained the unselected ones, and you’d have arrow buttons to move one or all customers from one box to the other. On submit the two lists would be provided and the code could then update the link.
It was quite a nice visual way of doing it, wouldn’t suit something with hundreds of categories but then neither would a drop-down list IMO.
Hmm, that’s an interesting possibility. So once the two lists are set, then the old set of categories for that organization would be deleted from the database, and the new set would be inserted?
It’s not a long list of categories, maybe 10 or 12, so that could work nicely.
Yes, once you get the submit it contains the two lists and you either remove them all and re-add those in the list, or do some comparing or whatever to see what has been added and what has been removed. Probably a fancy jquery version around now, though that’s not always better as it removes the ‘oh, I didn’t mean it’ option.
For what it’s worth, that’s the way I usually do it… simpler than working out which ones need to be deleted and which need adding. I’d do it as part of a DB transaction though, in case there’s an error midway through and you’re left with no categories for that organization. As an aside, you don’t need a separate ID column in your organizations_categories table - you can use org_id and cat_id as a compound primary key (this prevents you from inserting duplicates too).