Updating dynamic form fields in a MySQL database

Question

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

When the user saves the form, it is possible that they deleted existing records, added new ones or left unchanged. The best way I can think of is to clear all the rows of the table that associate this device with the element and rewrite them based on input from the form.

Would there be a more efficient way to do this? I cannot imagine that dropping all the lines and then rewriting them is the most viable option.

Answer 1

Dumping data becomes cleaner and faster. Then add new data when they submit the form. If you are worried about what happens between deletion and addition as suggested by @djot, use a transaction.

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

I have done this many times without problems. It takes all the logic of checking each record against the database outside the game. If there is no reason to check, just remove the more efficient one.

Answer 2

The way I did this is to store row identifiers in hidden fields.
In your case, you can store the identifiers of the third rows of the table in hidden fields. And in response, delete all rows whose identifiers are missing (deleted by the user), and update those that are present using identifiers.