Simple Line Graph with 2 series - data from php - almost there

3,339 views
Skip to first unread message

new_prog

unread,
Apr 17, 2013, 4:26:35 PM4/17/13
to google-visua...@googlegroups.com

Hi all,

Am stuck on trying to display two series on a line chart through google charts api.

I am getting my data from a database using sql and this is all working fine.

I have adapted the code from: PHP MYSQL Google Chart JSON Complete Example

<?php
$connection = mysql_connect(blah);
$database = mysql_select_db(blah);

// The Chart table contain two fields: Date and PercentageChange
$queryData = mysql_query("SELECT Date, PercentageChange
                              FROM Data
                              ORDER BY Date DESC
                              LIMIT 0, 14");

$queryData1 = mysql_query("SELECT Date, PercentageChange
                              FROM Data1
                              ORDER BY Date DESC
                              LIMIT 0, 14");                              


$table = array();
$table['cols'] = array(

    array('label' => 'Date', 'type' => 'string'),
    array('label' => 'Percentage Change', 'type' => 'number'),
    array('label' => 'Percentage Change 1', 'type' => 'number')

);

//First Series
    $rows = array();
    while($r = mysql_fetch_assoc($queryData)) {
        $temp = array();
        // the following line will used to slice the Pie chart
        $temp[] = array('v' => (string) $r['Date']); 

        //Values of the each slice
        $temp[] = array('v' => (float) $r['PercentageChange']); 
        $rows[] = array('c' => $temp);
    }

    $table['rows'] = $rows;
    $jsonTable = json_encode($table);
    //echo $jsonTable;

//For Data1
    $rows = array();
    while($r = mysql_fetch_assoc($queryData1)) {
        $temp = array();
        // the following line will used to slice the Pie chart
        $temp[] = array('v' => (string) $r['Date']); 

        //Values of the each slice
        $temp[] = array('v' => (float) $r['PercentageChange']); 
        $rows[] = array('c' => $temp);
    }

    $table['rows'] = $rows;
    $jsonTable1 = json_encode($table);
    echo $jsonTable1;
?>


<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">

    // Load the Visualization API and the chart package.
    google.load('visualization', '1', {'packages':['corechart']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);

    function drawChart() {

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(<?=$jsonTable?>);
      var data1 = new google.visualization.DataTable(<?=$jsonTable1?>);
      var options = {
          title: 'Performance',
          width: 800,
          height: 600
        };
      // Instantiate and draw our chart, passing in some options.
      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
    </script>
  </head>

  <body>
    <!--Div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>

Now, i know it is picking up the correct data from each query but I can't figure out how to put both on the same chart.

I assume it is something to do with this line: 

chart.draw(data, options);

And have tried:

chart.draw(data, data1, options);

But no luck.

Could anyone point me in the correct direction?

Thanks!

asgallant

unread,
Apr 17, 2013, 5:19:41 PM4/17/13
to google-visua...@googlegroups.com
Ok, the first problem is that you are creating a DataTable with 3 columns here:

$table['cols'] = array(
    array('label' => 'Date', 'type' => 'string'),
    array('label' => 'Percentage Change', 'type' => 'number'),
    array('label' => 'Percentage Change 1', 'type' => 'number')
);

But only populating 2 columns in the while loops:

while($r = mysql_fetch_assoc($queryData)) {
    $temp = array();
    // the following line will used to slice the Pie chart
    $temp[] = array('v' => (string) $r['Date']);

    //Values of the each slice
    $temp[] = array('v' => (float) $r['PercentageChange']);
    $rows[] = array('c' => $temp);
}

This will cause the line:

var data = new google.visualization.DataTable(<?=$jsonTable?>);

to throw an error.

There are two ways you can address the situation:

1) perform a table join in your SQL to make 1 query and build 1 DataTable in PHP
2) build 2 2-column DataTables in PHP and join them in javascript

Option 1 should be faster to execute than option 2 (and results in simpler code), as databases are optimized for things like table joins, whereas javascript is not.  Try this instead:

<?php
$connection = mysql_connect(blah);
$database = mysql_select_db(blah);

// The Chart table contain two fields: Date and PercentageChange
$queryData = mysql_query("
SELECT
Date,
PercentageChange,
PercentageChange1
FROM (
SELECT
a.Date,
a.percentageChange,
a.percentageChange AS percentageChange1
FROM Data AS a
LEFT JOIN Data1 AS b ON (a.Date = b.Date)
UNION
SELECT
b.Date,
a.percentageChange,
a.percentageChange AS percentageChange1
FROM Data AS a
RIGHT JOIN Data1 AS b ON (a.Date = b.Date)
)
ORDER BY Date DESC
LIMIT 0, 14
");

$table = array();
$table['cols'] = array(
    array('label' => 'Date', 'type' => 'string'),
    array('label' => 'Percentage Change', 'type' => 'number'),
    array('label' => 'Percentage Change 1', 'type' => 'number')
);

//First Series
$rows = array();
while($r = mysql_fetch_assoc($queryData)) {
$temp = array();
// the following line will used to slice the Pie chart
$temp[] = array('v' => (string) $r['Date']); 

//Values of the each slice
$temp[] = array('v' => (float) $r['PercentageChange']); 
$temp[] = array('v' => (float) $r['PercentageChange1']); 
$rows[] = array('c' => $temp);
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);
?>
<!DOCTYPE html>
<html>
<head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">
// Load the Visualization API and the chart package.
google.load('visualization', '1', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);

function drawChart() {
// Create our data table out of JSON data loaded from server.
var data = new google.visualization.DataTable(<?=$jsonTable?>);
var options = {
title: 'Performance',
width: 800,
height: 600
};
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
    </script>
</head>

<body>
<!--Div that will hold the pie chart-->
<div id="chart_div"></div>
</body>
</html>

new_prog

unread,
Apr 18, 2013, 1:47:50 PM4/18/13
to google-visua...@googlegroups.com
Thank you very much for the reply and for the code.. I am having a problem with the SQL though:
"#1248 - Every derived table must have its own alias"

I have tried fiddling around with it but can't get it to work.

Do you have any suggestions?

asgallant

unread,
Apr 18, 2013, 2:52:20 PM4/18/13
to google-visua...@googlegroups.com
Try this:

$queryData = mysql_query("
SELECT
Date,
PercentageChange,
PercentageChange1
FROM (
SELECT
a.Date,
a.percentageChange,
a.percentageChange AS percentageChange1
FROM Data AS a
LEFT JOIN Data1 AS b ON (a.Date = b.Date)
UNION
SELECT
b.Date,
a.percentageChange,
a.percentageChange AS percentageChange1
FROM Data AS a
RIGHT JOIN Data1 AS b ON (a.Date = b.Date)
) AS x
ORDER BY Date DESC
LIMIT 0, 14
");

new_prog

unread,
Apr 18, 2013, 4:07:35 PM4/18/13
to google-visua...@googlegroups.com
Thanks for the response - this is an improvement as it's executing.. It's returning both columns (PercentageChange and PercentageChange1) with the same data (data from Table: Data).. Do you know where the error may be?

Thanks again for the help!

asgallant

unread,
Apr 18, 2013, 5:53:56 PM4/18/13
to google-visua...@googlegroups.com
Oops, typo in there (the percentageChange1 selections in both sides of the UNION should be pulled from b not a):

$queryData = mysql_query("
SELECT
Date,
PercentageChange,
PercentageChange1
FROM (
SELECT
a.Date,
a.percentageChange,
b.percentageChange AS percentageChange1
FROM Data AS a
LEFT JOIN Data1 AS b ON (a.Date = b.Date)
UNION
SELECT
b.Date,
a.percentageChange,
b.percentageChange AS percentageChange1
FROM Data AS a
RIGHT JOIN Data1 AS b ON (a.Date = b.Date)
) AS x
ORDER BY Date DESC
LIMIT 0, 14
");

new_prog

unread,
Apr 20, 2013, 9:28:06 AM4/20/13
to google-visua...@googlegroups.com
Perfect, thanks have managed to get that working now - just one final problem I can't figure out..

The axis and data is in the wrong order ( i.e. it's in reverse so yesterdays data is on the far left instead of far right of the x axis).

Now I've tried array_reverse($table) but that hasn't worked.. any ideas?

Thanks again for the help

asgallant

unread,
Apr 20, 2013, 2:06:10 PM4/20/13
to google-visua...@googlegroups.com
You would want to reverse $table['rows'], not $table.  You can also set the chart's hAxis.direction option to -1 to reverse the order of the values.
Reply all
Reply to author
Forward
0 new messages