2

I have this mysql table

id    parent_id
1      NULL
2       1
3       1
4       2
5       4
6       5

and so on, note that this is a structure for mlm construction.

id is non repeatable number for a member and parent_id is their upline.

In this instance, member id 6 having upline with id 5, and member id 4 is the upline to id 5,and member id 2 is the upline to member id 4,all of which makes those people upline of member id 6.

I know i can retrieve the direct upline of member id 6 by getting its parent id, and the only value we have is member id=6, we get its direct upline by getting parent id,and how to get the parent id of the parent id of 6,and parent id of parent id of parent id of 6 and so on in terms of php.

thank you.

Cheong D Mun Pong
  • 247
  • 1
  • 2
  • 10
  • 1
    http://www.sitepoint.com/hierarchical-data-database-2/ – Rob Jul 24 '12 at 13:56
  • 1
    What you actually need for this, and MySQL doesn't (yet) support it, is a **recursive self join**. However, if you Google [mysql recursive self join](http://www.google.com/search?q=mysql+recursive+self+join) you will find many work arounds and explanations on how to redesign your database so this is not required. – DaveRandom Jul 24 '12 at 13:59
  • i couldnt found anyone doing this thing and i kind of know its not quite possible,lets put aside mysql, is it possible to make it in php?as, it fetch one value at a time and set it as a $value,and use it to execute query again,and at last gather all the info and echo it – Cheong D Mun Pong Jul 24 '12 at 14:02
  • @DaveRandom Top marks for guessing what the question was. – Pete Jul 24 '12 at 14:02
  • @CheongDMunPong How many records are you likely to accumulate/ If it's not too many you could load the whole table into PHP and then use recursive functions to find what you want. – Pete Jul 24 '12 at 14:06
  • @Pete thanks for being helpful,this is for mlm,so its kind of like 100,000 member at best if possible?i would like to store data in mysql table and use php to do the job, what you think?of my previous comment? lets say i fetch parent id where id=6, then set the parent id as $a in php,and use this $a to execute the same query again, and loop 4 times – Cheong D Mun Pong Jul 24 '12 at 14:08
  • @CheongDMunPong Storing Hierarchical Data in a Database Article topic has been given so much importance in every language platform. Rather than just getting temporary solution, I advice to go thoroughly Spare sometime for it. [Read it here](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) – Rajan Rawal Jul 24 '12 at 14:43

1 Answers1

1

For recursive data, you better have a look at nested sets. Thats a technique to get tree-data managed with sql.

http://en.wikipedia.org/wiki/Nested_set_model

http://www.klempert.de/nested_sets/ (german)

Ron
  • 1,336
  • 12
  • 20