Solved

Add 2 more columns to Google Chart column chart

Posted on 2013-10-23
10
614 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
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…

691 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