PHP update MYSQL many-to-many relation

Question

I have a many-to-many relation, implemented with an association table in MySQL. I have a table for children and a table for parents. A child can have multiple parents, saved in the parent_child_link association table with their ID's.

Children can be updated through a HTML form, the parents are in a HTML multi-select. Now I need to update the record in the database, but my solution is not very efficient. Here's in pseudocode what I do:

1. Update the child information where child_id=x
2. Delete all the current associations in parent_child_link where child_id=x
3. Insert the new associations

This solution works great, but when the the parents weren't changed, e.g. only the name of the child was changed, then there are 2 unnecessary queries executed. How can I avoid those unnecessary queries? Is there some way to check if the parents in the multi-select didn't change?

Ofcourse I could just ignore all this hassle, because it already works, but I really like to keep things as efficient as possible.

Answer 1

I have the same question and figured out my solution as I was reading.

When I am ready to process the submitted entries, I first do a query to get the current associations and call that array $original_list. The submitted list I will call $submitted_list.

$original_list = array(3,5,7);
$submitted_list = array(1,2,3);

Then I just need to figure out 1) which items to delete (no longer exist) and 2) which items to add (new associations). Items in both lists do not get touched.

$delete_list = array_diff($original_list, $submitted_list);
$insert_list = array_diff($submitted_list, $original_list);

foreach($delete_list as $item) {
    // delete $item from DB
}

foreach($insert_list as $item) {
    // insert item in db
}

Would love to know if others feel this a valid solution.