Re: Generating view of tree?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux