从mysql图形化数据

Question

I have a mysql result sorted on date and grouped by date and id.

SELECT
  DATE(`created_at`) AS `date`,
  COUNT(`id`) AS `count`,
  `account_id`
FROM `streamaccounts`
GROUP BY `date`, `account_id`
ORDER BY `date`;

And the result is this:

date, count, account_id
2011-03-23, 2, 1
2011-03-23, 1, 6
2011-04-25, 1, 1
2011-04-26, 5, 6

If you ask me, that's the right result set for using it to make a graph with highcharts. But I into the following problem.

I cannot figure out how to create the right result for highcharts. The result has to look something like this:

series: [{name: 'account1', data: [2, 1]}, {name: 'account2', data: [1, 5}]

It will all be programmed in PHP and echo'd out to print out valid javascript. What do I do? Will I need to loop through all dates first and check if in that date is a valid result? Or do I loop through all the accounts first and check for a valid date.

Any suggestions?

Thanks in advance!

Answer 1

It has been way easier for me to get Highchart to do the data processing work from SQL queries by converting the data to one of the following formats:

1. Convert an HTML table (this is helps meet accessibility standards) demo here: http://www.highcharts.com/studies/table-parser.htm
2. Using AJAX, read in a CSV file - demo here: http://www.highcharts.com/studies/data-from-csv.htm

Since your query is already doing a CSV file, just follow the example for #2.