Updating Dynamic Form Fields in MySQL Database

Question

I have created a form that allows users to dynamically add as many fields as needed. Each of these is an inventory item that is assigned to a device. I have 3 tables, the Devices, the Inventory Items, and the third to associate the Device with an inventory item.

When the user saves the form, it is possible that they may have removed existing entries, added new ones or left some unchanged. The best way I can think to handle this is to empty all Table rows that associate that device with an item and rewrite them based on the input from the form.

Would there be a more efficient way of doing this? I can't imagine that dumping all rows and then rewriting them is the most viable option.

Answer 1

Dumping the data is cleaner and faster. Then add in the new data when they submit the form. If you're worried about something happening between deleting and adding as @djot suggested, then use a transaction.

http://dev.mysql.com/doc/refman/5.0/en/commit.html

I have done this many times with no issues. It takes all the logic of checking each entry against the database out of play. If there is no reason to check, then just delete, way more efficient.

Answer 2

The way I did this was storing the IDs of the rows in hidden fields.
In your case, you can store the IDs of the third table's rows in hidden fields. And on post back, delete all the rows whose IDs are not present(deleted by user), and update those which are present, using the IDs.