Re: 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 <cal@calevans.com> > Datum: Fri, 21 Nov 2003 11:24:52 -0600 > An: ma <grillen@abendstille.at> > Cc: PHP-DB <php-db@lists.php.net> > 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 >