on 1/16/03 7:12 PM, Alan McFarlane at [EMAIL PROTECTED] 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

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:

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


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:


Hope that helps.


Paul Burney


I'm inhaling Caesar's last gasp...

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to