need help with a small problem (generate tree)

[treprti]

Hi, i’m having a small puzzle problem i can’t seem to solve and was hoping someone here could help me with.

The problem is as following:
I have a mysql database that looks like this:

table1(table) | table2(column) | table3(column) | table4(column)
tablename1   | 1                      | 0                      | 0    (values)

table2(table) | table1(column) | table3(column) | table4(column)
tablename2   | 0                      | 1                      | 1    (values)


table3(table) | table1(column) | table2(column) | table4(column)
tablename3   | 0                      | 0                      | 1    (values)


table4(table) | table1(column) | table2(column) | table3(column)
tablename3   | 0                      | 0                      | 1    (values)

what i want to achieve is to generate a tree like this:

tablename1 - tablename2 - tablename3
                        - tablename4 - tablename3

The way i’m trying to make this work goes a bit like this:

check all columns of tablename1
foreach column with value 1 from tablename1
 { echo tablename(s) of column (tablename2)
   
    check all columns of tablename2
    foreach column with value 1 from tablename2
     { echo tablename(s) of column (tablename3 & tablename4)
...etc

and this ofcourse does only work for the amount of times i include extra lines to check, but because i have no idear how deep these trees go, i need a way to loop this.

i hope someone can point me in the right direction. tnx in advance.

[grwebugy]

I’m not sure what you are attempting to do, what you don’t show is how you know the 'parent-child" relationship.
When I do a menu type entry, I usually have a column that says “parent”, that starts with 0. Then I use the index number of the listing, as the parent.

index | description | parent
1 | home | 0
2 | sub page | 1

[treprti]

I think your question might have helped me on my way allready,
the parent relation in my structure was by adding a column of the specific child, and adding a 1 as value to indicate child/parent, so in the structure i demonstrated, table 2 is a child of table 1, and table 3 and 4 of 2, and so on.
Thanks, i will restructure it the proper way

[treprti]

This is what i had:

database:
randomword1
randomword1_index
randomword2
randomword2_index
..etc

randomword1:
autoID | randomword1 | other stuff
1      | bla1        | ..
2      | bla2        | ..
3      | bla3        | ..
5      | bla5        | ..

randomword1_index:
autoID | randomwordID | randomword2_indexID | randomword3_indexID | randomword5_indexID ..etc
1      | 1            | 3                   | 0                   | 1
2      | 3            | 2                   | 0                   | 0
...etc

randomword2:
autoID | randomword2  | other stuff
1      | 2bla1        | ..
2      | 2bla2        | ..
3      | 2bla3        | ..
5      | 2bla5        | ..

randomword2_index:
autoID | randomword2ID | randomword1_indexID | randomword3_indexID | randomword5_indexID ..etc
1      | 1             | 0                   | 2                   | 0
2      | 3             | 1                   | 0                   | 0
...etc

In this example
randomword1 (bla1) is parent of randomword2 (2bla3) and randomword5 (1 not listed here)
randomword1 (bla3) is parent of randomword2 (2bla2)

randomword2 (2bla1) is parent of randomword3 (2 not listed here)
randomword2 (2bla3) is parent of randomword1 (bla1)

---

After your feedback i changed it to this:

database:
randomword1
randomword1_index
randomword2
randomword2_index
..etc

randomword1:
autoID | randomword1 | other stuff
1      | bla1        | ..
2      | bla2        | ..
3      | bla3        | ..
5      | bla5        | ..

randomword1_index:
autoID | randomword1ID | parent
1      | 1             | randomword2.3

randomword2_index:
autoID | randomword2ID | parent
1      | 1             | 0
2      | 2             | randomword1.3
3      | 3             | randomword1.1

i fear this might still not be the best solution because i need to use varchar on parent instead of int.
am i on the right track?

[grwebugy]

Close.
for the submenu of randomWord1 you would want to select all where parent = randomWord1

[treprti]

Ah, ofcourse!
Making it:

randomword2_index:
 autoID | randomword2ID | parent | parentid
 1      | 1             | 0      | 0
 2      | 2             | 1      | 3
 3      | 3             | 1      | 1

Thanks a lot!
Now to find out if i can generate the tree this way

[treprti]

Woops… this would require another index, making it:

 randomword_index:
 autoID | randomword
 1      | randomword1
 2      | randomword2
 3      | randomword3

randomword2_index:
 autoID | randomword2ID | parent | parentid
 1      | 1             | 0      | 0
 2      | 2             | 1      | 3
 3      | 3             | 1      | 1