php - Tree Traversal for getting the nodes in an Array -

i have tree this

      2     /  |  \   3   4   6      / \     7   8          \            9    

in db table this

    node_id parent_id          2        0        3        2        4        2        6        2        7        4        8        4        9        8 

the problem is, if pass 4 node id function, return should in array ( or comma separated ) 7,8 , 9 ( means nodes under 4 ). have tried recursive function, not getting expected. please suggest possible way in php

you can @ article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ (originally published under mysql.com, deleted)

for data can try similar following:

create table `tree` (node_id int not null, parent_id   int not null);  insert `tree`(node_id, parent_id) values(2,0), (3,2), (4,2), (6,2), (7,4), (8,4), (9,8);   select t1.node_id lev1, t2.node_id lev2, t3.node_id lev3, t4.node_id lev4 tree t1 left join tree t2 on t2.parent_id = t1.node_id left join tree t3 on t3.parent_id = t2.node_id left join tree t4 on t4.parent_id = t3.node_id t1.node_id = 4;