Count result according level

Question

I have Adjacency list mode structure like that and i want to count all title of parent according level like Food = (2,4,3), Fruit = (3,3)

tree tabel structure

after that make tree like that

by this code i m getting right total like for Food =9, Fruit = 6

function display_children($parent, $level) 
{

 $result = mysql_query('SELECT title FROM tree '.'WHERE parent="'.$parent.'"');
 $count = 0;
  while ($row = mysql_fetch_array($result))
   {
    $data=  str_repeat(' ',$level).$row['title']."\n";
    echo $data;
    $count += 1 + $this->display_children($row['title'], $level+1);
   }  
    return $count; 
 }

call function

 display_children(Food, 0) 

Result : 9 // but i want to get result like 2,4,3

But i want to get count total result like that For Food 2,4,3 and For Fruit 3,3 according level

so plz guide how to get total according level

Answer 1

This article has all you need to creates a tree with mysql, and how count item by level

Answer 2

function display_children($parent, $level) 
{

 $result = mysql_query('SELECT title FROM tree '.'WHERE parent="'.$parent.'"');
 $count = "";
  while ($row = mysql_fetch_array($result))
   {
    $data=  str_repeat(' ',$level).$row['title']."\n";
    echo $data;
    if($count!="")   
        $count .= (1 + $this->display_children($row['title'], $level+1));
    else
        $count = ", ".(1 + $this->display_children($row['title'], $level+1));
   }  
    return $count; 
 }

Lets try this once..

Answer 3

If you want to get amounts by level, then make the function return them by level.

function display_children($parent, $level) 
{

 $result = mysql_query('SELECT title FROM tree WHERE parent="'.$parent.'"');
 $count = array(0=>0);
  while ($row = mysql_fetch_array($result))
   {
    $data=  str_repeat(' ',$level).$row['title']."\n";
    echo $data;
    $count[0]++;
    $children= $this->display_children($row['title'], $level+1);
    $index=1;
    foreach ($children as $child)
    {
     if ($child==0)
      continue;
     if (isset($count[$index]))
      $count[$index] += $child;
     else    
      $count[$index] = $child;
     $index++;
    }
   }  
    return $count; 
 }

Note that its hard for me to debug the code as i dont have your table. If there is any error let me know and i will fix it. Anyways result will be array which should contain amounts of levels specified by indices:

$result=display_children("Food", 0) ;
var_export($result);//For exact info on all levels 
echo $result[0];//First level, will output 2
echo $result[1];//Second level, will output 4
echo $result[2];//Third level, will output 3

And by the way there is typo in your database, id 10 (Beef) should have parent "Meat" instead of "Beat" i guess.

If you want to see testing page, its here.

Answer 4

If you don't mind changing your schema I have an alternative solution which is much simpler.

You have your date in a table like this...

item             id
-------------+------
Food         |  1
Fruit        |  1.1
Meat         |  1.2
Red Fruit    |  1.1.1
Green Fruit  |  1.1.2
Yellow Fruit |  1.1.3
Pork         |  1.2.1

Queries are now much simpler, because they're just simple string manipulations. This works fine on smallish lists, of a few hundred to a few thousand entries - it may not scale brilliantly - I've not tried that.

But to count how many things there are at the 2nd level you can just do a regexp search.

select count(*) from items
where id regexp '^[0-9]+.[0-9]+$'

Third level is just

select count(*) from items
where id regexp '^[0-9]+.[0-9]+.[0-9]+$'

If you just want one sub-branch at level 2

select count(*) from items
where id regexp '^[0-9]+.[0-9]+$'
and id like "1.%"

It has the advantage that you don't need to run as many queries on the database, and as a bonus it's much easier to read the data in the tables and see what's going on.

I have a nagging feeling this might not be considered "good form", but it does work very effectively. I'd be very interested in any critiques of this method, do DB people think this is a good solution? If the table were very large, doing table scans and regexps all the time would get very inefficient - your approach would make better use of the any indexes, which is why I say this probably doesn't scale very well, but given you don't need to run so many queries, it may be a trade off worth taking.