PHP更新MYSQL多对多关系

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.

我有一个多对多关系，用MySQL中的关联表实现。我有一张儿童桌和一张家长桌。一个孩子可以有多个父母，保存在parent_child_link关联表中，与他们的ID一起。

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:

儿童可以通过HTML表单进行更新，家长可以在HTML多选择。现在我需要更新数据库中的记录，但是我的解决方案不是很有效。伪代码是这样的:

Update the child information where child_id=x
在child_id=x处更新子信息
Delete all the current associations in parent_child_link where child_id=x
删除child_id=x的parent_child_link中的所有当前关联。
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.

当然，我可以忽略所有这些麻烦，因为它已经起作用了，但我真的喜欢让事情尽可能的有效率。

4 个解决方案

#1

Try solving it in the database, not in the application layer by using ON UPDATE CASCADE and ON DELETE CASCADE in the definition of the child table.

尝试在数据库中解决它，而不是在应用程序层中，在子表的定义中使用ON UPDATE CASCADE和ON DELETE CASCADE。

A slightly revised example form the MySQL site:

一个稍作修改的示例来自MySQL站点:

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

查看这里的文档:http://dev.mysql.com/doc/refman/5.5/en/innodb- keys -constraints.html。

EDIT: For your many-to-many relation 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.

希望这个有帮助。

#2

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。提交的列表我将调用$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.

然后我只需要计算出1)要删除哪些项(不再存在)和2)要添加哪些项(新的关联)。两个列表中的项目都不会被触摸。

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

我很想知道其他人是否认为这是一个有效的解决方案。

#3

Your solution is fine.
In your case you could "optimize" the process by making a query to retrieve the parents and check with the multi-select data if any changes has occurred.
Then you only perform the two delete and insert queries if needed. The counterpart is that when you actually changed the parents, then there will be 3 queries instead of 2.
So you should ask you if the you are about to modify the parents very often. In this case you should stick to your original solution to avoid an extra select query.
If you think the parents won't be updated very often, then you can go with the above solution. When you update the child info only, only one query is performed. When you also update the parents, 3 queries are performed.
When you go with the second solution, the delete and insert queries can be optimized too to only perform what is required (only delete the parents that are not his parents anymore and only insert new parent links).
PHP array functions can be helpful for that.

你的解决方案很好。在您的情况下，您可以通过查询来检索父进程，并在发生任何更改时检查多选择数据，从而“优化”进程。然后只执行这两个删除和插入查询(如果需要的话)。对应的是，当您实际更改父类时，将有3个查询而不是2个查询。所以你应该问你是否会经常修改父母。在这种情况下，您应该坚持原来的解决方案，以避免额外的select查询。如果您认为父母不会经常更新，那么您可以使用上面的解决方案。当您只更新子信息时，只执行一个查询。当您同时更新父类时，将执行3个查询。当您使用第二个解决方案时，删除和插入查询也可以进行优化，以只执行所需的操作(只删除不再是父级的父级，只插入新的父级链接)。PHP数组函数可以对此有所帮助。

#4

If you want to keep you'r current way of doing it, but just optimizing, you could wrap the queries in IF statements.

如果您想保持当前的方式，但只是优化，您可以将查询封装在If语句中。

Like:

如:

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

if (isset ($parent_name_change)){// /运行查询}

#1