Re: Recursive SQL Expression?

Thanks Paul, not quite the answer I hoped for, but nevertheless, an excellent description of the problem. 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:

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