#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0

    Returning a tree structure from table


    Hello all!

    I'm trying to get a tree structure from a table.
    The table looks like this:
    id
    parent_id
    description
    sortorder

    rows with parent_id = 0 is the nodes of the tree

    I would like to have a list returned ordered by sortorder but structured like a tree.

    Like this (number indicates sortorder):
    parent1
    child1
    child4
    child8
    child9
    child6
    parent2
    child2
    child3
    parent3
    child5
    child7
    child8

    Please help me with a smart select statement

    Regards, Larre
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    New York
    Posts
    49
    Rep Power
    11
    Where is the child table and relational field(s)?
    -Dave
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0
    All is contained in the same table:

    The table looks something like this:

    id-------parent_ID-------description
    1----------0----------------Main1
    2----------0----------------Main2
    3----------0----------------Main3
    4----------1----------------Sub1
    5----------1----------------Sub1
    6----------2----------------Sub1
    7----------3----------------Sub1
    8----------5----------------Sub2
    9----------5----------------Sub2
    10---------3----------------Sub1

    It also contains a sortOrder column to sort the individual nodes under same parent.

    Is this clearer?

    Regards, Larre
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    New York
    Posts
    49
    Rep Power
    11
    ?? I'm not clear on what you want the output to look like ?? Order By parent_ID, description? Can you give me what you want the output to look like given the data you supplied in the previous example?
    -Dave
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    3
    Rep Power
    0
    OK, given this table:

    id-------parent_ID-------description
    1----------0----------------Main1
    2----------0----------------Main2
    3----------0----------------Main3
    4----------1----------------Sub1
    5----------1----------------Sub1
    6----------2----------------Sub1
    7----------3----------------Sub1
    8----------5----------------Sub2
    9----------5----------------Sub2
    10---------3----------------Sub1

    The output should be:

    Main1 (id1)
    ---Sub1 (id4)
    ---Sub1 (id5)
    ------Sub2 (id8)
    ------Sub2 (id9)
    Main2 (id2)
    ---Sub1 (id6)
    Main3 (id3)
    ---Sub1 (id7)
    ---Sub1 (id10)

    Thanks for your time so far

    Best regards, Larre
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    3
    Rep Power
    0
    this is called threading. nuke's forums used to do this (I really liked it) but now they gave it up along with all the rest of the most popular forums. I like it cuz it keeps everything more organized and you can have infanent conversations under the same topic, insted of mods deleting or locking double's all the time. that's something I really hate. but anyway.. threading is a great way to do a lot of things if it's done right. it's hard to keep the code running fast. that's the biggest problem.

    here's how I do it. it's basicly a controlled endless loop. it will open a function in itself over and over until it reaches the end of a branch, then goes back until it finds more branches to process. this is only an example. you'll have to use my theory to write your own code

    function do_thread($thread_id){
    query("SELECT id FROM table WHERE thread=".$thread_id)
    if(row_count!=0){
    while($row){
    echo $row[id]
    do_thread($row[id])
    }
    }
    }
    do_thread(0)

    that should give you the basic idea of it. you might also want a safety in that function to keep it from doing a real endless loop somehow. say, if it goes more then 10 levels down into the tree, it will stop going down that branch and finish the rest. yo umight also want some kind of cache, cuz it will have to do a lot of query's each time the code is run. if anyone has any ideas on how to do threading like this with just one query, please let me know!

IMN logo majestic logo threadwatch logo seochat tools logo