sql - 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.

Answer 1

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