Get nested/hierarchical JSON from array/PHP

Question

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

prodigitalson · Accepted Answer · 2013-03-13 19:38:18Z

2

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));

answered Mar 13, 2013 at 19:38

prodigitalson

60.6k10 gold badges105 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

Although I'd do it with a loop calling $stmt->fetch() rather than creating two data structures.

prodigitalson Over a year ago

Yeah that would be more appropriate, but i assumed maybe he needed the fetchAll for some other reason not shown.

Collectives™ on Stack Overflow

Get nested/hierarchical JSON from array/PHP

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related