fetch records from databse and generate tree automatically

Question

I have three tables:

Department :

Designation :

Department Designations :

DesignationID and ReportTo are foreign keys of table designation, which shows that a specific designation report to a specific designation. i.e. Manager(Which is designation) Report to CEO(Which is also a designation )

Now I want to generate a tree, like...

I manually enter the entries to shows above tree . i.e

Now My Problem is that, I am not able to write a function in a way that generate this tree, automatically.

In short, After fetching records from database, I want to generate tree as shown above.

Any Solution ?

NOTE: LEVEL OF TREE IS NOT FIX, THERE MAY BE UNLIMITED LEVEL OF TREE

Answer 1

It's Java, so you can make that tree as deep as it needs to be.

Separate the query from the population of the tree from the UI rendering and you'll have no problem. Query the database, populate the tree and give it to the UI to render.

You'll have issues if you try to mingle one part with another.

Answer 2

Here's an hint.

It's a tree you need to iterate recursively over the tree items till you reach the leaf level in order to populate it and as mentioned in answer give by @duffymo you should separate logic of tree population from your query.

Link if you still are unable to solve

Best luck :-)

Answer 3

You can use a Tree structure like this, and then populate it from the query.

Then, you will have different choices, for example:

1) You convert your tree to JSON and iterate it recursively with jQuery;

2) You iterate it directly in Java (recursively), building the output server-side (let's say a big String containing your HTML). Then you simply inject the result in the page (not that good because of coupling between server and client side)

etc...

Answer 4

i think you could use php for drawing this tree. example.
write the join query for selecting data to row <li>
while(!empty($row))
{
echo'<li><ul>$row[designation name]';
while(!empty($next))
{echo'<li><ul>$next[designation name]';
//next sentence must be a select query }
echo'</ul></li>'
}
echo'</ul><li>' }
this is not ur answer but a hint that can tell the logic.