CODEWITHSUNDEEP
phpmysql
Bahram
Bahram

Reputation: 87

query for selecting all of the branches of a tree

I have a table for category like this

id | title | parent_id
---|-------|------------
1  | cat1  |  null
2  | cat2  |  null
3  | cat3  |  1
4  | cat4  |  1
5  | cat5  |  3
6  | cat6  |  5
7  | cat7  |  6
8  | cat8  |  7

I want to know how can I have the main categories with their branches and a column that gives me the count branches of each tree for example something like this for the main category cat1 :

cat1 => cat3 => cat5 => cat6 => cat7 => cat8

Thank you

Upvotes: 1

Views: 318

Answers (1)

Aashish gaba
Aashish gaba

Reputation: 1776

get all entries from the database in the form of an array.

select * from categories;

$name = [];
$parent = [];
$children = [];

foreach($categories as $category){
    $name[$category['id']] = $category['name'];
    
    $parent[$category['id']] = $category['parent_id'];

    if(isset($category['parent_id'])){
       $children[$category['parent_id']][] = $category['id'];
    }
}

Now you have children for every category in the $children[$category_id] array.

And parent for every child

To print the traversal from any child to the root.

$id = `any id`;

while(isset($id)){
   echo $name[$id] . " => ";
   $id = isset(parent[$id] ? parent[$id] : null); 
}

To get the count of children of any node.

echo sizeof($children[$category_id]);

Now, when it comes to traversal from parent to a child, there can be many options as one node can have more than one child.

I'm writing the code that picks random child and goes for the traversal to the next level.

$id = `any id`;

while(isset($id)){
   echo $name[$id] . " => ";
   if(isset($children[$id])){
     $idx = array_rand($children[$id], 1);
     $id = $children[$idx];
   }
   else{
      // This means you've reached the leaf node.
      $id = null;
   }
}

Upvotes: 1

Related Questions