Google Chart draw Trendlines with date on x axis

I need to draw a Google Line Chart with Trendlines, but I use an Ajax request to get data to put inside it

In the ajax file, I insert in one array the elements of the graphic and after I convert it in JSON like that:

$reply= json_encode($array);
echo $reply;

This is the content of my ajax reply:

reply = [
    ["Periodo","Rome","Milan","Test"],
    ["20160830",1,2,3],
    ["20160831",2,3,6],
    ["20160901",2,3,20],
    ["20160902",20,30,12]
  ];

In my program I populate the graphic like that:

var replyJSON = JSON.parse(reply);
var data = google.visualization.arrayToDataTable(replyJSON);

This is a similar example of what I have and what I would to do, but here I can't replicate the ajax call: http://jsfiddle.net/roby492/srrrn9sa/384/

I need to get reply JSON, convert the string of date in jquery date to show correctly the trendlines.

How can I do this? Or how Can I send by Ajax the JSON with date instead of a string?

Thank you. Roberto R.

take a look at this php to google chart via ajax example

recommend similar setup here

the example builds JSON that is acceptable by google

which allows creation of the DataTable, directly from the JSON

following is a snippet from that answer, that builds the JSON

$rows = array();
$table = array();

$table['cols'] = array(
    array('label' => 'Time', 'type' => 'string'),
    array('label' => 'Wind_Speed', 'type' => 'number'),
    array('label' => 'Wind_Gust', 'type' => 'number')
);

while ($row = mysql_fetch_assoc($sqlResult)) {
  $temp = array();
  $temp[] = array('v' => (string) $row['Time']);
  $temp[] = array('v' => (float) $row['Wind_Speed']);
  $temp[] = array('v' => (float) $row['Wind_Gust']);
  $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

echo json_encode($table);

to use an actual Date column in the JSON, gets a little tricky

primarily because month numbers in JavaScript are zero-based, unlike php

so if you format a Date in php, need to reduce the month number by 1

which makes for a lengthy format statement

to pass a date in JSON, you can use the following format, note that it is passed as a string...

"Date(2016, 8, 28, 15, 34, 40)" --> which equates to today's date
again, the month is zero-based (8 = September)

so to build a date in this format in php, you can use the following...

$date1 = new DateTime();
$date2 = "Date(".date_format($date1, 'Y').", ".((int) date_format($date1, 'm') - 1).", ".date_format($date1, 'd').", ".date_format($date1, 'H').", ".date_format($date1, 'i').", ".date_format($date1, 's').")";

which produces the "Date(...)" shown above

adjusting the snippet from the other answer to include a Date column, might look something like this...

$rows = array();
$table = array();

$table['cols'] = array(
    array('label' => 'Date', 'type' => 'date'),
    array('label' => 'Wind_Speed', 'type' => 'number'),
    array('label' => 'Wind_Gust', 'type' => 'number')
);

while ($row = mysql_fetch_assoc($sqlResult)) {
  $date1 = $row['Date'];
  $date2 = "Date(".date_format($date1, 'Y').", ".((int) date_format($date1, 'm') - 1).", ".date_format($date1, 'd').", ".date_format($date1, 'H').", ".date_format($date1, 'i').", ".date_format($date1, 's').")";

  $temp = array();
  $temp[] = array('v' => (string) $date2);
  $temp[] = array('v' => (float) $row['Wind_Speed']);
  $temp[] = array('v' => (float) $row['Wind_Gust']);
  $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

echo json_encode($table);

getting the above results via ajax from php, you can create the table directly

var data = new google.visualization.DataTable(reply);

no need to parse the JSON or use arrayToDataTable

Google Chart draw Trendlines with date on x axis

Related

Recent Posts