title: PHP/MySQL - Nested groups in JSON
body: <p>I'm creating nested (hierarchical) groups in JSON.  The structure is simply - <code>grand parent -&gt; parent -&gt; child</code>:</p>
  <pre><code>main
    secondary
      mcondition
  </code></pre>
  <p>This is using the following MySQL/PHP to so far format <code>main -&gt; mcondition</code>.</p>
  <p>How should I change this to add the second level ('secondary' e.g. the parent level) between main and mcondition?</p>
  <p>The column for secondary is <code>mcondition.secondary</code></p>
  <pre><code>$query = 'SELECT * FROM mcondition ORDER BY mcondition.main ASC';
  $result = $connection-&gt;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' =&gt; $row['mcondition_pk'],
      'text' =&gt; $row['mcondition_name'] 
    ));
  }
  
  $temp = array_values($temp);
  echo json_encode($temp);
  </code></pre>
  <p>This is what the JSON currently looks like:</p>
  <pre><code>[
    {
      &quot;text&quot;: &quot;Main Heading 1&quot;,
      &quot;children&quot;: [
        {
          &quot;id&quot;: &quot;1&quot;,
          &quot;text&quot;: &quot;mcondition_1&quot;
        },
        {
          &quot;id&quot;: &quot;17&quot;,
          &quot;text&quot;: &quot;mcondition_4&quot;
        }
      ]
    },
    {
      &quot;text&quot;: &quot;Main Heading 2&quot;,
      &quot;children&quot;: [
        {
          &quot;id&quot;: &quot;49&quot;,
          &quot;text&quot;: &quot;mcondition_2&quot;
        },
        {
          &quot;id&quot;: &quot;48&quot;,
          &quot;text&quot;: &quot;mcondition_5&quot;
        }
      ]
    },
    {
      &quot;text&quot;: &quot;Main Heading 3&quot;,
      &quot;children&quot;: [
        {
          &quot;id&quot;: &quot;68&quot;,
          &quot;text&quot;: &quot;mcondition_3&quot;
        },
        {
          &quot;id&quot;: &quot;67&quot;,
          &quot;text&quot;: &quot;mcondition_6&quot;
        }
      ]
    }
  ]
  </code></pre>
  <p>This is the structure of the table mcondition:</p>
  <pre><code>    +---------------+------------+------+-----------+
      | mcondition_pk | mcondition | main | secondary |
      +---------------+------------+------+-----------+
  </code></pre>
  <p>Column <code>mcondition</code> is unique.</p>