mysql recursive(tree) parent child category

Question

I am new in mysql. This is my table:

category table:

id | name        | prent
----------------------------
1  |  os         | null
2  | linux       | 1
3  | ubuntu      | 2
4  | xubuntu     | 3
5  | lubuntu     | 3
6  | zubuntu     | 3
7  | zubuntu 2   | 6
8  | suse        | 2
9  | fedora      | 2
10 | windowse    | 1
11 | windowse xp | 10
12 | windowse 7  | 10
13 | windowse 8  | 10
14 | food        | null
15 | dance       | null

Each category has a parent and I want to prepare them to show in a drop-down menu.

This is what I want to get:

id | name          | depth
----------------------------
1  | os            | 0
2  | -linux        | 1
3  | --ubuntu      | 2
4  | ---xubuntu    | 3
5  | ---lubuntu    | 3
6  | ---zubuntu    | 3
7  | ----zubuntu 2 | 4
8  | --suse        | 2
9  | --fedora      | 2
10 | -windows      | 1
11 | --windows  xp | 2
12 | --windows  7  | 2
13 | --windows  8  | 2
14 | food          | 0
15 | dance         | 0

Here, categories are not in order and my code has to provide order for children categories far from their parents. Indentation before name is provided based on depth of parents of each category. There is no limit for number of children of each category however the total number of categories will not exceed 100.

Is there any query that gives such result? I prefer a query that can be run in form of active record in a PHP framework.

no. mysql doesn't support recursive queries. you'll have to do an iterative loop in your code to work your way down the tree. — Marc B, Aug 06 '13 at 14:54
Did you look here? http://stackoverflow.com/questions/8633497/mysql-how-to-query-parent-child?rq=1 — Ed Manet, Aug 06 '13 at 15:01
@EdManet, how about the number of depth more than one and two? — monjevin, Aug 06 '13 at 15:04
`while($not_done) { do_more_queries(); }` basically. the code is up to you. — Marc B, Aug 06 '13 at 15:07

score 11 · Accepted Answer · edited Apr 13 '17 at 12:42

11

This Thread led me. Thanks to @RolandoMySQLDBA

DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(`prent`,-1) INTO ch FROM
        (SELECT `prent` FROM Table1 WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;

END $$
DELIMITER ;

A working fiddle here.

SELECT id,GetAncestry(id) as parents from Table1 where id = 7;

ID  PARENTS
7   6,3,2,1

edited Apr 13 '17 at 12:42

Community

1
1

answered Aug 06 '13 at 16:26

Praveen Prasannan

7,093
10
50
70

thanks, may I know what `A` means here? `(SELECT 'prent' FROM Table1 WHERE id = ch) A;` – monjevin Aug 06 '13 at 19:06
`A` is just an alias name for this result set :(SELECT `prent` FROM Table1 WHERE id = ch) – Praveen Prasannan Aug 07 '13 at 04:11
I recognize how cool this function is, however please can you explain how you would utilise the ancestry data in order to add it to a hierarchical list (its late and my brain is not firing) – Hightower Jan 08 '14 at 22:31

mysql recursive(tree) parent child category

1 Answers1

Linked