CODEWITHSUNDEEP
phpjavascriptmysqlhighcharts
frietkot
frietkot

Reputation: 911

Graphing data from mysql

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!

Upvotes: 1

Views: 2081

Answers (3)

bandrzej
bandrzej

Reputation: 533

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.

Upvotes: 2

dogmatic69
dogmatic69

Reputation: 7575

group by account_id will count everything together by the account

update:

group by date, account_id

Upvotes: 0

Eivind
Eivind

Reputation: 841

So basically you will have an X-axis that consists of dates, and the Y-axis will consist of count, on a per account basis.

The easiest is to generate this by the common axis for the data, in this case the dates. Hence you need to loop through the dates in the timerange you want for the graph, and map the counts to those days. Ending up in a dataset that might look like this:

series : [
{name: 'ac1', data: [0,1,5,0,0,0,0,2,3]}, 
{name: 'ac2', data: [0,0,2,1,0,1,0,0,0]}
]

Ending up with an example like this: http://jsfiddle.net/55dvF/4/

Hope that helps.

Upvotes: 0

Related Questions