Re: nested sets?

[Cal Evans <cal@xxxxxxxxxxxx>]

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