CODEWITHSUNDEEP
mysqlhighcharts
amnmachado
amnmachado

Reputation: 3

Highcharts - Series data from MySQL in the expected format

I'm having some troubles working with highcharts.

I want to generate a chart similar to this one : http://www.highcharts.com/demo/column-basic

My problem relates to the series data. I have some SQL queries, one for the Categories (where I fetch distinct data) and other to fetch the results for the series, namely, some X and Y value.

My data from the database has the following format (this is the actual result from the database):

Year Value
2012 9747600000
2012 9358200000
2013 9494830000
2013 9459250000
2013 9478030000
2013 9592300000
2013 9535060000

As far as I can understand, highcharts expects something in this format:

Name: 2012
Data: [9747600000, 9358200000]
Name: 2013
Data: [9747600000, 9358200000]

How can I generate such format directly from MySQL? What's the better way to do this?

Thanks in advance!

EDIT

I thought that the accepted solution worked as I wanted, but I'm still having trouble passing this information to Highcharts. Although the query works as expected, I can't return an array with the following format:

[{
name: 'Tokyo',
data: [49.9, 71.5, 106.4]

}, {
name: 'New York',
data: [83.6, 78.8, 98.5]

}]

Any ideas to solve this problem? Thanks!

Upvotes: 0

Views: 955

Answers (2)

Infant Rosario
Infant Rosario

Reputation: 149

You can run a foreach loop and put it an array like 

`<?php
$newArray = array();
 foreach ($result  as $year=>       $value) {
   $newArray[$year][ ] = $value;
 }
 Print_r($newArray);
?>`

Upvotes: 1

RBB08
RBB08

Reputation: 126

You can try query below:

SELECT YR, GROUP_CONCAT(VAL)
FROM TimeTable
GROUP BY YR;

This will get distinct year and values are concatenated as one (comma separated).

Here is the sample SQL Fiddle: http://sqlfiddle.com/#!2/7859c/1

Upvotes: 1

Related Questions