1

I have following kind of data to table:

id    parent_id    child_id    level
1     53987          52548       1
2     60764          52548       2
3     60764          53987       1
4     60764          59695       2
5     63457          59695       1
6     60764          63457       1

So, how i can get data by recursively with level and store data to array like ['child_id','parent_id',level]. I need help for writting query and generate the tree.

The tree should be like : enter image description here

Note: I can't change the database's table structure. I must need tree based on given table structure.

Sandeep Thakkar
  • 94
  • 8
  • Provide more detail.. like what kind of output do you need. – Ninja Turtle Oct 23 '17 at 12:12
  • May be this would help you: https://stackoverflow.com/questions/3362669/what-are-the-known-ways-to-store-a-tree-structure-in-a-relational-db – Tarun Oct 23 '17 at 12:14
  • @Tarun : I can't change the database's table structure. I must need tree based on given table structure. – Sandeep Thakkar Oct 23 '17 at 12:24
  • 1
    We are always glad to help and support new coders but ***you need to help yourself first. :-)*** After [**doing more research**](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). – Jay Blanchard Oct 23 '17 at 12:27
  • @SandeepThakkar just keep searching recursively if you dont mind performance and cache the tree in your application code so you dont have to keep hitting DB to retrieve the tree. – Tarun Oct 23 '17 at 12:33
  • For graphical generation of the tree, look at [GraphViz](https://www.graphviz.org/gallery/) – Mawg says reinstate Monica Feb 13 '18 at 08:42
  • Just consider all the entries with level 1. It will give you the required array. – Sanket Makani Feb 14 '18 at 19:10

1 Answers1

1

Try This Query:

select  id,
        `child_id`,
        `parent_id` , level
from    (select * from vtiger_ib_level
         order by parent_id, id) products_sorted,
        (select @pv := '60764') initialisation
where   find_in_set(`parent_id`, @pv)
and     length(@pv := concat(@pv, ',', id))
dferenc
  • 7,918
  • 12
  • 41
  • 49
Mahesh Kathiriya
  • 612
  • 7
  • 9