API

Create an Annotated Timeline with the Google Visualization API and QuickBase API

Added by on July 27th, 2010, filed under API, Google, Javascript, QuickBase

The Google Visualization API has a lot of really useful charts and graphs that, when combined with a web-based database like QuickBase, can really be powerful.

Google Annotated Timeline

Here’s a complete example of how to use the PHP SDK for QuickBase to get data from a QuickBase and use it side-by-side with the Google Visualization API to generate annotated time line. The example below is one I used to compare the total number of trials we get on our website with the number of inbound support cases we get.

You’re going to see some date manipulation going on here. QuickBase ouputs the ‘Date Created’ timestamp in milliseconds. I needed to turn this into a format that was easy for the Javascript date() function would be able to parse. I also wanted to normalize the dates so that I could group the total number of support cases by day.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<?php
    include('qb.php');
    $quickbase = new QuickBase('','',true,'chljaara'); //replace chljaara with your table dbid
    $result = $quickbase->do_query('','123'); //replace 123 with your query id

    $trials = array();
   
    // build a 2d array for the Google chart
    foreach ($result->table->records->record as $record)
    {

        $timestamp = round(((double)$record->f) / 1000, 0);
        $date_string = date("F j, Y", $timestamp);

        $case_type = (string) $record->f[1];

        if(array_key_exists($date_string, $trials))
        {
            if(array_key_exists($case_type, $trials[$date_string]))
            {
                $trials[$date_string][$case_type] += 1;
            }
            else
                $trials[$date_string][$case_type] = 1;
        }
        else
            $trials[$date_string]= array();
    }
?>
<!DOCTYPE HTML>
<html>
  <head>
    <script type='text/javascript' src='http://www.google.com/jsapi'></script>
    <script type='text/javascript'>
      google.load('visualization', '1', {'packages':['annotatedtimeline']});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('date', 'Date');
        data.addColumn('number', 'Trials');
        data.addColumn('number', 'Support Cases');
        data.addRows
        ([
<?php
    foreach($trials as $key => $val)
    {
        $trials = isset($val['Trial']) ? $val['Trial'] : 0;
        $support_cases = isset($val['Support Case']) ? $val['Support' Case] : 0;

        echo '[new Date("'. $key .'"), ' . $trials . ', ' . $support_cases . '],' . "\r";
    }
?>
        ]);

        var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div'));
        chart.draw(data, {displayAnnotations: true, fill: 10});
      }
    </script>
  </head>

  <body>

    <div id='chart_div' style='width: 90%; height: 70%;'></div>

  </body>
</html>

About the Author: Joshua McGinnis

Joshua is a web designer and developer for Intuit, Inc. His other interests include guitar, photography, mycology, amateur chemistry, WWII movies and his beautiful wife Catherine.

Enjoy this Post?

Spread the word by promoting this post on FaceBook and Twitter.

Most Commented Posts

What do you think?