MySql Query issue in php

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

How to get this either by database query or by coding?

i want recursive query for it but i know that recursive query is not supported by MySql — M.I.T., May 10 '12 at 09:46
As you note, MySQL doesn't support recursive functions so it is not well suited to this adjacency list model for storing hierarchical data. You ought to consider restructuring your data to use either nested sets or closure tables. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. — eggyal, May 10 '12 at 09:47
select * from category_path i inner join category_path c on c.id = i.id inner join category_path c2 on c.parent_id = c2.id where c2.parent_id = @parentId — M.I.T., May 10 '12 at 09:49
is it compulsion to do with mysql query? you can do it by PHP script — Rajan Rawal, May 10 '12 at 10:06
If you're going to be dealing with tree-data a lot, check out [Modified Preorder Tree Traversal](http://www.sitepoint.com/hierarchical-data-database-2/) — Cylindric, May 11 '12 at 11:55

score 1 · Accepted Answer · answered May 10 '12 at 14:20

Finally Spending whole evening here is your solution:

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>';

Hope this should work!!!

@M.I.T Does it solved your Problem? You may call it as backtracking. I have used recursion here. If you don't get let me know. Thank you. — Rajan Rawal, May 11 '12 at 08:30

score 0 · Answer 2 · answered Nov 19 '12 at 09:28

0

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

answered Nov 19 '12 at 09:28

exussum

18,275
8
32
65

score -1 · Answer 3 · edited Nov 19 '12 at 09:05

-1

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

edited Nov 19 '12 at 09:05

Ben

51,770
36
127
149

answered May 10 '12 at 09:57

Sindhara

1,423
13
20

MySql Query issue in php

3 Answers3