MPTtree, Hieararchical trees in a database table

[eluser]pistolPete[/eluser]
hi m4rw3r,

first of all thanks a lot for your comprehensive model!

i've got two questions:

i manage hierarchical pages using MPTtree, and i added a column "visible" to my table.
i am now trying to find the first VISIBLE leaf of the tree, whose parents are all VISIBLE itself.
how could i find it?

second question:
i want to echo a hierarchical menu (a <ul><li>... list) to access these pages, but in the menu should just be the pages, which are visible and whose parents are all visible.
is this possible?

thanks in advance,

peter

[eluser]nmweb[/eluser]
First try to find the leaves:

Code:

select from tree where lft=rgt-1

Then for each do

Code:

select from tree where visible=1 and lft<=$lft and rgt >=$rgt

Then check if the lineage is correct, I don't know a way to handle all you want in one query. This will select all visible parents. I usually store the parent_id in a column with mptt so I can derive whether a parent is missing. The other way would be to get the entire lineage of a leaf and then see with a loop in php whether all parents are visible.

I'm not sure it can be done any faster, maybe the more sql proficient know.

[eluser]tobben[/eluser]
This looks great. Thanks for the contribution!

I wonder, is there some way of making the tree2array work nicely with CodeIgniters ol() or ul() HTML-helper?

[eluser]m4rw3r[/eluser]
Ok, been busy and now I've noticed your question when checking all suggestions (be patient, I'll implement some of them), so here comes the answer:

No, I'm afraid not.

You have to either convert this:

Code:

array(
    [0] => array(
        'id' => '1',
        'title' => 'A name',
        'children' => array(
            [0] => array(
                'id' => '2',
                'title' => 'Another name'
            )
        )
    )
)

To

Code:

array('A name' => 'Another name');

Or you could use something like this:

Code:

function MPTtree_ul($array){
    $str = '<ul>';
    foreach($array as $data)
        $str .= '<li>';
        $str .= '<a href="'.$data['id'].'">'.$data['title'].'</a>'; // whatever you want between the <li> </li>
        if(isset($data['children'])){
            $str .= MPTtree_ul($data['children']);
        }
        $str .= '</li>';
    }
    $str .= '</ul>';
    return $str;
}

[eluser]Majd Taby[/eluser]
Hey, I just understood the whole "MPTree" concept. I actually started implementing the same thing for the CodeExtinguisher site ( http://71.65.20.84:81/codexsite/index.php/docs/setup )

I remember you were interested in implementing this feature into CodeExtinguisher. If you still are, I'm willing to work through it with you. I haven't actually tried MPTree yet, but it seems perfect for my use.

[eluser]m4rw3r[/eluser]
I think it would be a very nice addition, both to CodeExtinguisher and MPTtree, to have a good admin interface for storing tree data in a database.

I'm going to make some other additions to MPTtree and it would be nice to make a release "with" a full admin interface, so I'll gladly work with you.

[eluser]Majd Taby[/eluser]
Cool, I can include this as a "view mode". Take a look at the preview (in my signature), and see how there is the ability to use different view_modes? If you want more info on view_modes in the context of CodeExtinguisher, take a look at the temporary docs also linked to in my signature.

[eluser]m4rw3r[/eluser]
Ummm, the view_modes page in your wiki is empty.
But I think it's not a bad idea. It could be useful with also a "flat" table manipulation option.

The things needed are a few buttons/drag-drop, etc.
Can view modes handle this in a nice way?

[eluser]Majd Taby[/eluser]
oh yeah, i thought I had written that page. In any case, yeah, each file in CodeExtinguisher can load its own assets.

[eluser]m4rw3r[/eluser]
That sounds fine.
Maybe we should continue this discussion via MSN or skype.

(While this is increasing our post count , I don't feel like filling a whole thread with a conversation)