sql - find out list of inverted hierarchy in mysql -

imagine have table (mysql myisam) child->parent relationship (categories , multiple levels of subcategories)

+--------+---------+ | id     |parent_id| +--------+---------+ | 1      |  null   | | 2      |    1    | | 3      |    2    | | 4      |    7    | | 5      |    1    | | 6      |    5    | +--------+---------+ 

how find children of id, querying id 1 output : 2,5,3,6 ? (order has no importance)

so in other words, how reverted children lookup on parent_link ?

at moment, cycle in php , query parent_id, again , concatenate results in string while there results, slow...

create table my_table( id int, parent_id int );  insert my_table values (1,null), (2,1), (3,2), (4,7), (5,1), (6,5); 

this stored procedure children of given id

delimiter $$ drop procedure if exists get_children$$  create procedure get_children(in v_key int) proc: begin   declare vid text; declare oid text;   declare count int;   create temporary table temp_child_nodes(       id int     );    set vid = v_key;   insert temp_child_nodes(id) select id my_table parent_id = vid;   select group_concat(concat("'",id,"'")) oid my_table parent_id = vid;    set vid = oid;   set count = 0;   set @val = '';   while (vid not null)         set @sql = concat("insert temp_child_nodes(id) select id my_table parent_id in (",vid,")");       prepare stmt1 @sql;       execute stmt1;       deallocate prepare stmt1;        set @tsql = concat("select group_concat(id) @val my_table parent_id in (", vid, ")");       prepare stmt2 @tsql;       execute stmt2;       deallocate prepare stmt2;       set vid = @val;       set count = count + 1;   end while;   #select count;   select * temp_child_nodes;    #select oid; end $$  delimiter ; 

call get_children(1);

mysql> call get_children(1); +------+ | id   | +------+ |    2 | |    5 | |    3 | |    6 | +------+ 4 rows in set (0.22 sec)  query ok, 0 rows affected (0.22 sec)