Get rid of circular parent/child in MYSQL

Question

I have a table like this:

 Attribute  |  Type   | Modifier
------------+---------+----------
 id         | integer | not null
 title      | text    | not null
 parent     | integer | 

The parent field is a foreign key referencing the same table.

How can I ensure that no loops (circular parent/child references) are ever inserted? For example:

 id         | title   | parent
------------+---------+----------
 1          | A       | 3 or 2
 2          | B       | 1
 3          | C       | 2

I'm using PHP and MySQL.

Answer 1

First, assume that the table has no loops initially. If it does, then you'll need to fix any loops manually. Then,when you try to set the parent for a child, first fetch the child's potential parent. Then keeping fetching the parent's parent. If you reach a node that has no parent, then everything is ok, and you can set the child's parent. If you reach the child, then you know that a loop would be created if you set that 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
}