Creating nested JSON with mysql and PHP

Question

So, I have been trying to create a nested JSON in which the data will come from the MYSQL.

Got this SQL data after writing a long query-

 +-------------+--------+-------+-------+
 | Type        | month  | Year  | Total | 
 +-------------+--------+-------+-------+
 | AR          | April  | 2018  | 23443 |
 +-------------+--------+-------+-------+
 | AP          | April  | 2018  | 11456 |
 +-------------+--------+-------+-------+
 | AR          | May    | 2018  | 26483 |
 +-------------+--------+-------+-------+
 | AR          | May    | 2018  | 14442 |
 +-------------+--------+-------+-------+

Need to create this JSON -

    [
       {
        "categorie": "April 2018", 
         "values": [
             {
               "value": 23443, 
               "rate": "AR"
             }, 
             {
               "value": 11456, 
               "rate": "AP"
             }
          ]
       }, 
  .
  .
  .
  ]

Been banging my head since morning on this, but got no solution. Got this answer in SO - Create nested json object using php mysql, but its using 2 queries from SQL to get the data.

Need help with creating the PHP file which will generate JSON.

include '../config/config.php';
if(isset($_GET['sub_cat_id']))
{
         $sub_cat_id = $_GET['sub_cat_id']; 
        $result = mysql_query("SELECT 'AR' as Type,month(DocumentDate) as PeriodM, year(DocumentDate) as PeriodY, sum(Amount) as Total from custledgerentry group by PeriodY,PeriodM union all select 'AP' as Type,month(DocumentDate) as PeriodM, year(DocumentDate) as PeriodY, sum(Amount) as Total from vendledgerentry group by PeriodY,PeriodM"); 
        $json_response = array();
        $i=1;
                        while ($row = mysql_fetch_array($result))
                        {
                        $row_array['categorie'] = $row['month'];        
                        $row_array['value'] = $row['question']; 



        echo json_encode($row_array);
}

Answer 1

This becomes simple when you use the value you want to group by as an array key:

$results = [];

foreach ($databaseResult as $row) {
    $category = "$row[month] $row[Year]";

    if (!isset($results[$category])) {
        $results[$category] = ['category' => $category, 'values' => []];
    }

    $results[$category]['values'][] = ['rate' => $row['Type'], 'value' => $row['Total']];
}

echo json_encode(array_values($results));