Google Chart draw Trendlines with date on x axis

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

More Related Contents:

Leave a Comment Cancel reply