Rendering complex JSON from mysql and use column value as label

Question

I have a table with 1000 records and a corresponding data history of 5 years, including events. The table structure looks like this at the moment:

id|date|reference_id|account_id|dataSet|price|title|type|description

1|2006-01-03|ID00001|1|dataSet01|44.23|Analyst opinion change|A|Upgrade by Bank from Sell to Hold
2|2006-01-03|ID00002|1|dataSet02|62.75|||
3|2006-01-03|ID00003|1|dataSet03|25.95|Dividend|D|Amount: 0.22
4|2006-01-03|ID00004|2|dataSet04|31.81|||
5|2006-01-03|ID00005|3|dataSet05|78.20|||
6|2006-02-01|ID00001|1|dataSet01|45.85|Dividend|D|Amount: 0.30
7|2006-02-01|ID00002|1|dataSet02|59.37||
8|2006-02-01|ID00003|1|dataSet03|27.59|Dividend|D|Amount: 0.26
9|2006-02-01|ID00004|2|dataSet04|34.24|||
10|2006-02-01|ID00005|3|dataSet05|83.42|||
11|2006-03-01|ID00001|1|dataSet01|45.54|Analyst opinion change|A|Upgrade by Bank from Sell to Hold
12|2006-03-01|ID00002|1|dataSet02|60.86|||
13|2006-03-01|ID00003|1|dataSet03|27.04|Downgrade by Bank from Buy to Hold
14|2006-03-01|ID00004|2|dataSet04|36.04|||
15|2006-03-01|ID00005|3|dataSet05|84.32|||

I want to render the data depending on account_id (in this case account_id = 1) to get the following JSON:

{
"data": [{
    "date": "2006-01-03",
    "dataSet01": "44.23",
    "dataSet02": "62.75",
    "dataSet03": "25.95"
}, {
    "date": "2006-02-01",
    "dataSet01": "45.85",
    "dataSet02": "59.37",
    "dataSet03": "27.59"
}, {
    "date": "2006-03-01",
    "dataSet01": "45.54",
    "dataSet02": "60.86",
    "dataSet03": "27.04"
}],
"events": [{
    "dataSet01": [{
        "date": "2006-01-03",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Upgrade by Bank from Sell to Hold"
    }, {
        "date": "2006-02-01",
        "title": "Dividend",
        "text": "D",
        "description": "Amount: 0.30"
    }, {
        "date": "2006-03-01",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Upgrade by Bank from Sell to Hold"
    }]
},{
    "dataSet03": [{
        "date": "2006-01-03",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Upgrade by Bank from Sell to Hold"
    }, {
        "date": "2006-02-01",
        "title": "Dividend",
        "text": "D",
        "description": "Amount: 0.30"
    }, {
        "date": "2006-03-01",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Downgrade by Bank from Buy to Hold"
    }]
}]
}

I'm struggling to build the json though. As of right now I'm rendering the data like this:

$query = "SELECT date, price 
FROM datatable
WHERE account_id = 1
ORDER BY date ASC";
$result = mysql_query( $query );

$data = array();
while ( $row = mysql_fetch_assoc( $result ) ) {
$data[] = $row;
}

return json_encode( $data );

Obviously this returns the json with price as label for each record value (price). How should the query look like instead to render the above json example?

Answer 1

$query = "SELECT * 
          FROM datatable
          WHERE account_id = 1
          ORDER BY date ASC";

$result = mysql_query( $query );

// Define temporary arrays
$data = array();
$events = array();

while ( $row = mysql_fetch_assoc( $result ) ) {

   // Assemble the data grouped by date and dataset
   if ( !isset($data[$row['date']]) )
   {
      $data[$row['date']] = array(
          'date' => $row['date'],
      );
   }

   // Inject dataSet in $data grouped by date
   if ( !isset($data[$row['date']][$row['dataSet']]) )
   {
      $data[$row['date']][$row['dataSet']] = $row['price'];
   }

   // Assemble events grouped by dataSet
   if ( !isset($events[$row['dataSet']]) )
   {
      $events[$row['dataSet']] = array();
   }

   $events[$row['dataSet']][] = array(
      'date' => $row['date'],
      'title' => $row['title'],
      'text' => $row['type'],
      'description' => $row['description']
   );
}

// Remove date keys
$data = array_values($data);

return json_encode(array(
    'data' => $data,
    'events' => $events
));