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 <[EMAIL PROTECTED]> wrote:
> on 3/26/03 9:18 PM, Leif K-Brooks at
> [EMAIL PROTECTED] 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
