Annotated Timeline - Multiple Lines from PHP/MySQL

[Paul Loeb]

Hello! I'm having trouble formatting my data to be displayed properly on the Google Visualization AnnotatedTimeLine chart. 

I have a table of products, and a table of devices (each device is a representation of one type of product).

The data looks something like this in MySQL:

products table:

product_id

product_name

devices table:

device_id

product_id

device_registered

I want the chart to display device registrations over time. The x-axis is a date range, and the y-axis is the total number of devices registered.

So far I am able to successfully generate the chart for one product at a time, but what I need to do is display a line for each product's registration growth - all together on one chart.

Here is my code to generate the chart for one product at a time:

<h3 class="center">Device Registration Growth:
<select name="device_registration_growth_product_id" id="device_registration_growth_product_id" class="selecth3">
<option value="0">(All Products)</option>
<?php foreach($view->products as $product):
$exF="";
if ($product->product_id==$view->product_id){$exF=" selected";}
?>
<option value="<?=$product->product_id?>"<?=$exF?>><?=$product->product_name?></option>
<?php endforeach; ?>
</select></h3>
<div id="chart_device_registration_growth" style="height:400px;"></div>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
      google.load("visualization", "1", {packages:["annotatedtimeline"]});
      google.setOnLoadCallback(firstChart);
      function firstChart() {
     drawChart(<?=$view->product_id?>)
      }
      function drawChart(pID) {
    var gData = new google.visualization.DataTable();
        var gUrl = "/devices/growth/product/"+pID+".json";
        $.get(gUrl,function(data){
            var pName = $("#device_registration_growth_product_id option[value='"+pID+"']").text();        
            gData.addColumn('date', 'Date');             
                gData.addColumn('number',pName);
                var gRows = [];               
                var growth = data.data.growth;
                var startingRegistrations = 0;                
                $.each(growth,function(key,value){
                    var growthdate = value.date;
                    var dateParts = growthdate.split("-");
                    growthdate = new Date(dateParts[0], dateParts[1] - 1, dateParts[2]);
                    var growthregistrations = parseInt(value.device_registrations);
                    startingRegistrations+=growthregistrations;
    gRows.push([growthdate, startingRegistrations]);
                }); 
                gData.addRows(gRows);
                var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_device_registration_growth'));
                chart.draw(gData, {'displayAnnotations': true});            
        });
      }
 $("#device_registration_growth_product_id").change(function(){
var pID = $(this).val();
drawChart(pID);
 });
</script>

And here is the non-functional code I have to display all the products on one chart:

gData.addColumn('date', 'Date');

$.get("/products.json",function(data){

var products = data.data.products;

$.each(products,function(key,value){

var pName = value.product_name;

               gData.addColumn('number',pName);

var pID = value.product_id;

               var gUrl = "/devices/growth/product/"+pID+".json";

            $.get(gUrl,function(pdata){

            var growth = pdata.data.growth;

            var startingRegistrations=0;

            $.each(growth,function(key,value){

            if (value.hasOwnProperty("date")) {

                       var growthdate = value.date;

                       var dateParts = growthdate.split("-");

                       growthdate = new Date(dateParts[0], dateParts[1] - 1, dateParts[2]);

                       var growthregistrations = parseInt(value.device_registrations);

                       startingRegistrations+=growthregistrations;

// gData.addRows([[new Date(2013,02,01), 1, 2, 3, 4, 5, 6]]);

            }

                   }); //end each product growth     

           }); //end get product     

}); // end each product 

// gData.addRows([[new Date(2013,02,01), 1, 2, 3, 4, 5, 6]]);

// gData.addRows([[new Date(2013,02,15), 2, 3, 4, 5, 6, 7]]);

// gData.addRows([[new Date(2013,03,01), 8, 8, 8, 9, 9, 9]]);

var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_device_registration_growth'));

   chart.draw(gData, {'displayAnnotations': true});    

});   

 

The data coming from the APIs are:

/devices/growth/product/"+pID+".json"  

{

    "code": 200,

    "data": {

        "growth": [

            {

                "date": "2013-01-28",

                "device_registrations": "14"

            },

            {

                "date": "2013-02-14",

                "device_registrations": "2"

            },

            {

                "date": "2013-03-05",

                "device_registrations": "1"

            }

        ]

    }

}

and from the Products api (to get a list of products):

 

{

    "code": 200,

    "data": {

        "products": [

            {

                "product_id": "23",

                "product_name": "Alpha",

            },

            {

                "product_id": "22",

                "product_name": "Beta",

            },

            {

                "product_id": "21",

                "product_name": "Gamma",

            }

        ]

    }

}

 

Please help!!

Thanks

[asgallant]

You will have to parse the data into a usable format.  Here's a rough set of code that should get you most of the way there:

$.get("/products.json", function (data) {

var gData = new google.visualization.DataTable();

gData.addColumn('date', 'Date');

var products = data.data.products;

var tempData = {};

var pNames = [];

$.each(products, function (key, value) {

var pName = value.product_name;

gData.addColumn('number', pName);

var pID = value.product_id;

pNames.push(pName);

var gUrl = "/devices/growth/product/"+pID+".json";

$.get(gUrl, function (pdata) {

var growth = pdata.data.growth;

var startingRegistrations = 0;

$.each(growth, function (key, value) {

if (value.hasOwnProperty("date")) {

var growthdate = value.date;

var dateParts = growthdate.split("-");

growthdate = new Date(dateParts[0], dateParts[1] - 1, dateParts[2]);

var growthregistrations = parseInt(value.device_registrations);

startingRegistrations+=growthregistrations;

if (typeof(tempData[value.date]) != 'object') {

tempData[value.date] = {date: growthdate};

}

tempData[value.date][pNames] = startingRegistrations;

}

}); //end each product growth

}); //end get product

}); // end each product

for (var x in tempData) {

var row = [tempDate[x].date];

for (var i = 0; i < pNames.length; i++) {

var val = (typeof(tempData[x][pNames[i]]) == 'undefined') ? null : tempData[x][pNames[i]];

row.push(val);

}

gData.addRow(row);

}

var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_device_registration_growth'));

chart.draw(gData, {'displayAnnotations': true});

});

[Paul Loeb]

Thanks for your quick reply! I think I see what you're getting at, but it isn't working exactly right.

When I try implementing your solution and run  console.log(tempData), it shows up empty {}

pNames comes out correctly as ["Alpha", "Beta", "Gamma"] 

[asgallant]

I see two typos that need to be fixed:

tempData[value.date][pNames] = startingRegistrations;

should be:

tempData[value.date][pName] = startingRegistrations;

("pNames" change to "pName"), and 

var row = [tempDate[x].date];

should be:

var row = [tempData[x].date];

("tempDate" change to "tempData").  Give that a try and see if it works.

[Paul Loeb]

Still no dice

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/F47qdbJlAR0/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[asgallant]

Does the error console give you any hints as to what might be going on?  Also, if you could post a link to the working page, I could take a deeper look.

On Friday, March 8, 2013 6:04:39 PM UTC-5, Paul Loeb wrote:

Still no dice

On Mar 8, 2013, at 2:45 PM, asgallant <drew_g...@abtassoc.com> wrote:

I see two typos that need to be fixed:

tempData[value.date][pNames] = startingRegistrations;

should be:

tempData[value.date][pName] = startingRegistrations;

("pNames" change to "pName"), and 

var row = [tempDate[x].date];

should be:

var row = [tempData[x].date];

("tempDate" change to "tempData").  Give that a try and see if it works.

On Friday, March 8, 2013 3:29:39 PM UTC-5, Paul Loeb wrote:

Thanks for your quick reply! I think I see what you're getting at, but it isn't working exactly right.

When I try implementing your solution and run  console.log(tempData), it shows up empty {}

pNames comes out correctly as ["Alpha", "Beta", "Gamma"] 

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/F47qdbJlAR0/unsubscribe?hl=en.

To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.