CODEWITHSUNDEEP
phparraysjson
tabek
tabek

Reputation: 11

Get nested/hierarchical JSON from array/PHP

I have a function:

function getROLES() {
    $sql = "
SELECT wur.role_id, wur.user_id, wu.first_name, wu.last_name
FROM user_roles wur LEFT JOIN users wu ON wur.user_id = wu.user_id
WHERE wur.role_id IN (100,101)
    ";
    try {
        $db = getConnection();
        $stmt = $db->query($sql);  
        $roles = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $db = null;
        echo json_encode($roles);
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}'; 
    }
}

the ouput of this function is an array and JSON

Array:

Array ( 
    [0] => Array 
        ( [role_id] => 100 [user_id] => 1 [first_name] => Al [last_name] => Pacino ) 
    [1] => Array 
        ( [role_id] => 100 [user_id] => 5 [first_name] => Brad [last_name] => Pitt ) 
    [2] => Array 
        ( [role_id] => 101 [user_id] => 12 [first_name] => Pierce [last_name] => Brosnan ) 
    [3] => Array 
        ( [role_id] => 101 [user_id] => 10 [first_name] => Johnny [last_name] => Deep ) 
    [4] => Array 
        ( [role_id] => 101 [user_id] => 11 [first_name] => Tom [last_name] => Hanks ) 
) 1

JSON:

[
    {"role_id":100,"user_id":1,"first_name":"Al","last_name":"Pacino"},
    {"role_id":100,"user_id":5,"first_name":"Brad","last_name":"Pitt"},
    {"role_id":101,"user_id":12,"first_name":"Pierce","last_name":"Brosnan"},
    {"role_id":101,"user_id":10,"first_name":"Johnny","last_name":"Deep"},
    {"role_id":101,"user_id":11,"first_name":"Tom","last_name":"Hanks"}
]

The question is: how to identify the group of all users belongs to each role_id. How to convert or change the code to have on the ouput JSON like this:

[
    {"role_id":100, "childrens":
        [
            {"user_id":1,"first_name":"Al","last_name":"Pacino"},
            {"user_id":5,"first_name":"Brad","last_name":"Pitt"}
        ],
    {"role_id":101, "childrens":    
        [
            {"user_id":12,"first_name":"Pierce","last_name":"Brosnan"},
            {"user_id":10,"first_name":"Johnny","last_name":"Deep"},
            {"user_id":11,"first_name":"Tom","last_name":"Hanks"}
        ]
]

Thanks in advance for help.

Please note that im using Postgresql 9.1.8

Upvotes: 0

Views: 1378

Answers (1)

prodigitalson
prodigitalson

Reputation: 60413

Well the first thing that some to mind is jsut to loop over the data set and create the desired structure:

$jsonRoles = array();

foreach($roles as $role) {
   $id = $role['role_id'];
   if(!isset($jsonRoles[$id])) {
      $jsonRoles[$id] = array(
          'role_id' => $id,
          'childrens' => array()
      );
   }
   unset($role['role_id']);
   $jsonRoles[$id]['childrens'][] = $role;
}

// then to get a regular array use array values:
echo json_encode(array_values($jsonRoles));

Upvotes: 2

Related Questions