Solved

Add 2 more columns to Google Chart column chart

Posted on 2013-10-23
10
611 Views
Last Modified: 2013-10-24
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

0
Comment
Question by:Sim1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39596496
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
 

Author Comment

by:Sim1980
ID: 39597485
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
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39597510
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Sim1980
ID: 39597651
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
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39597700
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
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 500 total points
ID: 39597751
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
 

Author Comment

by:Sim1980
ID: 39597877
I'll try it, by the way my web server database is MySQL.
0
 

Author Comment

by:Sim1980
ID: 39598176
It worked! I'm going to look over the Select statement so I understand what you did.

Thanks again!
0
 

Author Closing Comment

by:Sim1980
ID: 39598218
Thank you!
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 39598429
Glad to help. Good luck!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Jquery form 9 42
Jquery Ajax - on change event not being picked up 9 35
DataTable column sorting incorrectly 2 21
Javascript: Range object 16 14
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question