how to get the tree structure from db

[chaitu11]

i have attached the requirement.
1 register's from admin and it has some reference_id,
then,
2 reg with reference of 1 ref_id,
5 reg with reference of 2 ref_id

now my dout is i want to get the complete path i.e 1->2->5->9

[rch1231]

Hello,

What you are referencing is often called a pyramid structure. I have built a few in the past and they can be interesting to code. However that being said the best method I have found to track the items is that every element has an id and a parent id. The parent id references the next unit up in the list. In your exsample there would be the following records:

ID parentId reference_notes
1 0 top of tree
2 1 element
3 1 second element
4 1 element
5 2 sub element
6 2 tree limb
7 4 sub element
8 4 sub element
9 5 sub element
10 5 another sub element
11 7 next level
12 7 next level

then you can track the items back up by starting at 9 and referencing up to 5 that references up to 2 and then 1.
Hope that helps.

[chaitu11]

thanks for your response db design is ok.can you give me business logic for this example if u have..pl

ALthough I've used the "parent" structure in the past, I found it difficult to extract the data. A "simpler" method may be to use "nested sets" with "left" and "right" fields. Although simpler, it can cook your noodle to begin with. We can think of the "left" and "right" as a "start-point" and "end-point". I've got an example somewhere, I'll see if I can find it.

[chaitu11]

ok thank you ..try to send the business code if possible :).it can be done using phpmyadmin or i have to switch to oracle?

Here's an example of the structure...

UNfortunately I don't have my code with me, but I seem to remember that I built an xml document and used the "tab" character as an incrementer/decrementer. I'm sure there's an easier way. I'm pretty sure that there must a php class out there for this.

[chaitu11]

ok thank you...for the help

Here's a library. Looks awesome: http://www.sideralis.org/baobab/