How to get subtree table in php

Question

I have a table like this :

id    title      parent_id
1       A           0
2       B           0
3       C           1
4       F           3
5       O           3
6       D           2
7       J           6
8       T           2
9       P           8


A
   C
      F
      O

B
  D
    J
  T
    P

now, if I give 2, this function should return:

and also if I give 3 it function should return:

4
5

I don't want just leaf nodes !!!
I think there is a sql query.

There is similar question and good answer here: http://stackoverflow.com/questions/2915748/convert-a-series-of-parent-child-relationships-into-a-hierarchical-tree . This might be a duplicate question then. — Nikola Kirincic, Nov 17 '16 at 08:31

score 0 · Answer 1 · answered Nov 17 '16 at 08:45

I think you need to join table with itself. I haven't try it myself but it is like code below

SELECT a.id, a.title 
FROM tbl AS a 
WHERE a.id = :id

UNION

SELECT b.id, b.title
FROM tbl AS a
INNER JOIN tbl AS b ON a.id = b.parent_id 
WHERE a.id = :id

UNION

SELECT c.id, c.title
FROM tbl AS a
INNER JOIN tbl AS b ON a.id = b.parent_id
INNER JOIN tbl AS c ON b.id = c.parent_id 
WHERE a.id = :id

UNION

SELECT d.id, d.title
FROM tbl AS a
INNER JOIN tbl AS b ON a.id = b.parent_id
INNER JOIN tbl AS c ON b.id = c.parent_id 
INNER JOIN tbl AS d ON c.id = d.parent_id 
WHERE a.id = :id

 ...

Keep adding query until number of depth you require. There is other approach for example using stored procedure.

I think it doesn't a good idea . because if I want to fetch depth 50 ? and `depth` not available ! — S.M_Emamian, Nov 17 '16 at 08:53
I believe if there is no parent child relationship inner join simply return no result set. However the more depth the more resources it needs — Zamrony P. Juhara, Nov 17 '16 at 08:57

score 0 · Accepted Answer · answered Nov 17 '16 at 09:15

0

just sql :

select  id,
        title,
        parent_id 
from    (select * from products
         order by parent_id, id) base,
        (select @pv := '3') tmp
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

answered Nov 17 '16 at 09:15

S.M_Emamian

17,005
37
135
254

How to get subtree table in php

2 Answers2