Also just saw a new PEAR package posted that helps you work with nested sets. Haven't tried it yet. http://pear.php.net/package-info.php?package=DB_NestedSet olinux --- Paul Burney <paul.lists@burney.ws> wrote: > on 3/26/03 9:18 PM, Leif K-Brooks at > eurleif@buyer-brokerage.com appended > the following bits to my mbox: > > > I have a table with a tree. Thing is, I need to > generate a view of it like: > > Category > > Sub-category > > Sub-sub-category > > Another sub-category > > Another category > > Sub-category > > > > Any way to do this, without using a huge number of > queries? Using MySQL. > > This comes upon the list every few weeks. Check the > archives for more > information. > > Basically, in MySQL there is no way to do it without > using a large number of > queries if you have the traditional table layout > with a record_id and a > parent_id in each, so that the table relates to > itself. > > The only suggestion I can really offer is to make > sure you are indexing the > parent_id as well as the record_id. > > In the php code, make it a little less nasty by > using a function > recursively, for example: > > <?php > function list_sub_cats($p = 0) { > > $str = ''; > $q = 'SELECT cat_id,cat_name FROM cats WHERE > cat_parent="' . $p . '"'; > $r = mysql_query($q,$dbh); > if (mysql_num_rows($r) > 0) { > $str .= '<ul>'; > while ($s = mysql_fetch_assoc($r)) { > $str .= '<li>' . $s['cat_name']; > $str .= list_sub_cats($s['cat_id']); > $str .= '</li>'; > } > $str .= '</ul>'; > } > return $str; > } > ?> > > Calling list_sub_cats() above should return a nested > unordered list in HTML > of your categories (hasn't been tested, though). > > If you were using Oracle, you could use a CONNECT BY > term in your query or > write a stored procedure to give you back the tree. > See this site for > details: > > <http://philip.greenspun.com/sql/trees.html> > > If you aren't tied to that database structure, you > could investigate the > must faster de-normalized alternative nested set > model that is often > mentioned on this list: > > <http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html> > <http://www.dbmsmag.com/9605d06.html> > <http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm> > > Hope that helps. > > Sincerely, > > Paul Burney > <http://paulburney.com/> > > Q: Tired of creating admin interfaces to your MySQL > web applications? > > A: Use MySTRI instead. Version 3.1 now available. > > <http://mystri.sourceforge.net/> > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > __________________________________________________ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
