More efficient hierarchy system

Question

I am trying to code a hierarchy detection script, I've already written the script, but can only go down 4 levels. Is there a way to condense this to a few lines that will work with an infinite amount of levels?

This script is basically the same code copy and pasted 4 times

<?php
function listCategories($name, $disable_status = 0, $show_nums = 0) {
    echo "<select name='".$name."'>";
    $result = mysql_query("SELECT * FROM categories") or die(mysql_error());
    while($row = mysql_fetch_array($result)) {
        if($row['parent_id']==0) {
            $result2 = mysql_query("SELECT * FROM categories WHERE parent_id=".$row['id']) or die(mysql_error());
            echo "<option value='".$row['id']."'";

            if($disable_status==1&&isParent($row['id'])){
                echo " disabled='disabled'";
            }
            echo ">".$row['name']."</option>";

            while($row2 = mysql_fetch_array($result2)) {
                $result3 = mysql_query("SELECT * FROM categories WHERE parent_id=".$row2['id']) or die(mysql_error());
                echo "<option value='".$row2['id']."'";
                if($disable_status==1&&isParent($row2['id'])){
                    echo " disabled='disabled'";
                }
                echo ">- ".$row2['name']."</option>";

                while($row3 = mysql_fetch_array($result3)) {
                    $result4 = mysql_query("SELECT * FROM categories WHERE parent_id=".$row3['id']) or die(mysql_error());
                    echo "<option value='".$row3['id']."'";
                    if($disable_status==1&&isParent($row3['id'])){
                        echo " disabled='disabled'";
                    }
                    echo ">-- ".$row3['name']."</option>";

                    while($row4 = mysql_fetch_array($result4)) {
                        echo "<option value='".$row4['id']."'>[".$row4['id']."] --- ".$row4['name']."</option>";
                    }
                }
            }
        }
    }
    echo "</select>";
}

function isParent($cat_ID) {
    $result = mysql_query("SELECT * FROM categories WHERE parent_id=".$cat_ID) or die(mysql_error());
    if(mysql_num_rows($result)==0) {
        return FALSE;
    } else {
        return TRUE;
    }
}

My table structure for categories is

id, name, parent_id

If the category has no parent, the parent_id will be 0, or else, it would be the id of the category of it's parent.

All help is appreciated.

You can use recursion to solve this problem. I don't have time right now to elaborate further - hopefully someone else will soon :) — Treffynnon, Dec 08 '11 at 13:12
this answer might prove too simple for you http://stackoverflow.com/questions/5291054/hierarchical-sql-problem/5291159#5291159 — Jon Black, Dec 08 '11 at 15:24

CAFxX · Accepted Answer · 2011-12-08T14:39:39.920

4

I guess something along these lines should do (untested, must be adapted to your needs):

$q = mysql_query("SELECT id, parent_id, name FROM categories");
while ($r = mysql_fetch_row($q)) {
  $names[$r[0]] = $r[2];
  $children[$r[0]][] = $r[1];
}

function render_select($root=0, $level=-1) {
  global $names, $children;
  if ($root != 0)
    echo '<option>' . strrep(' ', $level) . $names[$root] . '</option>';
  foreach ($children[$root] as $child)
    render_select($child, $level+1);
}

echo '<select>';
render_select();
echo '</select>';

an even funkier way of doing this is by using SQL stored procedures, but it may be way overkill in this case...

edited Dec 08 '11 at 14:39

answered Dec 08 '11 at 13:29

CAFxX

28,060
6
41
66

@David there's no explicit stop condition to the recursion in my code so an "infinite hierarchy" is supported – CAFxX Dec 11 '11 at 17:32
But the sql query isn't recursive why do you need a recuraive function? – Micromega Dec 11 '11 at 17:35
@David, exactly to avoid multiple SQL queries. – CAFxX Dec 11 '11 at 17:38
Can you add an example it's not clear to me how your code can print a tree of depth 3 and more. A a tree with 2 depth seems to work. – Micromega Dec 11 '11 at 17:44
@David: Trust me, this approach works. You can see it by following the control flow: as long as a category has any children, no matter their depth, they will be visited (look at the `foreach`) – CAFxX Dec 11 '11 at 18:48
I cant manage to make it work.. seems to be a great way of doing it – DreaminMedia Queretaro May 09 '13 at 19:44

Micromega · Answer 2 · 2011-12-08T13:20:41.020

0

This isn't an answer but instead of using many selects you can use many left joins to traverse 4 hierarchies. Instead of so many row of codes it's just this single query:

select a.id,a.parent_id,b.id,b.parent_id,c.id,c.parent_id,d.id,d.parent_id from a left join b ON a.id = b.parent_id left join c on b.id=c.parent_id left join d on c.id=d.parent_id;

You can use my query with a single table, too, because you can alias a table name:

left join mytable as c on c.id=d.parent_id ... ...

But unfortunetley mysql doesn't support recursive selects. Maybe try another database?

edited Dec 08 '11 at 13:20

answered Dec 08 '11 at 13:14

Micromega

12,486
7
35
72

The thing is, this is 1 table, and I want to be able to have an unlimited number of child categories, not just 4 – nine7ySix Dec 08 '11 at 13:16
@nine7ySix: You can traverse a single table, too. Just use an alias. – Micromega Dec 08 '11 at 13:21

More efficient hierarchy system

2 Answers2

Linked