MYSQL query to arrange list of categories hierarchy

Question

I have a database list which looks this:

ID      Name      Parent_ID
1       Cat 1     NULL
2       Cat 2     NULL
3       Cat 3     2
4       Cat 4     3
5       Cat 5     1
6       Cat 6     2

The out put that I am trying to get is all the categories arranged in order of hierarchy and sorted alphabetically. Like this:

Cat 1
    Cat 5
Cat 2
    Cat 3
        Cat 4
    Cat 6

I am really not sure how to quite get this result, this is what I have at the moment, but does not work:

 SELECT * from Categories AS parent 
 LEFT JOIN Categories AS child ON child.Parent_ID = parent.ID

Any help is appreciated.

Answer 1

I think, you need build categories tree in PHP. Try it (example):

$sql = 'SELECT * from Categories ORDER BY parent_id ASC'; 
$items = // fetch as array of assoc.arrays

// example
// $items = array(
//     array('id' => 3, 'parent_id' => 1 ,'name'=>'..'),
//     array('id' => 4, 'parent_id' => 3, ,'name'=>'..'),
//     array('id' => 7,  'parent_id' => 0, ,'name'=>'..'),
//);

$childs = array();
foreach($items as &$item) $childs[$item['parent_id']][] = &$item;
unset($item);

foreach($items as &$item) if (isset($childs[$item['id']]))
    $item['childs'] = $childs[$item['id']];
unset($item);

$tree = $childs[0];

echo '<pre>';
print_r($tree);
echo '</pre>';