创建没有多个SQL查询的JSON对象（PHP）

I'm trying to create this kind of JSON object by using MySQL/ PHP.

[ 
{
  "hours":0
  "vulnerability":867
  "file":166
  "virus":59
}, 
{
  "hours":1
  "vulnerability":400
  "file":14
  "virus":40
}, 
]

I'm trying to reduce the amount of queries sent to the server as my db scales quite large. My query returns these results:

So while looping through the data I'm getting this kind of JSON returned:

{
  "hours":0
  "vulnerability":867
}, 
{
  "hours":0
  "file":14
}, 
{
  "hours":0
  "virus":59
}, 
]

I would like to create the desired output without using multiple SQL queries in a foreach loop. Full code below-

$query = "SELECT hour(generated_time) as hours, subtype, count(subtype) as y from description group by subtype, hours order by hours asc, y desc";
$result = mysql_query($query) or die(mysql_error());

$output = array();
$data = array();

while ($row = mysql_fetch_assoc($result)) {
    $data["time"] => $row['hours'];
    $data[$row['subtype']] => $row['y'];
    array_push($output, $data);
}

echo json_encode($output);

Just run the SQL query once, and loop over the results and put each row in its correct spot.

Something like this:

$output = array();

while ($row = mysql_fetch_assoc($result)) {
    $key = intval($row['hours']);
    if(!isset($output[$key])){
        $output[$key] = array('hours' => $key);
    }
    $output[$key][$row['subtype']] = intval($row['y']);
}

echo json_encode($output);

It is only one mySQL query. It is put into the associative array and the loop isn't calling more mySQL queries but instead looking through the entry in the associative array in the loop.

If you know all of the subtypes ahead of time you can rewrite the query to format the data the way you want it:

SELECT
    hour(generated_time) as hours,
    SUM(subtype = "vulnerability") AS vulnerability,
    SUM(subtype = "file") AS file,
    SUM(subtype = "virus") AS virus
FROM
    description
GROUP BY hours