Re: nested sets?

Chapter 28 (or 29) in Joe Celko's SQL for Smarites. The chapter on nested sets alone is worth the price of the book. At the very end of the chapter (after taking you through the details of a nested set) he gives a very simple and easy to implement solution for nested sets. Let me know how I may be of service, =C= * Cal Evans * http://www.eicc.com * We take care of your IT, * So you can take care of your business. * * I think inside the sphere. ma wrote:

hi everybody! i try to make a clickable menu. it should be outputed as XML. i found a solution to make it possible to decend only the selected menues. but is there a easier solution? my goal is to first show only the first (level=0) level. if the user clicks on the menu it should select the second (level=1) level, but only from the menu he clicked. (so HAVING level<=1 doesn't help :( ) does anybody know a solution or a good reference for nested sets? (don't have the possibility to use pear - unfortunately cause there are some great tools) here's my db-structure: id (int) unsigned auto_increment left (int) unsigned right (int) unsigned level (int) unsigned (default: 0) title (varchar) link (varchar) menu (int) unsigned (default: 0) heres my solution (using singleton registry pattern and a sql-class working with adodb-functions): ####################################### $reg = &registry::instance(); $sql = &$reg::getEntry('sql'); # $_GET['menu'] = 'About/Company/History' // <- something like this

    $qry =    'SELECT
                first.left,
                first.id,
                first.link,
                first.title,
                first.level,
                ROUND((first.right-first.left-1)/2) AS children
            FROM
                '.$grz->cnf['tablePluginPrefix'].'menu AS first,
                '.$grz->cnf['tablePluginPrefix'].'menu AS second
            ';
    $open = array();
    if(isset($_GET['menu'])) {
        $open = explode(';', $_GET['menu']);
        $or = join(' OR third.id=', $open);
        $qry .=    ',    '.$grz->cnf['tablePluginPrefix'].'menu AS third
                WHERE
                    (first.left BETWEEN second.left AND

second.right

                    AND first.left BETWEEN third.left AND

third.right

                    AND first.level<=third.level+1
                    AND (third.id='.$or.'))
                    OR
                    (first.left BETWEEN second.left AND

second.right

                    AND first.level=0)
                ';
    } else {
        $qry .=    'WHERE
                    first.left BETWEEN second.left AND

second.right

                    AND first.level=0
                ';
    }
    $qry .=    'AND first.menu='.$menuID.'
            GROUP BY
                first.left
            ORDER BY
                first.left
            ';
        $res = $sql->query($qry);

####################################### thx a lot for taking your time, help would be warmly appretiated... -ma # life would be easier if i knew the source code...