php - Find all parents sql query with in a single table? -

i have table:

id | child | parent   1      67         0   2      69        67   3      79        68   4      76        69   7      75        68 

i want select records until parent id 0

all records in same table

to quote how represent data tree in sql

some databases, particularly mysql, have issues in handling model, because requires ability run recursive queries mysql lacks.

a long thorough explanation of problem (and solution) can found here: managing hierarchical data in mysql

tl/dr: if want solve problem single query need change tree nested list structure - bit harder understand more efficient handle in mysql.

let's take tree:

• a
  • b
  • c
    • d
    • e
  • f

in adjacency list format this

id | text | parent 1    2    b      1 3    c      1 4    d      3 5    e      3 6    f      1 

getting parents of node d not easy.

now convert nested set:

0_________________a__________________11   1_b_2   3_______c_______8   9_f_10             4_d_5   6_e_7  id | text | lft | rgt 1         0     11 2    b      1     2 3    c      3     8 4    d      4     5 5    e      6     7 6    f      9     10 

now getting parents of node d easy:

select p.* nestedset p inner join nestedset o on o.lft > p.lft , o.rgt < p.rgt o.text == "d" 

as how other operations, follow link posted.