Format results as a tree
Posted: Wed May 23, 2007 11:31 am
Hi all I have a table of the following schema:
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
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