Reputation: 29
My problem is as follows:
I have a database that gives me two sets of information as shown in the picture below:
https://i.sstatic.net/ijshg.png
I can't quite figure out if my PHP/JSON or SQL is the problem here.. Im aware that my JSON data-sets should have more than one data, eg:
[{"name":"Person1","data":[123123, etc, etc , etc]}
My problem is that it is showed as:
[{"name":"Person1","data":[123123]}
[{"name":"Person1","data":[2345436]}
JSON (changed names and numbers for privacy issues):
[{"name":"Person1","data":[123123]},{"name":"Person1","data":[123123]}
,{"name":"Person2","data":[123123]},{"name":"Person2","data":[123123]}
,{"name":"Person3","data":[123123]},{"name":"Person3","data":[123123]}
,{"name":"Person4","data":[123123]},{"name":"Person4","data":[123123]}]
SQL (also changed, should be the same query though):
$SQL = "SELECT ISNULL(USERNAME) AS Accountmanagers, xMonth AS xMonth, ROUND(SUM(report.[FATDOLLARS(EUR)]), 0) AS COGS
FROM SecretDB
WHERE xYear = '2013'
GROUP BY ISNULL(USERNAME), xMonth
ORDER BY ISNULL(USERNAME), xMonth
my query looks like this:
https://i.sstatic.net/BAz7I.png
This is the php i use for my JSON data:
while( $row = sqlsrv_fetch_array( $somevariablehere, SQLSRV_FETCH_ASSOC) )
{
$name = $row['Accountmanagers'];
$categories = array($row['xMonth']);
$data = array($row['COGS']);
$arr[] = array('name' => $name, 'data' => $data);
}
$arr = json_encode($arr, JSON_NUMERIC_CHECK);
echo $arr;
HighChart ("series: json" is replaced with dummy data):
<script>
$(function () {
var chart3;
$(document).ready(function() {
// $.getJSON("account.php", function(json) {
chart3 = new Highcharts.Chart({
chart: {
renderTo: 'accountmanagers',
type: 'column'
},
title: {
text: '2012'
},
xAxis: {
categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Okt', 'Nov', 'Dec'],
title: { text: 'Month'}
},
yAxis: {
min: 0,
title: {
text: 'Revenue'
}
},
legend: {
layout: 'vertical',
backgroundColor: '#FFFFFF',
align: 'left',
verticalAlign: 'top',
x: 100,
y: 70,
floating: true,
shadow: true
},
tooltip: {
formatter: function() {
return '<b>'+ this.series.name +'</b><br/>'+
this.x +': '+ this.y +'€';
}
},
plotOptions: {
column: {
pointPadding: 0.2,
borderWidth: 0
}
},
series: [{"name":"Person1","data":[12528]},{"name":"Person1","data":[2658]},{"name":"Person2","data":[27368]},{"name":"Person2","data":[30793]},{"name":"Person3","data":[64987]},{"name":"Person3","data":[21582]},{"name":"Person4","data":[53735]},{"name":"Person4","data":[14810]}]
});
// });
});
});
</script>
I don't know if you need any more information, if so, please let me know.
I want to combine the "Person" or Accountmanager values and of course spread them over the months, instead of creating new series for each data.
Thanks in advance :-)
Upvotes: 0
Views: 457
Reputation: 29
I found the error - it was the SQL query, i made a whole bunch of left outer joins, which (after many copy/paste's) gave me data for each month with one series.name to it.
Upvotes: 1
Reputation: 8563
You need to provide values for each user for all the months that you have mentioned in the categories. You are passing 2 values for each person that to separately that is why you are getting graph for only JAN
Ex
series: [{
name: 'Person1',
data: [49.9, 71.5, 106.4, 129.2, 144.0, 176.0, 135.6, 148.5, 216.4, 194.1, 95.6, 54.4]
}, {
name: 'Person2',
data: [83.6, 78.8, 98.5, 93.4, 106.0, 84.5, 105.0, 104.3, 91.2, 83.5, 106.6, 92.3]
}]
Sample fiddle provided by high charts
Upvotes: 0