MYSQL
How to pull a list....
10 views
Hey everyone!
I've got a question for you that's been stumping me for a while...
I have a table of relationships like this:
How it works is the parent_a and parent_b correspond to the id from which they originate.
The easiest way to think of it is say I'm breeding some birds and I want to keep track of it. id is the bird. So let's say bird 5 has the parents of 1 and 2. 6 has the parents of 3 and 4. The parent of 0 means we don't know and don't need to go back any farther.
My question is, how do I query this to generate a list so I can display a "lineage" of relationships? Ideally I'd like to display this info in a table with the bird on top, and it's mother and father in the next row. That's easy enough but then I want to get the mother's mother and it's lineage and the same with the father's father. I want to go all the way back to the beginning.
I know it's possible cause I've seen it done, I'm just wondering if there is a way to query for it without using endless loops.
Hopefully I was clear, if you want me to explain a bit more let me know.
EDIT:
Here is an awesome example from a game I play: http://dragcave.net/lineage/gmpkY
I've got a question for you that's been stumping me for a while...
I have a table of relationships like this:
Code:
id | parent_a |parent_b ____________________ 1 | 0 | 0 2 | 0 | 0 3 | 0 | 0 4 | 0 | 0 5 | 1 | 2 6 | 3 | 4 7 | 5 | 6 8 | 7 | 5
The easiest way to think of it is say I'm breeding some birds and I want to keep track of it. id is the bird. So let's say bird 5 has the parents of 1 and 2. 6 has the parents of 3 and 4. The parent of 0 means we don't know and don't need to go back any farther.
My question is, how do I query this to generate a list so I can display a "lineage" of relationships? Ideally I'd like to display this info in a table with the bird on top, and it's mother and father in the next row. That's easy enough but then I want to get the mother's mother and it's lineage and the same with the father's father. I want to go all the way back to the beginning.
I know it's possible cause I've seen it done, I'm just wondering if there is a way to query for it without using endless loops.
Hopefully I was clear, if you want me to explain a bit more let me know.
EDIT:
Here is an awesome example from a game I play: http://dragcave.net/lineage/gmpkY