How to find child of any node in PHP MySQL?

Question

I have a MLM Binary tree and I want to find all the parent and child of that user. How can I do that?

For example:

            A
       B         C
   D      E    F    G

Basically, I am not able to create two PHP MySQL functions in which one will return all the child and other will retrace all the parents and grandparents.

My table structure is:

id | my_referral_id | referral_id

my_referral_id = my referral id; referral_id = parent's referral id

Is your problem with the MySQL queries required, or the PHP code to execute those queries, or the control structures around that PHP code to iterate up and down the tree? And why have you tagged this with both CodeIgniter and CakePHP? CakePHP's ORM does include a Tree behaviour that might well do this out-of-the-box for you (with appropriate configuration), if it's possible for you to use that. — Greg Schmidt, Mar 14 '19 at 05:43
well, it's actually both. Sorry for the wring tags, I have removed them. I guess it would need a recursive function, but my logics are wrong. Can u create two functions which can return all the child while others can return parents? — Shivam, Mar 14 '19 at 07:46

Shoyeb Sheikh · Answer 1 · 2019-03-14T08:32:59.093

I have a solution for this,

If you have a tree structure in mysql like below,

id    |    parent_id
--------------------
1     |      0   
2     |      1
3     |      1
4     |      1
5     |      2
6     |      3
7     |      4

Then you can query this table structure like this,

$mysqli = mysqli_connect("127.0.0.1", "dbuser", "dbpass", "dbname");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

function get_children($id = 0, $mysqli) {    
    $ch = [];
    if($id==0){
        print_r($ch);
        return;
    }
    $children = get_children_loop($id, $ch, $mysqli);
    sort($children);
    return $children;
}

function get_children_loop($id, &$ch, $mysqli) {
    $query = 'select * from mlm where parent_id = ' . $id;
    $result = mysqli_query($mysqli, $query);    
    if ($result->num_rows) {
        while($row = $result->fetch_array(MYSQLI_ASSOC)){
            $ch[] = $row['id'];
            get_children_loop($row['id'], $ch, $mysqli);
        }
    }
    return $ch;
}

function get_parents($id = 0, $mysqli) {

    $p = [];
    $parents = get_parents_loop($id, $p, $mysqli);
    sort($parents);
    return $parents;
}

function get_parents_loop($id, &$p, $mysqli) {
    $query = 'select * from mlm where id = ' . $id;
    $result = mysqli_query($mysqli, $query);
    if ($result->num_rows) {
        while($row = $result->fetch_array(MYSQLI_ASSOC)){
            if ($row['parent_id']!= 0)
                $p[] = $row['parent_id'];
            get_parents_loop($row['parent_id'], $p, $mysqli);
        }
    }
    return $p;
}

$children = get_children(1, $mysqli);
print_r($children);
$parents = get_parents(6, $mysqli);
print_r($parents);

get_children($id,$mysqli) is a function that takes an id and gives you all the children and grandchildren for that id.

Similarly, get_parents($id,$mysqli) gives you all the parents and grandparents for a particular id.

$mysqli being the mysqli connection object.

Note :- This code snippet uses recursion.

I apologize for my mistake. I thought those who know CI can also solve Core PHP problems :) — Shivam, Mar 14 '19 at 08:03

score 0 · Answer 2 · answered Mar 14 '19 at 10:04

This work for you. First you have to write query to get all rows. Second you can use recursive function to create parent child node.

 function1($parentId = null,&$listData) {
            $treeData = array();
            foreach ($listData as $key => $eachNodeData) {
                if ($eachNodeData['parent_id'] == $parentId) {
                    $eachNodeData['my_id'] = $this->createTree ($listData,$eachNodeData['id']);
                    $treeData[] = $eachNodeData;
                    unset($listData[$key]);
                }
            }
            return $treeData;
         }

score 0 · Answer 3 · answered Aug 28 '20 at 15:09

anyone looking for the CI version can use my code:

function _findParent($child_id, $parent_id)
{
    $parents = array();
    $direct_parent = $this->db->query("select parent_id from users where id='$child_id'")->row();
    if (!empty($direct_parent)) {
        array_push($parents, $direct_parent->parent_id);
        if (in_array($parent_id, $parents)) {
            return $parents;
        }
        $parents = $this->_findAllParents($direct_parent->parent_id, $parents, $parent_id);
    }
    return $parents;
}

function _findAllParents($child_id, &$parents, $parent_id)
{
    $direct_parent = $this->db->query("select parent_id from users where id='$child_id'")->row();
    if (!empty($direct_parent)) {
        array_push($parents, $direct_parent->parent_id);
        if (in_array($parent_id, $parents)) {
            return $parents;
        }
        $parents = $this->_findAllParents($direct_parent->parent_id, $parents, $parent_id);
    }
    return $parents;
}

function _findChild($parent_id, $child_id)
{
    $childs = array();
    $direct_childs = $this->db->query("select id from users where parent_id='$parent_id'")->result();
    if ($direct_childs != null) {
        foreach ($direct_childs as $ch) {
            array_push($childs, $ch->id);
            if (in_array($child_id, $childs)) {
                return $childs;
            }
            $childs = $this->_findAllChilds($ch->id, $childs, $child_id);
        }
    }
    return $childs;
}

function _findAllChilds($parent_id, &$childs, $child_id)
{
    $direct_childs = $this->db->query("select id from users where parent_id='$parent_id'")->result();
    if ($direct_childs != null) {
        foreach ($direct_childs as $ch) {
            array_push($childs, $ch->id);
            if (in_array($child_id, $childs)) {
                return $childs;
            }
            $this->_findAllChilds($ch->id, $childs, $child_id);
        }
    }
    return $childs;
}

and the usage of the same is:

 // $children = $this->_findChild(1, 4);
 // log_message('ERROR', json_encode($children));
 // $parents = $this->_findParent(5, 2);
 // log_message('ERROR', json_encode($parents));
 // exit;

 $children = $this->_findChild($user_id, $parent_id->id); //pr,ch
 if (in_array($parent_id->id, $children)) {
     echo json_encode("The selected team leader $team_leader is already a child of $first_name $last_name...");
     exit;
 }

and I hope you can use the _findParent now...

How to find child of any node in PHP MySQL?

3 Answers3