Lea Hayes Lea Hayes - 5 years ago 296
SQL Question

MySQL SELECT Tree Parent IDs

How can I sort the records of a SELECT statement so that they represent a valid tree?

All of my attempts show sub-nodes nested under wrong parent nodes. What is the most reliable way to achieve this ordering?

Data

ID Parent ID Title
--------------------------------------------
0 NULL Root
1 0 Node A
2 0 Node B
3 1 Sub-Node C
4 1 Sub-Node D
5 3 Sub-Node E


Output

ID Parent ID Title
--------------------------------------------
0 NULL Root
1 0 Node A
3 1 Sub-Node C
5 3 Sub-Node E
4 1 Sub-Node D
2 0 Node B


Data Visualisation

Root
Node A
Sub-Node C
Sub-Node E
Sub-Node D
Node B

Answer Source

Following the advice of @Blindy I have implemented this sort with PHP. Here are the two functions that seem to solve this issue relatively easily.

protected function _sort_helper(&$input, &$output, $parent_id) {
    foreach ($input as $key => $item)
        if ($item->parent_id == $parent_id) {
            $output[] = $item;
            unset($input[$key]);

            // Sort nested!!
            $this->_sort_helper(&$input, &$output, $item->id);
        }
}

protected function sort_items_into_tree($items) {
    $tree = array();
    $this->_sort_helper(&$items, &$tree, null);
    return $tree;
}

I would be interested to hear if there is a simpler approach, but this does seem to work.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download