MySQL的parent_ids_MySQL SELECT树的父ID

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

解决方案

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.