Returning a tree from mysql DB

Peter B

I have a large categories list. There are sub categories and sub-sub categories?

Is there a simple was to return all the categories in a tree like structure in alhabetical order?

My categories table is something like this

category_id
category_name
parent_category_id (The parent node of this category)

If there is no parent node the category's parent is the root.

I would like my results to show something like this

-Cars
     -Ford
          -Fiesta 
          -Transit
     -Peugeot
          -206
          -306
          -406
     -Porche
          -Boxter
          -Carrera

Is there a simple SQL call to capture all the nodes and return them in this tree-like fashion or will I have to complete multiple queries for the sub-levels?

I am programming in PHP.

Thanks in advance.

koloughlin

Oracle supports hierarchical self-joins using the STARTS WITH and CONNECTS BY constructs. See here http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/gennick_connectby.html.

I highly suspect that MySQL does not have a similar construct. A quick google on this throws out http://dev.mysql.com/tech-resources/articles/hierarchical-data.html.

If you have a limited number of possible parent-child relationships, in other words if you can never have this go more than n levels deep you can flatten it with left outer self-joins, e.g.

select a.name, b.name, c.name, ... , n.name 
from myTable a 
left outer join myTable b 
on a.id = b.parentId 
left outer join myTable c 
on b.id = c.parentId 
.
.
.
left outer join myTable n 
on m.id = n.parentID
where a.parentId is null