Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Add 2 more columns to Google Chart column chart

Posted on 2013-10-23
10
Medium Priority
?
637 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

604 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