Get rid of circular parent / child in MYSQL
I have a table like this:
Attribute | Type | Modifier
------------+---------+----------
id | integer | not null
title | text | not null
parent | integer |
Field parent
- is a foreign key referencing the same table.
How can I ensure that no loops (circular parent / child references) are inserted? For example:
id | title | parent
------------+---------+----------
1 | A | 3 or 2
2 | B | 1
3 | C | 2
I am using PHP and MySQL.
+3
source to share
1 answer
First, suppose the table initially has no cycles. If so, then you will need to fix any loops manually. Then, when you are trying to set a parent for a child, first select the child potential parent. Then keeping the parent parent's selection. If you reach a node that does not have a parent then everything is fine and you can set a child parent. If you get to the child, then you know that the cycle will be created if you set this parent.
function canSetParent($child, $parent)
{
$node = $parent;
while($node != null)
{
if($node->id == $child->id) return false; //Looped back around to the child
$node = $node->parent;
}
return true; //No loops found
}
0
source to share