Compare $_POST array with database

Question

Ok, I'm pretty sure I'm not the first one to run into this, I just don't know the right words to search for a solution. Consider the following database tables (simplified for this question):

database tables http://www.nth-root.nl/public/images/wolf/cat_attr.png

As you can see in the picture, there is a category table that holds product categories and an attribute table that holds different attributes. For instance: Color (for clothing), Capacity (for mp3 players), etc.

The third table, in the center of the picture, links the attributes to the categories.

Now in the backend I have an 'Edit category' page with a form to edit (or add) a category. On the same page I want to display a list of dropdown lists in order to manage which attributes are linked to the current product category.

backend http://www.nth-root.nl/public/images/wolf-commerce/edit_category.png

It uses jquery to add / delete dropdowns.

The problem is: how do I manipulate the data in the category_attribute table, using the $_POST data of the form.

The lazy solution would be (and yes I've done this in the past):
1. Delete all current rows in category_attribute for the given category
2. Then insert all selected attributes to category_attribute

But this isn't very efficient, it deletes and re-adds all rows even if someone only changes the category's title.

So I'm looking for a way to do the right inserts, updates and deletions:
1. Delete the rows that don't appear in $_POST (the dropdown has been removed)
2. Update the rows that are changed (another option from the dropdown has been selected)
3. Insert rows that are new (a new dropdown has been added)

I'm just not sure how to do it or where to find a good example. It's probably easy to do with some ajax but I am looking for a solution that's purely php, comparing the $_POST values with the values in the database.

If someone knows a good article that explains the best (and most efficient) solution, please let me know, I'd be very grateful.

-edit-
I had included some pictures to clarify my question, but stack overflow refused to include them because my reputation is too low, so I hope my question is clear enough without the images.

-edit2- Now I have enough reputation, I've added the images.

Thanks in advance,

Nic

Answer 1

Insert and Update:
You can insert a hidden input with the ID records.
Those who are new will reset this ID, so you do the insert.
The ID's that have filled you make an update. If you only want to perform the update really changed the items you can create a hidden input "UPDATED" with value "0". Via javascript you can check if the combo has changed, so that input marked as "1".

Exclusion:
Likewise you can do to exclusion, creating a hidden input scoring records that should be deleted when the user clicks to remove.

Do not know if it's the appropriate way for you, or if you expect a more practical solution.