Page 1 of 1

Format results as a tree

Posted: Wed May 23, 2007 11:31 am
by acidHL
Hi all I have a table of the following schema:

Code: Select all

CREATE TABLE `categories` (
  `CatID` int(11) NOT NULL auto_increment,
  `CatName` varchar(255) NOT NULL,
  `CatParent` int(11) default NULL,
  PRIMARY KEY  (`CatID`)
)

Code: Select all

mysql> SELECT * FROM categories;
+-------+-------------------------+-----------+
| CatID | CatName                 | CatParent |
+-------+-------------------------+-----------+
|     1 | Parent Cat One          |      NULL |
|     2 | Parent Cat Two          |      NULL |
|     3 | Sub Category            |         1 |
|     4 | Sub Category 2          |         2 |
|     5 | Sub Sub Category        |         3 |
|     6 | Small Electrodes        |         1 |
|     7 | Big Electrodes          |         1 |
|     8 | Kinda Big ish           |         7 |
|     9 | Getting Bigger          |         7 |
|    10 | Quite Large             |         7 |
|    11 | Massive!                |         7 |
|    12 | Did You Bring a Flatbed |         7 |
|    13 | Another Sub Category    |         1 |
+-------+-------------------------+-----------+
13 rows in set (0.00 sec)

The CatParent column denotes that the category is the child of the one specified. If the field is null, it is at the root of the hierarchy.

Does anybody have any opinion regarding the most efficient to get and output the data as a full tree.
Presentation isn't relevant but if I can end up with an object or array structure contain the whole tree I would be overjoyed.
I'm using PHP of course but would like to do as much of the work in SQL as possible.

I hope this makes sense!

Thanks in advance,
Jim

Posted: Wed May 23, 2007 11:33 am
by feyd
Due to your design, it's not very efficient for SQL to produce the tree. You will need PHP to do it for you.

Posted: Wed May 23, 2007 11:38 am
by acidHL
I suspected as much, I just wanted to avoid having it run lots and lots of queries.

Posted: Wed May 23, 2007 12:11 pm
by feyd
You can do it in one query with PHP building the tree with the results.