PHP/MySQL - Nested groups in JSON

Question

I'm creating nested (hierarchical) groups in JSON. The structure is simply - grand parent -> parent -> child:

main
  secondary
    mcondition

This is using the following MySQL/PHP to so far format main -> mcondition.

How should I change this to add the second level ('secondary' e.g. the parent level) between main and mcondition?

The column for secondary is mcondition.secondary

$query = 'SELECT * FROM mcondition ORDER BY mcondition.main ASC';
$result = $connection->query( $query );

$results = array();
$temp = array();

while ($line = mysqli_fetch_array($result)) {
    $results[] = $line;
}

foreach($results as $row) {
    $temp[$row['main']]['text'] =  $row['main'];
  if(!isset($temp[$row['main']]['children'])) {
      $temp[$row['main']]['children'] = array();
  }
  array_push($temp[$row['main']]['children'], array(
    'id' => $row['mcondition_pk'],
    'text' => $row['mcondition_name'] 
  ));
}

$temp = array_values($temp);
echo json_encode($temp);

This is what the JSON currently looks like:

[
  {
    "text": "Main Heading 1",
    "children": [
      {
        "id": "1",
        "text": "mcondition_1"
      },
      {
        "id": "17",
        "text": "mcondition_4"
      }
    ]
  },
  {
    "text": "Main Heading 2",
    "children": [
      {
        "id": "49",
        "text": "mcondition_2"
      },
      {
        "id": "48",
        "text": "mcondition_5"
      }
    ]
  },
  {
    "text": "Main Heading 3",
    "children": [
      {
        "id": "68",
        "text": "mcondition_3"
      },
      {
        "id": "67",
        "text": "mcondition_6"
      }
    ]
  }
]

This is the structure of the table mcondition:

    +---------------+------------+------+-----------+
    | mcondition_pk | mcondition | main | secondary |
    +---------------+------------+------+-----------+

Column mcondition is unique.

I am a little confused on what the output json is supposed to look like. Perhaps you could provide a sample of a desired json item, and also a sample of the source db table or the structure for it. I'm also unclear why you don't create each item of the temp array all at once, instead of with the isset to check if the children element exists, creating it if it doesn't, and then pushing mcondition_ to it. Is main unique? If it's not what's supposed to happen with the duplicates? If it is then I don't know that the check is necessary, just create the whole array element at once. — Chris Strickland
– Chris Strickland, Commented Jul 31, 2021 at 11:43
@ChrisStrickland see updated OP. mcondition is unique, and will always have a grandparent (main) and parent (secondary) headings in the Select2 — IlludiumPu36
– IlludiumPu36, Commented Aug 1, 2021 at 0:07
@IlludiumPu36 can you add the expected JSON you're looking for? — Omi Shah
– Omi Shah, Commented Aug 1, 2021 at 0:17

Chris Strickland · Accepted Answer · 2021-08-01 19:26:45Z

I'm still not 100% sure what your expected json would look like, but you should be able to do something like this. You may need to make some modifications to get exactly what you're wanting. I'm using an array $results to simulate the data acquisition from the database. This will get you a nested hierarchical structure.

$out = [];

$results = [
    ['main' => 'mk1', 'secondary' => 'sk1', 'mcondition_pk' => 1, 'mcondition' => 'mcondition_1'],
    ['main' => 'mk1', 'secondary' => 'sk2', 'mcondition_pk' => 2, 'mcondition' => 'mcondition_2'],
    ['main' => 'mk1', 'secondary' => 'sk2', 'mcondition_pk' => 3, 'mcondition' => 'mcondition_3'],
    ['main' => 'mk2', 'secondary' => 'sk3', 'mcondition_pk' => 4, 'mcondition' => 'mcondition_4'],
    ['main' => 'mk2', 'secondary' => 'sk3', 'mcondition_pk' => 5, 'mcondition' => 'mcondition_5'],
    ['main' => 'mk3', 'secondary' => 'sk4', 'mcondition_pk' => 6, 'mcondition' => 'mcondition_6'],
    ['main' => 'mk3', 'secondary' => 'sk5', 'mcondition_pk' => 7, 'mcondition' => 'mcondition_7'],
    ['main' => 'mk3', 'secondary' => 'sk5', 'mcondition_pk' => 8, 'mcondition' => 'mcondition_8'],
];

foreach($results as $row) {
    
    $mk = $row['main'];
    $sk = $row['secondary'];
    $pk = $row['mcondition_pk'];
    $cond = $row['mcondition'];
    
    $temp = ['id' => $pk, 'text' => $cond];
    
    if(!isset($out[$mk])) { $out[$mk] = ['text' => $mk]; }
    if(!isset($out[$mk][$sk])) { $out[$mk][$sk] = ['text' => $sk, 'children' => []]; }
    
    $out[$mk][$sk]['children'][] = $temp;
    
}

echo "<pre>"; print_r($out); echo "</pre>"; 

$json = json_encode($out);
echo "<pre>"; print_r($json); echo "</pre>"; exit;

Collectives™ on Stack Overflow

PHP/MySQL - Nested groups in JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related