Google Charts Timeline - PHP array as input

Question

I am working on a Google Charts Timeline plot, showing the duration periods of a number of tasks, located in a table in a MySQL database. Using PHP there is a number of ways to retrieve and store this, for instance as a 2-dimensional array. However, is there a way to pass such an array (or other easily created data containing objects), in the way that the Google Charts Timeline can handle, through the dataTable.addrows() method?

Or if I should describe it as an example:

function drawChart() {
    // Nothing to see here, move along now...
    var container = document.getElementById('timeline');
    var chart = new google.visualization.Timeline(container);
    var dataTable = new google.visualization.DataTable();

    // The column heading can be fixed or variable, this doesnt matter.
    dataTable.addColumn({ type: 'string', id: 'Opgave ID' });
    dataTable.addColumn({ type: 'string', id: 'Opgavetitel' });
    dataTable.addColumn({ type: 'date', id: 'Start' });
    dataTable.addColumn({ type: 'date', id: 'End' });

    /* I would like to replace the fixed input of dataTable.addRows, with a        
     *variable input, for instance a 2-dimensional PHP-array.
     */
    dataTable.addRows([
            [ '1', 'Some task',        new Date(2015, 3, 9),  new Date(2015, 3, 23) ],
            [ '2', 'Another task',     new Date(2015, 3, 13), new Date(2015, 3, 20) ],
            [ '3', 'A different task', new Date(2015, 3, 16), new Date(2015, 3, 30) ]]);

    // No questions for any of this
    var options = {
            timeline: {showRowLabels: false, singleColor: '#8d8'}
    };

    chart.draw(dataTable, options);
}

It seems that my issue is not so much the general syntax, but rather an issue of how to format the date in PHP for it to became the right syntax after `json_encode`... — Jakob Busk Sørensen, Mar 11 '15 at 09:10
As the issue is of the date format only, I made a new post here on StackOverflow, where I specifically address this issue. Hopefully this will make it easier for both repliers and other people with the same issue. See: http://stackoverflow.com/questions/29004501/php-date-to-javascript-new-data-inside-array — Jakob Busk Sørensen, Mar 12 '15 at 07:59
I have the same problem, but with date and time. I tried some proposed solutions in this and the next your post https://stackoverflow.com/questions/29004501/php-date-to-javascript-new-data-inside-array but they don't work. Did you find the solution for this problem? If yes, can you share here as answer? — Sharunas Bielskis, Aug 31 '20 at 15:27
The solution which works is in the answer - https://stackoverflow.com/questions/36233412/using-timeline-google-chart-api-in-php-date-time-formatting-issues/36238497#36238497 — Sharunas Bielskis, Sep 01 '20 at 17:46

score 0 · Answer 1 · edited May 23 '17 at 12:06

Doing a quick google search, I found this article:

http://www.dyn-web.com/tutorials/php-js/json/multidim-arrays.php

I'm not a PHP programmer, so this isn't tested but should work:

<?
$timeData = array(
  array('1', 'Some task', new DateTime('2015-04-09'), new DateTime('2015-04-23')),
  array('2', 'Another task', new DateTime('2015-04-13'), new DateTime('2015-04-20')),
  array('3', 'A different task', new DateTime('2015-04-16'), new Date('2015-04-30))
);
?>
function drawChart() {
    // Nothing to see here, move along now...
    var container = document.getElementById('timeline');
    var chart = new google.visualization.Timeline(container);
    var dataTable = new google.visualization.DataTable();

    // The column heading can be fixed or variable, this doesnt matter.
    dataTable.addColumn({ type: 'string', id: 'Opgave ID' });
    dataTable.addColumn({ type: 'string', id: 'Opgavetitel' });
    dataTable.addColumn({ type: 'date', id: 'Start' });
    dataTable.addColumn({ type: 'date', id: 'End' });

    dataTable.addRows(<? echo json_encode( $timeData ); ?>);

    // No questions for any of this
    var options = {
            timeline: {showRowLabels: false, singleColor: '#8d8'}
    };

    chart.draw(dataTable, options);
}

You may find it easier to locate existing material if you broaden your search to a more basic concept (passing a PHP 2d array to javascript). Here's an example of an existing Stack Overflow question on that topic:

Best way to pass a 2d array into JavaScript from PHP?

And there are many more like it.

Finally had the time to test it, and the JSON encoding of the table looks quite odd: `dataTable.addRows([["1","Some task",{"date":"2015-04-09 00:00:00.000000","timezone_type":3,"timezone":"Europe\/Copenhagen"},{"date":"2015-04-23 (...)` — Jakob Busk Sørensen, Mar 11 '15 at 08:13
Ok, didn't expect that... looks like the Jason serialized is serializing the date as an object instead of a string or JavaScript Date. I work in .Net and this is a solved problem there (dates get serialized as JavaScript dates). You have a couple of options.. Either store the dates in PHP as strings and then parse them in JavaScript, or leave them as is and write a JavaScript function to turn them into JavaScript Dates. Don't forget that Months in JavaScript are zero-based (March=2). — nbering, Mar 11 '15 at 12:36
Is there a way to create the the date and convert it to the right format (i.e. the same format as created by `new Date()` in JavaScript)? — Jakob Busk Sørensen, Mar 12 '15 at 06:17

score 0 · Answer 2 · answered Dec 04 '20 at 13:58

I got the same problem while using Google Charts Timeline while using Laravel 6.0 framework. I passed the query results to the view file as an associative array and the used json_encode() to convert the associative array into a JavaScript array that turned into an array of objects:

<script>

  // create a custom data object to pass into the array
  // the charter array data is of the format:
  // 
  // Array(5)
  // 0: {mn: "2020-02-12 10:03:17", mx: "2022-07-12 10:03:17", cha_booking_id: 1}
  // 1: {mn: "2020-02-12 13:33:07", mx: "2020-10-12 13:33:07", cha_booking_id: 2}
  // 2: {mn: "2020-02-12 13:39:47", mx: "2020-08-12 13:39:47", cha_booking_id: 3}
  // 3: {mn: "2020-04-12 06:21:08", mx: "2020-04-12 06:21:08", cha_booking_id: 4}
  // 4: {mn: "2020-05-12 08:44:24", mx: "2020-07-12 08:44:24", cha_booking_id: 5}

  var data = [];

  charter.forEach(el => {
   var arr = [
    el.cha_booking_id.toString(),
    new Date(
     el.mn.substring(0, 4), // get the year from the date format that is passed
     el.mn.substring(5, 7), // get the month from the date format that is passed
     el.mn.substring(8, 10) // get the day from the date format that is passed
    ),  
    new Date(
     el.mx.substring(0, 4),
     el.mx.substring(5, 7),
     el.mx.substring(8, 10)
    )
  ];

  data.push(arr);

  // The column heading can be fixed or variable, this doesnt matter.
  dataTable.addColumn({ type: 'string', id: 'Opgave ID' });
  dataTable.addColumn({ type: 'string', id: 'Opgavetitel' });
  dataTable.addColumn({ type: 'date', id: 'Start' });
  dataTable.addColumn({ type: 'date', id: 'End' });

  dataTable.addRows(data);

  // No questions for any of this
  var options = {
        timeline: {showRowLabels: false, singleColor: '#8d8'}
  };

  chart.draw(dataTable, options);

</script>

Google Charts Timeline - PHP array as input

2 Answers2

Linked