CODEWITHSUNDEEP
phpsql-serverjsonhighcharts
nielskildsgaard
nielskildsgaard

Reputation: 29

HighCharts Month/multiple data array error

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

Answers (2)

nielskildsgaard
nielskildsgaard

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

Meherzad
Meherzad

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

Related Questions