I am having items relation stored within MySQL table of a following structure:
+---------+------------+
| item_id | parent_id |
+---------+------------+
Items are stored like this:
+-----------+------------+
| item_id | name |
+-----------+------------+
I need to buid tree from given childrens simillar to this:
- A
-- B
--- D
---- E
----- F
------ X
---- G
----- H
------ I
------- X
- J
-- K
--- L
---- X
----- M
------ X
I'm currently showing only "X is M", "X is I" and "X is F" relations. I didn't found out how to show whole tree from X up. Every item can have more then 1 parent so there are always several ways of "how to get to X".
I am using MySQL with cca 50 000 items and there are about 170 000 entries in the relation table. The only way i'm thinking of is to find out all the possible ways for every items and its child and stored them inside cache.
I'd appreciate any help!
