Google charts continuous x-axis from php array

Question

i use some code that works fine for producing a google line chart with a discreet x axis from data in a mysql table. But when i change the column type to "date" to make the x axis continous i just keep getting an error:

a.getTime is not a function

here is the code for producing the array from mysql.

    <?php
include("connect.php");
$qry = " mysql query here";
$result = mysqli_query($con,$qry);
mysqli_close($con);

$table = array();
$table['cols'] = array(

array('id' => '', 'label' => 'Date', 'type' => 'date'),
array('id' => '', 'label' => 'Amount ', 'type' => 'number'),
); 
$rows = array();
foreach($result as $row){
$temp = array();

$date1 = date_create_from_format('Y-m-d H:i:s', $row['Date']);
$date2 = date_format($date1, 'd-m-Y');
$temp[] = array('v' => (string) $date2);
$temp[] = array('v' => (float) $row['Amount']);
$rows[] = array('c' => $temp);
}

$result->free();
$table['rows'] = $rows;
$jsonTable = json_encode($table, true);
echo $jsonTable;
?>

Ive tried various things like date format, etc. but still get the same error if the cloumn 1 type is "date". Any help would be appreciated.

Edit: Still having trouble with this. I cant get my head around it. The following is the array output im getting at the minute. Hope this helps point where im going wrong.

Edit 2: I now have the array coming out as follows but still have the same a.getTime error.

{"cols":[
{"label":"Reading Date","type":"date"},
{"label":"Cl Reading(mg\/l) ","type":"number"},
"rows":[
{"c":[{"v":"new Date(04\/10\/2015)"},{"v":0.4}]},
{"c":[{"v":"new Date(04\/11\/2015)"},{"v":0.45}]},
{"c":[{"v":"new Date(04\/12\/2015)"},{"v":0.9}]},
{"c":[{"v":"new Date(04\/01\/2016)"},{"v":0.5}]},
{"c":[{"v":"new Date(04\/02\/2016)"},{"v":0.43}]},
{"c":[{"v":"new Date(18\/02\/2016)"},{"v":0.6}]}]}

WhiteHat · Accepted Answer · 2016-02-18 18:40:39Z

2

The error is thrown because the values in the first column need to be actual date values.

Try replacing...

{"c":[{"v":"04-10-2015"},{"v":0.4}]}

With...

{"c":[{"v":new Date("10/04/2015")},{"v":0.4}]}

Using...

$date1 = date_create_from_format('Y-m-d H:i:s', $row['Date']);
$date2 = "new Date(\"".date("m",$date1)."/".date("d",$date1)."/".date("Y",$date1)."\")";
$temp[] = array('v' => (string) $date2);

google.charts.load('current', {
  packages: ['corechart'],
  callback: drawChart
});

function drawChart() {
  var json = {
    "cols":[
      {"label":"Reading Date","type":"date"},
      {"label":"Cl Reading(mg\/l) ","type":"number"}
    ],
    "rows":[
      {"c":[{"v":new Date("10/04/2015")},{"v":0.4}]},
      {"c":[{"v":new Date("11/04/2015")},{"v":0.45}]},
      {"c":[{"v":new Date("12/04/2015")},{"v":0.9}]},
      {"c":[{"v":new Date("01/04/2016")},{"v":0.5}]},
      {"c":[{"v":new Date("02/04/2016")},{"v":0.43}]},
      {"c":[{"v":new Date("02/18/2016")},{"v":0.6}]}
    ]
  }

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

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
  chart.draw(data, {});
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

edited Feb 18, 2016 at 18:40

answered Feb 2, 2016 at 15:10

WhiteHat

61.3k7 gold badges54 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3726827 Over a year ago

Hi thanks for the reply, still having trouble. Ive edited my original question to show the array im getting from the above code.

user3726827 Over a year ago

thanks, but still getting the same error. I have updated the array output as you said above.

user3726827 Over a year ago

brilliant, your example is working for me too.just one little thing left. what do i have to change >$temp[] = array('v' => (string) $date2); to to get it to output that way?

user3726827 · Accepted Answer · 2016-02-23 09:56:50Z

1

Thanks for all the help WhiteHat, here is the working solution:

<?php

include("connect.php");
$qry = " mysql query here";
$result = mysqli_query($con,$qry);
mysqli_close($con);


    $table = array(
    'cols' => array(
        array('id' => '', 'label' => 'Date', 'type' => 'date'),
        array('id' => '', 'label' => 'Amount ', 'type' => 'number'),

    ),
    'rows' => array(),
);
foreach($result as $row){
    $date1 = date_create_from_format('Y-m-d H:i:s', $row['Date']);
    $date2 = date_format($date1, 'Y, m, d'); 
    $table['rows'][] = array(
        'c' => array(
            array('v' => 'Date('.$date2.')'), 
            array('v' => (float) $row['Amount'])
        )
    );
}

$result->free();
$jsonTable = json_encode($table, true);
echo $jsonTable;

    ?>

answered Feb 23, 2016 at 9:56

user3726827

658 bronze badges

1 Comment

Peter Over a year ago

Thanks to @WhiteHat and @user3726827. I adjusted $date2 this way to make chart visible: $date2 = "Date(" . date_format($date1, 'Y, m, d') . ")";. According to [groups.google.com/d/msg/google-visualization-api/DhqFqPJQQv0/… Date objects are not valid inside JSON strings. The Visualization API uses a custom string format for handling dates; you need to remove the "new" keyword and wrap the "Date(...)" in quotes: {"v":"Date(2013,10,10,01,46,0)","f":null}. I can't explain why Google Charts does not provide this essentials in their documentation.

Collectives™ on Stack Overflow

Google charts continuous x-axis from php array

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related