PHP updates MYSQL's many-to-many relationship

Question

I have a many-to-many relationship implemented with an association table in MySQL. I have a table for children and a table for parents. A child can have several parents stored in the parent_child_link association table with their identifiers.

Children can be updated through an HTML form, parents are in HTML-select. Now I need to update the database record, but my solution is not very efficient. Here, in the pseudo code, what am I doing:

• Update child information, where child_id = x
• Delete all current associations in parent_child_link, where child_id = x
• Insert new associations

This solution works fine, but when the parents were not changed, for example. only the child’s name was changed, then 2 unnecessary queries are executed. How can I avoid unnecessary queries? Is there a way to check if parents have changed in multi-select?

Of course, I could just ignore all these chores, because it already works, but I really like to keep the work as efficient as possible.

Answer 1

Try to enable it in the database, not at the application level, using ON UPDATE CASCADE and ON DELETE CASCADE in the definition of the child table.

A slightly revised example forms a MySQL site:

 CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB; 

Check out the docs here: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

EDIT: for your many-to-many relationship, you can use something like:

 CREATE TABLE parent_child_link ( parent_id INT NOT NULL, child_id INT NOT NULL, PRIMARY KEY(parent_id, child_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (child_id) REFERENCES child(id) ON DELETE CASCADE ON UPDATE CASCADE ); 

Hope this helps.

Answer 2

I have the same question, and I understood my decision when I read.

When I am ready to process the submitted entries, I will first make a request to get the current associations and call this $ original_list array. 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 find out 1) which elements to remove (no longer exists) and 2) which elements to add (new associations). Elements in both lists are not affected.

 $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 } 

I would like to know if others consider this the right decision.

Answer 3

Your decision is in order.
In your case, you can “optimize” the process by making a request to retrieve the parents and checking the data with multiple choices if any changes occur.
Then you execute only two deletion and insertion requests, if necessary. Another is that when you actually changed your parents, then there will be 3 queries instead of 2.
Therefore, you should ask you if you are going to change your parents very often. In this case, you must adhere to your initial decision in order to avoid an additional selection request.
If you think that parents will not be updated very often, then you can go with the above solution. When you update only information about a child, only one request is executed. When you also update your parents, 3 queries are executed.
When you go with the second solution, delete and insertion requests can be optimized to only do what is required (only delete parents that are no longer its parents, and insert only new parent links). PHP array functions may be useful for this.

Answer 4

If you want to keep your current way of doing this, but only optimize, you can wrap the queries in IF statements.

Like:

if ( isset ( $parent_name_change )){ // run query }