Re: [PHP-DB] 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...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] nested sets?
hi!
thx for the answer! in fact i'm just searching for a query to desend a
defined path in the structure, but left others alone. e.g.
path: about/company
structure:
about
company
history
today
employees
intern
extern
products
...
should end in
about
company
history
today
employees
products
i think of buying the book. its better to have a good reference.
thx -ma
# life would be easier if i knew the source code...
Von: Cal Evans [EMAIL PROTECTED]
Datum: Fri, 21 Nov 2003 11:24:52 -0600
An: ma [EMAIL PROTECTED]
Cc: PHP-DB [EMAIL PROTECTED]
Betreff: Re: [PHP-DB] 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...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] nested sets?
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
