Show Mysql data as array in multi level

Question

I created a database like this, I want to show it as an PHP array to each other with ref_id as the connector beetween users.

The result should based on it's level down. And I want a result like this

array(
  [0] => array
      (
        [id] => 2
        [username] => user2
        [ref_down] => Array
                   (
                     [0] => Array
                         (
                           [level] => 1
                           [count] => 1
                           [users] => user4
                          )
                     [1] => Array
                         (
                           [level] => 2
                           [count] => 1
                           [users] => user6
                          )
                    )
        )
  [1] => array
      (
        [id] => 3
        [username] => user3
        [ref_down] => Array
                   (
                     [0] => Array
                         (
                           [level] => 1
                           [count] => 1
                           [users] => user5
                          )
                     [1] => Array
                         (
                           [level] => 2
                           [count] => 1
                           [users] => user7
                          )
                    )
        )
)

I hope anyone help me to find a MYSQL and PHP solution for this problem, Please help me

============================================================================= updated

Try to implement what you give to me like this

function getRefs($userid,$level,$deep) {
    global $conn;
    $arr_refs = $arr_user=array();
    $stmts = $conn->prepare('SELECT u.id,u.fullname,u.username,u.ref_id,u.created_at FROM users u WHERE ref_id = :uid');
    $stmts->bindValue(':uid', $userid);
    $stmts->execute();
    $stmts->setFetchMode(PDO::FETCH_ASSOC);
    $rows = $stmts->fetchAll();
    $levelsekarang=$level;
    $level++;
    foreach ($rows as $row) {
        $arr_user['level'] = $levelsekarang;
        $arr_user['id'] = $row['id'];
        $arr_user['fullname'] = $row['fullname'];
        $arr_user['username'] = $row['username'];
        $arr_user['ref_id'] = $row['ref_id'];
        $arr_user['created_at'] = $row['created_at'];
        $arr_user['ref_stats'] = getRefs($row['id'],$level,$deep);
        $arr_refs[]=$arr_user;
    }
    return empty($arr_refs) ? null : $arr_refs;  
}
echo '<pre>';
$arr_refs = getRefs(1,0,1);
print_r($arr_refs);
echo '</pre>';

What I got is like this (Based on database table)

My question now is, can we edit this? so the result will be like this

What I want is inside the ref_stats from user 2 and 3 there is level 1 and level 2 user which is user 6 and user 7, so that I can reach them by using foreach function

What is precise MySQL version? What is maximal nesting level, is it fixed? — Akina
– Akina, Commented Jan 13, 2021 at 6:55
Why users 4 and 6 (5 and 7) have different tree levels but are prosessed at the same array level? Why users 2 and 3 have no level at all? Why user with id=1 is skipped at all? — Akina
– Akina, Commented Jan 13, 2021 at 6:59
User ID 1 is the main user who logged in, so basically it's I need to determine who refer the User ID 1 (user 2 and 3) the I just want to count how many user that is referred by user 2 and 3 which is user 4 and 5, these user is level 1 from user 2 and 3, and at last because i just want 3 level nesting, user 6 and 7 is level 2 from user 2 and 3 as referred by user 4 and 5 — dogemaster
– dogemaster, Commented Jan 13, 2021 at 7:16

Hammad Ahmed khan · Accepted Answer · 2021-01-13 07:12:02Z

This requires a very basic recursive function to parse the child/parent pairs to a tree structure and another recursive function to print it out. Only one function would suffice but here's two for clarity (a combined function can be found at the end of this answer).

$tree = array(
    'H' => 'G',
    'F' => 'G',
    'G' => 'D',
    'E' => 'D',
    'A' => 'E',
    'B' => 'C',
    'C' => 'E',
    'D' => null
);

Then the function that parses that array into a hierarchical tree structure:

function parseTree($tree, $root = null) {
    $return = array();
    # Traverse the tree and search for direct children of the root
    foreach($tree as $child => $parent) {
        # A direct child is found
        if($parent == $root) {
            # Remove item from tree (we don't need to traverse this again)
            unset($tree[$child]);
            # Append the child into result array and parse its children
            $return[] = array(
                'name' => $child,
                'children' => parseTree($tree, $child)
            );
        }
    }
    return empty($return) ? null : $return;    
}

And a function that traverses that tree to print out an unordered list:

function printTree($tree) {
    if(!is_null($tree) && count($tree) > 0) {
        echo '<ul>';
        foreach($tree as $node) {
            echo '<li>'.$node['name'];
            printTree($node['children']);
            echo '</li>';
        }
        echo '</ul>';
    }
}

And the usage:

$result = parseTree($tree);
printTree($result);

Here's the contents of $result:

Array(
    [0] => Array(
        [name] => D
        [children] => Array(
            [0] => Array(
                [name] => G
                [children] => Array(
                    [0] => Array(
                        [name] => H
                        [children] => NULL
                    )
                    [1] => Array(
                        [name] => F
                        [children] => NULL
                    )
                )
            )
            [1] => Array(
                [name] => E
                [children] => Array(
                    [0] => Array(
                        [name] => A
                        [children] => NULL
                    )
                    [1] => Array(
                        [name] => C
                        [children] => Array(
                            [0] => Array(
                                [name] => B
                                [children] => NULL
                            )
                        )
                    )
                )
            )
        )
    )
)

Collectives™ on Stack Overflow

Show Mysql data as array in multi level

============================================================================= updated

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

============================================================================= updated

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related