1

I have a tabel containing a column named parent which is able to store a parent ID. This makes it possible to create a tree-structure of my data.

Are there any good helper-functions for travelling through such a tree-structure in MySQL?

For example, if I have a row in my table and I want to be able to retreive all "parents" above it. So getting the it's parent's parent ID and so on...

user809829
  • 1,179
  • 3
  • 14
  • 33
  • possible duplicate of [Is it possible to query a tree structure table in MySQL in a single query, to any depth?](http://stackoverflow.com/questions/169817/is-it-possible-to-query-a-tree-structure-table-in-mysql-in-a-single-query-to-an) – Konerak Mar 26 '12 at 07:05
  • 1
    [This thread](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) has lots of useful links. – Ted Hopp Mar 26 '12 at 07:06
  • @Konerak, that's different - that question is asking for how to get all descendants to any depth. Looking through the search results, I see a lot of similar questions about trees and sql, but none asking this particular question -- how to get all parents. – Ben Lee Mar 26 '12 at 07:09
  • @BenLee Yet the answer uses information from a link in my duplicate-proposed question's answer... the question indeed is a bit different, but the answer covered this question too. – Konerak Mar 26 '12 at 07:20

2 Answers2

2

Copied from this popular link:

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
 (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
 (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

SELECT * FROM nested_category ORDER BY category_id;

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

You can use this to find all parents from node FLASH:

tree Retrieving a Single Path

With the nested set model, we can retrieve a single path without having multiple self-joins:

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

This works because the child's left will be in between its parents' left and right.
You can read further or search for modified preorder tree traversal.

cctan
  • 2,015
  • 3
  • 19
  • 29
1

Please read this "Hierarchical queries in MySQL" article, for in depth explanation on the topic.

But still I would like to keep things simple and create a recursive PHP function instead.

But after reading few articles, I found that best way regarded for this, is Modified Preorder Tree Traversal, which has been further explained in this article.

Starx
  • 77,474
  • 47
  • 185
  • 261