Add 2 more columns to Google Chart column chart

Hi all.

I have a website where I display a column chart. Currently it only shows one column for each user, but I want to be able to display 2 more columns. The current column is a count when the type = 1. I want to add another column that is a count when the type =2 and the third column to be the sum of both, how can I do this with the Google Charts column chart?

I have provided the code below of what I have so far, but it doesn't show the second column chart, it only shows one. I haven't tried the third column (which sums the 2 columns) yet.

Any help would be great. Thank you in advance!

ChartTest2.html

<html>
  <head>
    <!--Load the AJAX API-->
    <meta content="text/html;charset=utf-8" http-equiv="Content-Type" />
    <meta content="utf-8" http-equiv="encoding" />
    <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">
google.load("visualization", "1", {packages:["corechart"]});
 google.setOnLoadCallback(drawChart1);

function drawChart1() {
        var jsonData1 = $.ajax({
          url: "GetChartData1.php",
          dataType:"json",
          async: false
          }).responseText;
var data = new google.visualization.DataTable(jsonData1);

 var options = {'title':'Super Workload',
                       'width':900,
                       'height':500};

 var chart = new google.visualization.ColumnChart(document.getElementById('chart_div1'));
        chart.draw(data, options);
        
      
      }

    </script>
  </head>

  <body>
  <div id="chart_div1"></div> 
   
  </body>
</html>

Open in new window


GetChartData1.php
require("common.php");

 $smt = $db->prepare('SELECT Concat(firstname ," " ,lastname) as Name, count(Type) as Slab FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1  Group by Users.userid order by firstname');  
     $smt->execute();
     $data = $smt->fetchAll(); 
                
     $smt1 = $db1->prepare('SELECT Concat(firstname ," " ,lastname) as Name, count(Type) as DriedIn FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 2  Group by Users.userid order by firstname');  
     $smt1->execute();
     $data1 = $smt1->fetchAll(); 
    
  $rows = array();
  $table = array();
  $table['cols'] = array(
    array('label' => 'Name', 'type' => 'string'),
    array('label' => 'DriedIn', 'type' => 'number'),
    array('label' => 'Slab', 'type' => 'number')

);

foreach($data as $row) {
      $temp = array();
      $temp[] = array('v' => (string) $row['Name']); 
      $temp[] = array('v' => (int) $row['DriedIn']); 
      $temp[] = array('v' => (int) $row['Slab']); 
      $rows[] = array('c' => $temp);
    }

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

Open in new window

Sim1980Asked:
Who is Participating?
 
Pierre CorneliusConnect With a Mentor Commented:
try changing your query for $smt to:


SELECT Concat(x.firstname ," " ,x.lastname) as Name, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total 
from 
(
SELECT firstname, lastname, count(1) as Slab, 0 as DriedIn 
FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID 
Where Type = 1  
group by firstname, lastname

union all

SELECT firstname, lastname, 0 as Slab, count(1) as DriedIn 
FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID 
Where Type = 2
group by firstname, lastname
) x
Group by Concat(x.firstname ," " ,x.lastname) 
order by Concat(x.firstname ," " ,x.lastname) 

Open in new window


Change your cols to:
$table['cols'] = array(
    array('label' => 'Name', 'type' => 'string'),
    array('label' => 'DriedIn', 'type' => 'number'),
    array('label' => 'Slab', 'type' => 'number'),
	array('label' => 'Total', 'type' => 'number')
	);

Open in new window

     
Add this to your rows loop :
$temp[] = array('v' => (int) $row['Total']);

Open in new window

0
 
Pierre CorneliusCommented:
I tried your sample but without using php, just for testing expected result. See below
<html>
  <head>
    <!--Load the AJAX API-->
    <meta content="text/html;charset=utf-8" http-equiv="Content-Type" />
    <meta content="utf-8" http-equiv="encoding" />
    <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">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart1);

function drawChart1() {
        //var jsonData1 = $.ajax({
        //  url: "GetChartData1.php",
        //  dataType:"json",
        //  async: false
        //  }).responseText;
		var jsonData1 = {
			cols: [	{label: 'Name', type: 'string'},
					{label: 'DriedIn', type: 'number'},
					{label: 'Slab', type: 'number'}
					],
			rows: [	{c:[{v: 'Name1'}, {v: 1}, {v: 10}]},
					{c:[{v: 'Name2'}, {v: 2}, {v: 20}]},
					{c:[{v: 'Name3'}, {v: 3}, {v: 30}]}
					]
		};
var data = new google.visualization.DataTable(jsonData1);

 var options = {'title':'Super Workload',
                       'width':900,
                       'height':500};

 var chart = new google.visualization.ColumnChart(document.getElementById('chart_div1'));
        chart.draw(data, options);
        
      
      }

    </script>
  </head>

  <body>
  <div id="chart_div1"></div> 
   
  </body>
</html>
                                  

Open in new window


It shows both columns (see image below), so maybe the problem is in your php. Can you display the output of your GetChartData1.php?
SuperWorkload.PNG
0
 
Sim1980Author Commented:
Here's the output of GetChartData1.php. It's not including the count for one of them, they're all appearing as 0.

{"cols":[{"label":"Name","type":"string"},{"label":"DriedIn","type":"number"},{"label":"Slab","type":"number"}],"rows":[{"c":[{"v":"Anthony DiFranco"},{"v":0},{"v":25}]},{"c":[{"v":"Bill Clancy"},{"v":0},{"v":27}]},{"c":[{"v":"Bobby Marshall"},{"v":0},{"v":64}]},{"c":[{"v":"Daniel Montalvan"},{"v":0},{"v":68}]},{"c":[{"v":"Dino Mastrianni"},{"v":0},{"v":18}]},{"c":[{"v":"Jason Selden"},{"v":0},{"v":19}]},{"c":[{"v":"Jim Hamilton"},{"v":0},{"v":81}]},{"c":[{"v":"Tommy Distefano"},{"v":0},{"v":14}]}]}

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pierre CorneliusCommented:
The reason it seems like it is not showing the DriedIn field is because all your values returned by your php script for that field is zero. so the bar height for each of those are zero.

Problem is with your query. You don't have a field called DriedIn in it, only Name and Slab:
SELECT Concat(firstname ," " ,lastname) as Name, count(Type) as Slab FROM Users INNER JOIN FieldSuperDataEntry ON Users.userid = FieldSuperDataEntry.UserID Where Type = 1  Group by Users.userid order by firstname');
0
 
Sim1980Author Commented:
Right because first I'm counting Slabs (that's where Type = 1), then the second PHP code counts Dried In (that's where Type = 2). The fields in the FieldSuperDataEntry table are: UserID and Type. Type can be either 1 (for slab) or 2 (for dried in), so I need to count how many type 1 and how many type 2 for each UserID and then show that as 2 columns for each UserID. And then add a 3rd column for the sum of the two. I can get this to work great in Excel through VBA but I'm having a tough time doing this in php.

Should I populate this in a table on the webpage and then have the Google Chart read that table?
0
 
Pierre CorneliusCommented:
I see. but you never user the data1 variable. You have to add those records also in your for each loop where you add the values.

The easiest will be to just do a single query in which case your php is fine, all that you will need to change is your sql (and delete the second query). What you are asking for should be possible with a single query in most database servers. What database server are you using, so I can help you with the sql for that.
0
 
Sim1980Author Commented:
I'll try it, by the way my web server database is MySQL.
0
 
Sim1980Author Commented:
It worked! I'm going to look over the Select statement so I understand what you did.

Thanks again!
0
 
Sim1980Author Commented:
Thank you!
0
 
Pierre CorneliusCommented:
Glad to help. Good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.