nested sets?
| From: | ma | Date: | Thu, 20 Nov 2003 14:28:30 +0000 |
| Subject: | nested sets? | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-31651@lists.php.net to get a copy of this message | ||
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 = ®istry::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...