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?