Format results as a tree

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Format results as a tree

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Post by acidHL »

I suspected as much, I just wanted to avoid having it run lots and lots of queries.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You can do it in one query with PHP building the tree with the results.
Post Reply