0

I have a sample MySQL table with the columns ID, NAME AND ID_PARENT.

ID NAME ID_PARENT
1 NODE 1 NULL
2 NODE 2 1
3 NODE 3 2
4 NODE 4 2
5 NODE 5 1
6 NODE 6 5
7 NODE 7 6
8 NODE 8 7
9 NODE 9 6
10 NODE 10 5
11 NODE 11 NULL
12 NODE 12 11
13 NODE 13 11
14 NODE 14 13
18 NODE 15 14

And I have the following tree based on the above data and I need to fetch a node/tree/hierarchy based on ID provided.

NODE 1
  NODE 2
    NODE 3
    NODE 4
  NODE 5
    NODE 6
      NODE 7
        NODE 8
      NODE 9
   NODE 10
NODE 11
  NODE 12
  NODE 13
    NODE 14
      NODE 15

Now if I provide ID=6 (NODE 6) then we should have all the PARENTS (with their CHILDREN) of NODE 6 plus all the nested CHILDREN of NODE 6. So, we should have NODES from 1 to 10 as they fall under one PARENT NODE 1. The extracted tree would be.

NODE 1
  NODE 2
    NODE 3
    NODE 4
  NODE 5
    NODE 6
      NODE 7
        NODE 8
      NODE 9
   NODE 10

So, how can we achieve the above-mentioned scenario?

NOTE: I am using PHP 7 and MySQL 5.6.

Ulrich Eckhardt
  • 16,572
  • 3
  • 28
  • 55
Jamal Abdul Nasir
  • 2,557
  • 5
  • 28
  • 47
  • Does this answer your question? [MySQL - Recursing a tree structure](https://stackoverflow.com/questions/2378678/mysql-recursing-a-tree-structure) – kmoser Mar 01 '22 at 20:17
  • @kmoser WITH RECURSIVE is available in MySQL 5.6? – Jamal Abdul Nasir Mar 01 '22 at 20:23
  • @kmoser the link you provided did not answer my question because they are following a different approach. – Jamal Abdul Nasir Mar 01 '22 at 20:27
  • @KIKOSoftware I have updated the question. I need to achieve the mentioned scenario where I provide ID=6 and I get all the parents (with their nested children) plus all nested children of ID=6. – Jamal Abdul Nasir Mar 01 '22 at 20:29
  • Are you looking for [Hierarchical data in MySQL: parents and children in one query](https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/)? – kmoser Mar 01 '22 at 20:33
  • This has nothing to do with PHP and everything with the SQL server you use. PHP only uses the DB server, it doesn't determine what features it supports. – Ulrich Eckhardt Mar 01 '22 at 20:37
  • @UlrichEckhardt hmmm then how can I achieve the mentioned scenario? – Jamal Abdul Nasir Mar 01 '22 at 20:41

0 Answers0