How to make multilevel JSON using PHP and MySQL?

Question

PHP

What is the easiest way to generate such JSON

{
  "response": {
    "data": [
      {
        "month": "2014-07-01",  // данные групируються по месяцам, а это собственно месяц
        "managers": [
          {
            "name": "Dixie Powell",
            "deal_count": 23,
            "sale_sum": 2853
          },
          ...
        ]
      },
      ...
    ]
  }
}

from MySQL data table, for example:
id, date, manager_name, sale_total
1, '2014-07-02 11:52:00', 'Dixie Powell', 200
2, '2014-07-05 13:37:00', 'Dixie Powell', 3000
3, '2014-07-05 12:45:00', 'Elma Hester', 3029
....
Solution:
Get all months:

SELECT 
    DATE_FORMAT(`date`, '%Y-%m-01') AS `Месяц`
FROM
    manager_sales
GROUP BY YEAR(`date`) , MONTH(`date`)
ORDER BY `Месяц`

Then, in a PHP loop for each month, make a query to MySQL: and give me information on all sellers for this month.
And if the sellers still have some investments? Is that how it's done?

Answer 1

SELECT 
          DATE_FORMAT(`date`, '%Y-%m') AS months, 
          `manager_name`, 
          COUNT(`manager_name`) AS deal_count, 
          SUM(`sale_total`) AS sale_sum
FROM `manager_sales`
GROUP BY  DATE_FORMAT(`date`, '%Y-%m'), `manager_name`
ORDER BY `date`,  `manager_name`

Almost necessary lines
will be obtained. All that remains is to fill the array and do json_encode

Answer 2

Load dynamically on click I recently encountered. Killed two days for a solution, but did not find it. Decided in this way. In the end, not every time you need to look at the statistics for the past months, so it makes no sense to get the data right away. And cycles are somehow tough, especially if there is a lot of data.