[PHP-DB] nested sets?

[ma]

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...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php