Category Hierarchy - check level

Question

I use this script to create array with hierarchy categories:

$refs = array();
$list = array();

$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";
$result = mysql_query($sql);
while($data = @mysql_fetch_assoc($result)) {
$thisref = &$refs[ $data['item_id'] ];

$thisref['parent_id'] = $data['parent_id'];
$thisref['name'] = $data['name'];

    if ($data['parent_id'] == 0) {
    $list[ $data['item_id'] ] = &$thisref;
    } else {
    $refs[ $data['parent_id'] ]['children'][ $data['item_id'] ] = &$thisref;
    }
}

How I can get level of element array? This is example:

1. Cat A (level 0)
   • Sub-Cat 1 (level 1)
     • Sub_Sub_Cat 1 (level 2)
     • Sub_Sub_Cat 2 (level 2)
   • Sub_Cat 2 (level 1)
2. Cat B (level 0)
3. Cat C (level 0)

and here is source:

Category Hierarchy (PHP/MySQL)

Answer 1

You need to add it as well when you build the list. This is basically two-folded:

• If an element has no parent, it's level is 0. Set it.
• If an element has a parent, got to the parent, pick that level, add one to it and set it.

Depending on how sorted your data is, this can either be done on the fly with the iteration you already have, or you need to first only add those that have no parents in the first round, and then re-iterate and add the levels for the children.

Answer 2

You can use this function to get all level of categories

function getChildCategories($id=0){
  global $db;
  $res = $db->getAllArray("SELECT item_id, parent_id, name FROM items  parent_id = '$id' ORDER BY name");
  $raws = array();  
  foreach($res as $raw)
  {
   $raws[$raw['id']]['data'] = $raw;
    $subCount = getChildCategories($raw['id']);
   if(count($subCount)>0){
        $raws[$raw['id']]['sub'] =  $subCount;
   }
  }
 return $raws;
}

Cat A (level 0)
    Sub-Cat 1 (level 1)
        Sub_Sub_Cat 1 (level 2)
        Sub_Sub_Cat 2 (level 2)
    Sub_Cat 2 (level 1)
Cat B (level 0)
Cat C (level 0)