Tree structure solution in mysql and php

Discussion in 'MySQL' started by jcpatel99, Apr 22, 2009.

0
  1. #1
    I have one table which has three fields. MId Name and ParentId.

    Suppose i have following table.

    MId Name ParentId

    1 a 0
    2 a1 1
    3 a2 1
    4 a3 1
    5 a4 1
    6 a11 2
    7 a12 2
    8 a13 2
    9 a111 6
    10 a112 6
    11 a113 6
    12 a1111 9
    13 a1112 9
    14 a1113 9
    15 a1114 9

    Above table shows the MId(memberid), membername and parent of member.

    I want to get all the child of particular member.

    Suppose i have Mid=2 then we can get all the childs and subchilds of MId 2.

    We can get result in following format.

    Parent Child
    a1 a11
    a1 a12
    a1 a13
    a11 a111
    a11 a112
    a11 a113
    a111 a1111
    a111 a1112
    a111 a1113
    a111 a1114


    I need solution of this question as soon as possible.
    please help me.

    Thanks in advance.
     
    jcpatel99, Apr 22, 2009 IP
    jameskertley likes this.
  2. aras

    aras Active Member

    Messages:
    533
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    60
    #2
    Experimental. Might result in infinite loop, i didn't run or test it. Might give you an idea.

    
deepscan('2');


function deepscan($level) {
$q = mysql_query("SELECT Name FROM table WHERE MId='$level'");
$a = mysql_fetch_array($q,MYSQL_ASSOC);

$q2 = mysql_query("SELECT MId,Name FROM table WHERE ParentId='$level'");

  if (mysql_num_rows($q2) != '0') {

     while ($a2 = mysql_fetch_array($q2)) {
     echo $a['Name'].' - '.$a2['Name'].'<br>';
     flush();
     $que[] = $a2['MId'];
     }

  }

$key = array_search($level,$que);

  if ($key != '') { unset($que[$key]); }

  foreach ($que as $scan) {
  deepscan($scan);
  }


}
    PHP:
     
    aras, Apr 22, 2009 IP
  3. jcpatel99

    jcpatel99 Peon

    Messages:
    2
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    This is tested example with output........



    deepscan('1');

    function deepscan($level)
    {
    $q = mysql_query("SELECT EmpName FROM tbl_emp WHERE EmpID='$level'");
    $a = mysql_fetch_array($q,MYSQL_ASSOC);
    $q2 = mysql_query("SELECT EmpID,EmpName FROM tbl_emp WHERE MgrID='$level'");
    if (mysql_num_rows($q2) != '0')
    {
    while ($a2 = mysql_fetch_array($q2))
    {
    echo $a['EmpName'].' - '.$a2['EmpName'].'<br>';
    flush();
    $que[] = $a2['EmpID'];
    }
    }
    if($que)
    {
    $key = array_search($level,$que);

    if ($key != '') { unset($que[$key]); }

    foreach ($que as $scan)
    {
    deepscan($scan);
    }
    }
    }


    Output Is:

    President - Vice President
    Vice President - CEO
    Vice President - CTO
    CTO - Group Project Manager
    Group Project Manager - Project Manager 1
    Group Project Manager - Project Manager 2
    Project Manager 1 - Team Leader 1
    Project Manager 1 - Test Lead 1
    Team Leader 1 - Software Engineer 1
    Team Leader 1 - Software Engineer 2
    Test Lead 1 - Tester 1
    Test Lead 1 - Tester 2
    Project Manager 2 - Team Leader 2
    Project Manager 2 - Test Lead 2
    Team Leader 2 - Software Engineer 3
    Team Leader 2 - Software Engineer 4
    Test Lead 2 - Tester 3
    Test Lead 2 - Tester 4
    Test Lead 2 - Tester 5
     
    jcpatel99, Apr 23, 2009 IP
  4. francisco_2013

    francisco_2013 Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Hey JCPATEL99,

    How Could organize the same information in this manner?

    President - Vice President
    Vice President - CEO
    Vice President - CTO
    CTO - Group Project Manager
    Group Project Manager - Project Manager 1
    Project Manager 1 - Team Leader 1
    Team Leader 1 - Software Engineer 1
    Team Leader 1 - Software Engineer 2
    Project Manager 1 - Test Lead 1
    Test Lead 1 - Tester 1
    Test Lead 1 - Tester 2
    Group Project Manager - Project Manager 2
    Project Manager 2 - Team Leader 2
    Team Leader 2 - Software Engineer 3
    Team Leader 2 - Software Engineer 4
    Project Manager 2 - Test Lead 2
    Test Lead 2 - Tester 3
    Test Lead 2 - Tester 4
    Test Lead 2 - Tester 5

    Could you help me?.
     
    francisco_2013, Dec 29, 2012 IP