Question

i have a db table category_path it is in parent child relationship and its look like this

-----------------------------
 id   |    parent_id
------------------------------
  1   |   NULL        
  2   |   1        
  3   |   2        
  4   |   1        
  5   |   3        
  6   |   2

using this table i want to create a new table which will give me output like this. Where the table below shows distance for each id from parent 0 to that id by traversing through its parent.

----------------------------------
   #  |id     | parent_id | distance    
----------------------------------
   1  |  1    |   1       |   0  
   2  |  1    |   2       |   1  
   3  |  1    |   3       |   2  
   4  |  1    |   4       |   1  
   5  |  1    |   5       |   3  
   6  |  1    |   6       |   2
   7  |  2    |   2       |   0
   8  |  2    |   3       |   1
   9  |  2    |   5       |   2
   10 |  2    |   6       |   1
   11 |  3    |   3       |   0
   12 |  3    |   5       |   1
   13 |  4    |   4       |   0
   14 |  5    |   5       |   0
   15 |  6    |   6       |   0

如何通过数据库查询或编码做到这一点?

Answer 1

最后,你的解决办法是:

function findValue($key,$src){

    return $src[$key];
}    

function inPatentList($val, $patent_list){

    return (in_array($val, $patent_list)) ? true : false;
}

function findFullTraverse($id, $src,&$str){
    if(0 != ($value = findValue($id, $src))){
        if($str==  ){
            $str .= $value;
        }else{
            $str .=  _ .$value;
        }
        findFullTraverse($value,$src,$str);
    }
}
$id_parent = array(
     1  =>  0 ,
     2  =>  1 ,
     3  =>  2 ,
     4  =>  1 ,
     5  =>  3 ,
     6  =>  2 ,
);
$parent = array_values($id_parent);
$ids = array_keys($id_parent);

$depth = array();
$keys_for_value = array();
$id_parent = array_reverse($id_parent, true);
foreach($id_parent as $key => $val){

    $depth[] = $key. _ .$key. _0 ;
    if(inPatentList($key, $parent)){
        $keys_for_value = array_keys($id_parent, $key);
        $depth_found[$key] = $keys_for_value;
        foreach ($depth_found[$key] as $value){
            $str =   ;
            findFullTraverse($value, $id_parent,$str);
            //echo $value. => .$str. <br/> ;
            $traverse_array = explode( _ , $str);
            for($i=0;$i<sizeof($traverse_array);$i++){
                $has_depth = $i + 1;
                $depth[]=$traverse_array[$i]. _ .$value. _ .$has_depth;
            }
        }
    }
}

sort($depth);
echo  <pre> ;
print_r($depth);
echo  <pre> ;

希望能发挥作用!

Answer 2

use the Graph Engine, Its what its designed for http://openquery.com/products/graph-engine

Answer 3

SELECT `id`, `parent_id`, (`id` - `parent_id`) as `difference` 
  from `category_path`...

友情链接