5

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.

Improve this question
1
  • Dumping all data seems to require less logic than other more straightforwarded solutions ... but if you do something wrong all data is lost. Commented Sep 9, 2013 at 13:16

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

can you please provide some source code for this update
0

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.

Improve this answer

2 Comments

Thank you. I will go ahead and just proceed with dumping the data and then rewriting it on form submit for now. Once I switch to MySQLi I may revisit the idea of using transactions for this.
@BrettPowell you can use transactions with the old MySQL api too. It makes handling errors a lot simpler (and correct).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.