Since I'm not using the more powerful DBMS systems, I'll just stick to my original method and with the addition of a simple caching mechanism to limit the load on the server.
Thanks again...
--
Alan
Paul Burney wrote:
on 1/16/03 7:12 PM, Alan McFarlane at mcfarlane_alan@lycos.co.uk appended the following bits to my mbox:Well, I'm sure this isn't the answer you want to hear, but with that tableI'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.
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
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
