Efficient way to manage the relations between two database tables on the front-end using checkboxes ?

Question

Quick background info: of the three database tables that are relevant for this question, one holds devices info (d.id, d.name), the second one holds accessories for the devices in the first table (a.id, a.name) and the third table handles relations between the first two (d.id, a.id).

By selecting one of the available devices from a drop-down box, a list of all accessories is being displayed, each with a checkbox which is either checked or not, based on the data in the relations table.

And this brings us to my problem: I am not sure what is the most efficient way of checking which checkboxes were changed after the form has been submitted, as well as what the most efficient way of querying the database would be, since both INSERTs and DELETEs might be required for the same submitted form (i.e. a users un-checks a checked checkbox and checks an un-checked one -- whoa, that phrase should be a diction exercise -- before submitting).

By the way, I'm not hell bent on using checkboxes, but I figured it would be a clean way to handle this. Any suggestions and proof-of-concept code would be more than appreciated !

Thank you all in advance !

Answer 1

Look into REPLACE INTO on your bridge/join table between products and accessories. It's an under used gem in MySQL.

Otherwise, as you said, you can DELETE then INSERT everything. But if you have any additional foreign keys, this could get messy. However, in the end it seems to be the most common way, for better or worse.

If you did have foreign keys, you could pass an additional field containing the ids of previously checked accessories and handle those accordingly by comparing them to the submitted form data. I'd only suggest doing this if you have other transactional data in your bridge table that is important.

Answer 2

If you have a "Submit" button below the checkboxes, just send the form to the server with POST or GET, whatever you require.

Use AJAX and onClick with the checkboxes if you want to change the database on the fly. (When a user unchecks, send changed checkbox value('false') to the PHP script, it will then make a DELETE query. If the value of checkbox is 'true', it will fire an INSERT query).

For AJAX sending you could use jQuery(would end up in less code)