on 1/16/03 7:12 PM, Alan McFarlane at mcfarlane_alan@lycos.co.uk appended
the following bits to my mbox:
> I've a small problem working out the best method of reading data from a
> mySQL database with PHP...
>
> The table is a simple self-referential table constructed (loosely) as
> follows:
>
> cat_id integer unique
> cat_name string
> cat_parent integer (points to a cat_id or 0 if no parent).
>
> Now, I need to extract the data in 'tree order', that is each cat
> (catgory) with it's siblings then the next category and so on...
>
> I'm currently forced into using a very nasty bit of code to list each
> category within a particular category - this is fine for a small
> dataset, but a large one results in a rather expensive load on the
> database server.
Well, I'm sure this isn't the answer you want to hear, but with that table
structure and MySQL as your RDBMS, I don't think there's much else you can
do.
The only suggestion I can really offer is to make sure you are indexing the
parent category as well as the cat_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 denormalized 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
--
I'm inhaling Caesar's last gasp...
http://paul.burney.ws/thoughts/caesars_breath.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
