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

Please add your code and error you faced. Minimal, Complete and Verifiable — Shubham
– Shubham, Commented Aug 2, 2018 at 10:23

deceze · Accepted Answer · 2018-08-02 10:27:41Z

2

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

answered Aug 2, 2018 at 10:27

deceze♦

525k89 gold badges806 silver badges954 bronze badges

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

2 Comments

Abhijeetc50 Over a year ago

whats in $databaseResult?

deceze Over a year ago

Whatever your database results are which you did not show at the time I answered the question. Substitute your own details.

Collectives™ on Stack Overflow

Creating nested JSON with mysql and PHP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related