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

Question

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

Answer 1

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