Timeline chart from JSON file

[Ákos Kovács]

I have created a .json file from an SQL query. (This file is without cols and rows, I do not, can be that a problem?)
I tried to use this tutorial for generating a timeline graph, but it gives a blank page.
https://developers.google.com/chart/interactive/docs/php_example

[Andrew Gallant]

The DataTable constructor requires a very specific JSON format; if you aren't using that format, the constructor will throw an error and your chart will not draw.  I can help you get your chart working if you share your code.  I also suggest that you search this group for examples of creating charts from SQL queries (most examples here use PHP and MySQL, though you can easily substitute a different database in most cases).

[Ákos Kovács]

Where can I find please this specific format? I have not found any daily timeline tutorial, only Google's official.

[Andrew Gallant]

This post contains a description of the JSON string.

[Ákos Kovács]

Yes, I see, but how can I implement it to PHP?

[Andrew Gallant]

Here's some sample code using PHP's PDO object for database access:

try {

    $db = new PDO('mysql:dbname=myDataBase', 'username', 'password');

}

catch (PDOException $e) {

    die ($e->getMessage());

}

$query = $db->prepare('SELECT foo, bar, cad from myTable');

$query->execute();

$results = $query->fetchAll(PDO::FETCH_ASSOC);

$data = array(

    'cols' => array(

        // array of column definitions, eg:

        array('type' => 'string', 'label' => 'Foo'),

        array('type' => 'number', 'label' => 'Bar'),

        array('type' => 'number', 'label' => 'Cad')

    ),

    'rows' => array() // will contain the rows of data

);

foreach($results as $row) {

    // add data to the row

    $rows[] = array('c' => array(

        array('v' => $row['foo']),

        array('v' => $row['bar']),

        array('v' => $row['cad'])

    ));

}

// when outputting your data, use

json_encode($data, JSON_NUMERIC_CHECK);