PHP recursive function to delete all child nodes causes stackoverflow

Question

My MySQL looks like this: (the name of the table is category)

'id', 'content', 'parent'

where:

• id = the id of the category
• content = some-text-we-dont-care-about
• parent = the id of the parent category

this is what I'm trying right now:

function remrecurs($id) {
    $qlist=mysql_query("SELECT * FROM category WHERE parent='$id'");
    if (mysql_num_rows($qlist)>0) {
         while($curitem=mysql_fetch_array($qlist)) {
              remrecurs($curitem['parent']);
         }
    }
    mysql_query("DELETE FROM category WHERE id='$id'");
}

Which for some reason doesnt work and crashes .. Any idea what I'm doing wrong ?

Answer 1

The problem is in the recursive call:

remrecurs($curitem['parent']);

it should be:

remrecurs($curitem['id']);

Why?

Your objective is to delete the row with given id. First you check to see if it has any children. If yes you need to call the recursive delete on each of the children not on the parent again. You are calling the function recursively on the parent again..this leads to infinite recursive calls, you thrash the stack and crash.

Answer 2

Alternatively, you could let the database handle this. In MySQL, an InnoDB ON DELETE CASCADE will do this automatically.

CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES category (id) ON DELETE CASCADE
) ENGINE=InnoDB

Root nodes should have NULL as parent (not 0 as some people seem to employ on Adjancency List tables).