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
