Page 1 of 1

Recursive function FAIL.

Posted: Sun Jan 16, 2011 12:16 pm
by roninbv
Okay, so I am building a task management system, and I wanted to give the tasks an unlimited depth of subtasks, such that each task could have as many subtasks as necessary, and each of those subtasks could have subtasks, etc.

So I wrote a recursive function for retrieving said tasks from the database.

Code: Select all


private function GetTasks($userId, $parent = 0){
        global $database;
        $query = "CALL GetTasks($userId, $parent);";
        $result = mysql_query($query);
        $tasklist = array();
        if($result != false){
            while($row = mysql_fetch_object($result)){
                $task = new Task();
                $task->id = $row->id;
                $task->text = $row->text;
                $task->complete = $row->complete;
                $task->parent_id = $row->parent;
                $task->children = $this->GetTasks($userId, $task->id);
               array_push($tasklist,$task);
            }
        }

        return $tasklist;
    }

Here's the deal: it fails on the first recurse. Now if I call the stored proc with the params that are being passed in on the first recurse, I get the data i expect. I think the deal is that the database connection is not free.

What can I do to fix this?

Thanks,
-B

Re: Recursive function FAIL.

Posted: Sun Jan 16, 2011 12:52 pm
by sergio-pro
Hi

I can advise folowing solution:
First - do not call recursively loadChildren for every node, collect nodes IDs, so that to query the whole next level with one "parent_id IN (ids)" query.
Second - when iterating in the while loop - save all nodes into an array of type $allnodes = { nodeId => node }
In the next recursion, each found node you can add to its parent like this: $allnodes[ $currNode.parentId ]->children[] = $currNode;
And also add it to $allnodes, and generate new ids list for next recursion and so on ...

PS Forgot to mention - you can use "The Nested Set Model" described in this article: http://dev.mysql.com/tech-resources/art ... -data.html
Then you can query the whole subtree without any recursion.