Solved

Add 2 more columns to Google Chart column chart

Posted on 2013-10-23
10
599 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
  • 5
  • 5
10 Comments
 
LVL 14

Expert Comment

by:Pierre Cornelius
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Sim1980
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 500 total points
Comment Utility
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
Comment Utility
I'll try it, by the way my web server database is MySQL.
0
 

Author Comment

by:Sim1980
Comment Utility
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
Comment Utility
Thank you!
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
Comment Utility
Glad to help. Good luck!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now