Creating a Google Line Chart using a passed JSON array

Question

I have a JSON array passed via ajax and am having issues charting the data. I need to be able to pass the array to the chart dynamically adding columns without duplicates.

I've tried passing different values using arrayToDataTable() and DataTable() then using the data.addColumns() and data.addRow() however each time errors.

ajax call fired:

$.ajax({
    type: "POST",
    url: "/file.php",
    data: {
        data: dataHere,
    },
    dataType: "JSON",
    success: function(result) {
        var div = mydiv;
        drawInterfaceChart(result,div);
    },
});

JSON encoded from PHP using:

$SQL = "SELECT DATE_FORMAT(date, 'new Date(%Y, %c, %d, %H, %i, %s)') as date, ifDesc, ifInOctets FROM tablename WHERE date between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE()";
$Results = mysqli_query($db, $SQL) or die("Mysql cannot run Query");
$SQLRows = mysqli_num_rows($Results);
if ($SQLRows > 0) {
    $rows = array();
    while ($row = MySQLi_fetch_assoc($Results)) {
        $rows[] = $row;
    }
    echo json_encode($rows);
}

Example of a passed JSON array:

0: {date: "new Date(2019, 3, 26, 16, 13, 15)", ifDesc: "lo", ifInOctets: "2147483647"}
1: {date: "new Date(2019, 3, 26, 16, 13, 15)", ifDesc: "Port1", ifInOctets: "2147483647"}
2: {date: "new Date(2019, 3, 26, 16, 13, 15)", ifDesc: "Port2", ifInOctets: "2147483647"}
3: {date: "new Date(2019, 3, 26, 16, 13, 16)", ifDesc: "Port6", ifInOctets: "2147483647"}
4: {date: "new Date(2019, 3, 26, 16, 13, 16)", ifDesc: "imq0", ifInOctets: "906413834"}
5: {date: "new Date(2019, 3, 26, 16, 17, 31)", ifDesc: "lo", ifInOctets: "2147483647"}
6: {date: "new Date(2019, 3, 26, 16, 17, 49)", ifDesc: "Port1", ifInOctets: "2147483647"}
7: {date: "new Date(2019, 3, 26, 16, 17, 53)", ifDesc: "Port2", ifInOctets: "171330279"}
8: {date: "new Date(2019, 3, 26, 16, 17, 57)", ifDesc: "Port6", ifInOctets: "2147483647"}
9: {date: "new Date(2019, 3, 26, 16, 17, 57)", ifDesc: "imq0", ifInOctets: "1103910085"}
10: {date: "new Date(2019, 3, 26, 16, 20, 38)", ifDesc: "lo", ifInOctets: "2147483647"}
11: {date: "new Date(2019, 3, 26, 16, 20, 39)", ifDesc: "Port1", ifInOctets: "2147483647"}
12: {date: "new Date(2019, 3, 26, 16, 20, 40)", ifDesc: "Port2", ifInOctets: "194386054"}
13: {date: "new Date(2019, 3, 26, 16, 20, 41)", ifDesc: "Port6", ifInOctets: "2147483647"}
14: {date: "new Date(2019, 3, 26, 16, 20, 42)", ifDesc: "imq0", ifInOctets: "1128562685"}

My draw chart function:

function drawInterfaceChart(array,divR) {
    var dataSet = [];
    $.each(array, function (data, value) {
        dataSet.push([value.date, value.ifDesc, value.ifInOctets]);
    });
    var data = google.visualization.arrayToDataTable(dataSet);
    var chart = new google.visualization.LineChart(document.getElementById(divR));
    var options = {
        title: '',
        legend: { position: 'right' }
    };
    chart.draw(data, options);
}

This layout returns the error: Data column(s) for axis #0 cannot be of type string

Column 0 needs to be a datetime, and a line chart presented with each column and it's data per 'ifDesc' value. I am looking to add more data into the chart so creating the columns needs to be dynamic.

Thanks in advance!

EDIT:

I forgot to mention my page does include: google.charts.load('current', {'packages':['corechart', 'gauge']}); and I have other working charts on the same page.

WhiteHat · Accepted Answer · 2019-03-28 12:26:23Z

there are a few issues here, first the date.

you won't be able to get an actual datetime from this string --> "new Date(2019, 3, 26, 16, 13, 15)"
without using the eval method, which I wouldn't recommend.

on top of that, in javascript, when using this particular date constructor,
the month is zero based, meaning --> January = 0

as such, the above date would result as April 26, I'm thinking you want March 26.

here's proof (run the following snippet)...

var testDate = eval("new Date(2019, 3, 26, 16, 13, 15)");
console.log(testDate);

recommend changing the format to --> 3/26/2019 16:13:15
{date: "3/26/2019 16:13:15", ifDesc: "lo", ifInOctets: "2147483647"}
then convert to datetime in javascript --> new Date(value.date)

next, the chart expects each ifDesc to be in its own column in the data table,
as in the following structure...

['Date', 'lo', 'Port1', 'Port2', 'Port6', 'img0'],
[new Date('3/26/2019 16:13:15'), 2147483647, 2147483647, 2147483647, 2147483647, 906413834],

this will be difficult to build in the query, without hard-coding.

instead, we can use google's data view to create the structure needed.

pass the json via ajax in the following structure.

{date: "3/26/2019 16:13:15", ifDesc: "lo", ifInOctets: "2147483647"}

load the data table similar to what you have done,
we need to convert the date and parse the number...

var dataSet = [];
$.each(array, function (data, value) {
    dataSet.push([new Date(value.date), value.ifDesc, parseFloat(value.ifInOctets)]);
});
var data = google.visualization.arrayToDataTable(dataSet);

then use the following to create a data view for the line chart.
it first creates a column for each ifDesc.

var viewColumns = [0];
var distinctLabels = data.getDistinctValues(1);
$.each(distinctLabels, function (index, label) {
  viewColumns.push({
    calc: function (dt, row) {
      if (dt.getValue(row, 1) === label) {
        return dt.getValue(row, 2);
      }
      return null;
    },
    type: 'number',
    label: label
  });
});
var view = new google.visualization.DataView(data);
view.setColumns(viewColumns);

see following working snippet...

google.charts.load('current', {
  packages:['corechart']
}).then(function () {
  var jsonData = [
    {date: "3/26/2019 16:13:15", ifDesc: "lo", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:13:15", ifDesc: "Port1", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:13:15", ifDesc: "Port2", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:13:16", ifDesc: "Port6", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:13:16", ifDesc: "imq0", ifInOctets: "906413834"},
    {date: "3/26/2019 16:17:31", ifDesc: "lo", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:17:49", ifDesc: "Port1", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:17:53", ifDesc: "Port2", ifInOctets: "171330279"},
    {date: "3/26/2019 16:17:57", ifDesc: "Port6", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:17:57", ifDesc: "imq0", ifInOctets: "1103910085"},
    {date: "3/26/2019 16:20:38", ifDesc: "lo", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:20:39", ifDesc: "Port1", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:20:40", ifDesc: "Port2", ifInOctets: "194386054"},
    {date: "3/26/2019 16:20:41", ifDesc: "Port6", ifInOctets: "2147483647"},
    {date: "3/26/2019 16:20:42", ifDesc: "imq0", ifInOctets: "1128562685"}
  ];

  drawInterfaceChart(jsonData, 'chart_div');

  function drawInterfaceChart(array, divR) {
    var dataSet = [];
    $.each(array, function (data, value) {
      dataSet.push([new Date(value.date), value.ifDesc, parseFloat(value.ifInOctets)]);
    });
    var data = google.visualization.arrayToDataTable(dataSet, true);

    var viewColumns = [0];
    var distinctLabels = data.getDistinctValues(1);
    $.each(distinctLabels, function (index, label) {
      viewColumns.push({
        calc: function (dt, row) {
          if (dt.getValue(row, 1) === label) {
            return dt.getValue(row, 2);
          }
          return null;
        },
        type: 'number',
        label: label
      });
    });
    var view = new google.visualization.DataView(data);
    view.setColumns(viewColumns);

    var chart = new google.visualization.LineChart(document.getElementById(divR));
    var options = {
      title: '',
      legend: {position: 'right'},
      interpolateNulls: true  // <-- add this option
    };
    chart.draw(view, options);  // <-- use view to draw chart
  }
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

Amazing work, Thank you so much for your very detailed answer - This works exactly how I needed

Collectives™ on Stack Overflow

Creating a Google Line Chart using a passed JSON array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related