Annotated Timeline Refresh

[Aerodyno]

The annotated timeline chart is great, but the whole chart redraws and
blinks/flashes even when I add just a single point to the data.table
and call refresh. This creates an annoying user experience.

Is there any way to just update a single point in the chart -- without
the massive redraw? Am I missing something (should I be using some
data api) or do we need to wait for google to release the feature?

Thanks in advance! I love writing code using Google's API's.

- Savraj

[Javed Akhtar]

Hi
I need help in auto refreshing the annotated time line chart. I created the chart by populating data from DB using php/mysql, I want to ad the functionality of auto refresh data without having to refresh the whole page. I tried searching a lot on google but could not get a solution. Would appreciate your help as your post say that you already have this refresh feature working.

Help please.

Javed

[Xtremer]

Hi
I need help in auto refreshing the annotated time line chart. I created the chart by populating data from DB using php/mysql, I want to ad the functionality of auto refresh data without having to refresh the whole page. I tried searching a lot on google but could not get a solution. Would appreciate your help as your post say that you already have this refresh feature working.

Help please.

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

On Monday, 22 September 2008 17:31:22 UTC-4, Aerodyno wrote:

[asgallant]

Do you use the visualization Query API for retrieving your charts' data?

[Xtremer]

No I am using php/mysql to retrieve data then I am just feeding that data into charts and refreshing this whole page every minute, what I actually want is to refresh just the graph every minute not the whole page.

[asgallant]

Ok, move the PHP code that generates your data to another page, and use AJAX to fetch the data from that script.  This will allow you to refresh the chart data without reloading the page.  I find jQuery's AJAX functions particularly helpful for this: http://api.jquery.com/category/ajax/ 

Your code might look something like this: https://groups.google.com/d/msg/google-visualization-api/XS4xaEgkRtw/ClBcYNMDkt4J

To make that work, you need to put the data into a json format the charts can understand.  PHP's json_encode() function can help you with this.  Here's an example script: https://groups.google.com/d/msg/google-visualization-api/Gy18YdVSSfQ/tKdtzSfqKlcJ

[Xtremer]

Thanks for your help, but I am still not able to figure out how to implement this. I tried using the code but no success.

[asgallant]

The PHP code will have to be customized for your particular needs.  If you don't use PDO's, you'll have to change it to whichever method you do use.  You can check the output by calling the php script from your browser - the JSON should look something like the json shown at the bottom of this page: https://developers.google.com/chart/interactive/docs/php_example (although it will have all whitespace removed).  Once you get the JSON right, we can work on getting your charts updated.

[Xtremer]

this is the JSON output from PHP file I am getting the data in this format which is fine

"[new Date(2012,04-1,19,12,27, 00),182,],[new Date(2012,04-1,19,12,28, 00),183,],"



$output = '';
$rows = mysql_num_rows($scans);
$row = mysql_fetch_row($scans); //skip first row as it is incomplete
      while($rows>2)//skip last row as it is incomplete
      {
            $row = mysql_fetch_row($scans);
            $output = $output."[new Date(".$row[0].", 00),".$row[1].",],";
            $rows--;
      }

echo json_encode($output);

[asgallant]

There are a few things I see here:

1) date objects are not valid JSON, so you need to pass them as strings, ie "Date(2012,04-1,19,12,27, 00)" (with quotes) instead of: new Date(2012,04-1,19,12,27, 00)

2) you have errant commas at the end of your arrays, which will break IE.

Try this instead:

$output = array();

$rows = mysql_num_rows($scans);

$row = mysql_fetch_row($scans); // skip first row as it is incomplete

while($rows>2) { //skip last row as it is incomplete

$row = mysql_fetch_row($scans);

$output[] = array('Date({$row[0]}, 00)', $row[1]);

$rows--; 

}

// set up header and output json (first two prevent IE from caching queries)

header('Cache-Control: no-cache, must-revalidate');

header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');

header('Content-type: application/json');

echo json_encode($output);

Your JS to draw the chart should look something like this:

google.load('visualization', '1', {packages: ['annotatedtimeline']});
google.setOnLoadCallback(drawVisualization);

function drawVisualization() {
    var data = new google.visualization.DataTable();
    data.addColumn('datetime', 'Datetime');
    data.addColumn('number', 'Scans');
    
    var now = new Date(); //current date
    
    var annotatedtimeline = new google.visualization.AnnotatedTimeLine(document.getElementById('visualization'));
    
    function updateData () {
        $.ajax({
            url: 'path/to/data/script.php',
            dataType: 'json',
            success: function (json) {
                data.removeRows(0, data.getNumberOfRows());
                data.addRows(json);
                annotatedtimeline.draw(data, {
                    //'allValuesSuffix': '%', // A suffix that is added to all values
                    'colors': ['#005EFF'],
                    // The colors to be used
                    'allowRedraw': true,
                    'displayAnnotations': false,
                    'displayExactValues': true,
                    // Do not truncate values (i.e. using K suffix)
                    'displayRangeSelector': true,
                    // Do not sow the range selector
                    'displayZoomButtons': true,
                    // DO not display the zoom buttons
                    'fill': 15,
                    // Fill the area below the lines with 20% opacity
                    'legendPosition': 'newRow',
                    // Can be sameRow
                    //'max': 100000, // Override the automatic default
                    //'min':  100000, // Override the automatic default
                    'scaleColumns': [0, 1],
                    // Have two scales, by the first and second lines
                    'scaleType': 'allfixed',
                    // See docs...
                    'thickness': 1,
                    // Make the lines thicker
                    'zoomStartTime': new Date(now - 12 * 60 * 60 * 1000),
                    //NOTE: month 1 = Feb (javascript to blame)
                    'zoomEndTime': new Date(now - 4 * 60 * 60 * 1000) //NOTE: month 1 = Feb (javascript to blame)
                });
                
                // refresh data in 1 minute
                setTimeout(function () {
                    updateData();
                }, 60000);
            }
        });
    }
}​ 

Let me know if that works for you.

[Xtremer]

Still no success with the timeline chart. The data passed in JSON contains data with double quotes "" which could be one issue. Other than that I made all changes by you. Please suggest something.

[asgallant]

Post the JSON output, I'll take a look.

[Xtremer]

this is the output from JSON

[["new Date(2012,04-1,19,14,40,00),172"],["new Date(2012,04-1,19,14,41,00),153"],["new Date(2012,04-1,19,14,42,00),168"],["new Date(2012,04-1,19,14,43,00),171"],["new Date(2012,04-1,19,14,44,00),175"],["new Date(2012,04-1,19,14,45,00),145"],["new Date(2012,04-1,19,14,46,00),137"],["new Date(2012,04-1,19,14,47,00),209"],["new Date(2012,04-1,19,14,48,00),161"],["new Date(2012,04-1,19,14,49,00),172"],["new Date(2012,04-1,19,14,50,00),160"],["new Date(2012,04-1,19,14,51,00),149"],["new Date(2012,04-1,19,14,52,00),172"],["new Date(2012,04-1,19,14,53,00),164"],["new Date(2012,04-1,19,14,54,00),153"],["new Date(2012,04-1,19,14,55,00),155"],["new Date(2012,04-1,19,14,56,00),145"],["new Date(2012,04-1,19,14,57,00),160"],["new Date(2012,04-1,19,14,58,00),178"],["new Date(2012,04-1,19,14,59,00),152"],["new Date(2012,04-1,19,15,00,00),144"],["new Date(2012,04-1,19,15,01,00),178"],["new Date(2012,04-1,19,15,02,00),145"],["new Date(2012,04-1,19,15,03,00),152"],["new Date(2012,04-1,19,15,04,00),146"],["new Date(2012,04-1,19,15,05,00),116"],["new Date(2012,04-1,19,15,06,00),145"],["new Date(2012,04-1,19,15,07,00),143"],["new Date(2012,04-1,19,15,08,00),170"],["new Date(2012,04-1,19,15,09,00),156"],["new Date(2012,04-1,19,15,10,00),168"],["new Date(2012,04-1,19,15,11,00),167"],["new Date(2012,04-1,19,15,12,00),185"],["new Date(2012,04-1,19,15,13,00),179"],["new Date(2012,04-1,19,15,14,00),182"],["new Date(2012,04-1,19,15,15,00),174"],["new Date(2012,04-1,19,15,16,00),186"],["new Date(2012,04-1,19,15,17,00),154"],["new Date(2012,04-1,19,15,18,00),158"],["new Date(2012,04-1,19,15,19,00),178"],["new Date(2012,04-1,19,15,20,00),173"],["new Date(2012,04-1,19,15,21,00),152"],["new Date(2012,04-1,19,15,22,00),181"],["new Date(2012,04-1,19,15,23,00),162"],["new Date(2012,04-1,19,15,24,00),168"],["new Date(2012,04-1,19,15,25,00),206"],["new Date(2012,04-1,19,15,26,00),214"],["new Date(2012,04-1,19,15,27,00),186"],["new Date(2012,04-1,19,15,28,00),194"],["new Date(2012,04-1,19,15,29,00),174"],["new Date(2012,04-1,19,15,30,00),172"],["new Date(2012,04-1,19,15,31,00),144"],["new Date(2012,04-1,19,15,32,00),154"],["new Date(2012,04-1,19,15,33,00),156"],["new Date(2012,04-1,19,15,34,00),166"],["new Date(2012,04-1,19,15,35,00),167"],["new Date(2012,04-1,19,15,36,00),163"],["new Date(2012,04-1,19,15,37,00),154"],["new Date(2012,04-1,19,15,38,00),177"]]

I think the extra double quotes at the start and end is causing the issue.

[Xtremer]

I tried debugging the code with firebug where I found out that problem is not with double quotes but instead I am getting error on line data.addRows(json); saying Uncaught Error: Row given with size different than 2 (the number of columns in the table).

I think that API requires the data to be within [] brackets where as json passes the data without it. This could be one problem. Is there a way I can add [] before and after json data?

[asgallant]

The code I gave you should not be outputting that.  It should look more like: 

[["Date(2012,04-1,19,14,40,00)",172],["Date(2012,04-1,19,14,41,00)",153]....

[Xtremer]

Actually I added the new keyword to it since API requires it to be in that format.

[asgallant]

When passing a date as a string like this, you need to specifically exclude the "new" keyword, as date objects are not valid JSON.  Even with that, the quotes are in the wrong place.  They should be around the dates only.  I ran a test script and the code I sent you had single quotes where it needed double quotes in this line:

$output[] = array("Date({$row[0]}, 00)", $row[1]);

Running the test script with that in place produced the proper JSON (dates are different because of my database outputs):

[["Date(2012, 0, 1, 00)",386],["Date(2012, 0, 2, 00)",452],["Date(2012, 0, 3, 00)",327]]

You dates also need some format corrections, namely the "04-1" part will throw errors.

[Xtremer]

Thanks for all your help. I implemented my code with your changes and this is what I am getting in firebug. (see screenshot)

[Xtremer]

Thanks you very much for all your help, finally i got the graph workign as it should be. :D
Thank you very much.

[asgallant]

Ok lets take a slightly different tack; use this for your php:

$output = array();

$rows = mysql_num_rows($scans);

$row = mysql_fetch_row($scans); // skip first row as it is incomplete

// populate column definitions

$output['cols'] = array (

array('type' => 'datetime', 'label' => 'Datetime'),

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

);

while($rows>2) { //skip last row as it is incomplete

$row = mysql_fetch_row($scans);

$output['rows'][] = array("Date({$row[0]}, 00)", $row[1]);

$rows--; 

}

echo json_encode($output);

and this for your javascript:

function drawVisualization () {
    var data;

    var now = new Date(); //current date
    
    var annotatedtimeline = new google.visualization.AnnotatedTimeLine(document.getElementById('visualization'));
    
    function updateData () {
        $.ajax({
            url: 'path/to/data/script.php',
            dataType: 'json',
            success: function (json) {

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

[asgallant]

You're welcome.