Failed to create google chart using MySQL table data as data source
I am trying to create a google chart from a MySQL database. I replied to this post: PHP MySQL Google Chart JSON - complete example
I have done everything mentioned in the post, but I get this on the webpage:
'Weekly Task', 'type' => 'string'), array ('label' => 'Percentage', 'type' => 'number')); $ rows = array (); while ($ r = mysql_fetch_assoc ($ sth)) {$ temp = array (); // the next line will be used to slice the pie chart $ temp [] = array ('v' => (string) $ r ['Weekly_task' ]); // Values โโof each slice $ temp [] = array ('v' => (int) $ r ['percent']); $ rows [] = array ('c' => $ temp); } $ table ['rows'] = $ rows; $ jsonTable = json_encode ($ table); // echo $ jsonTable ;? >
I have tried and tested my database, everything looks fine.
code:
<html>
<head>
<!--Load the Ajax API-->
<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">
// Load the Visualization API and the piechart package.
google.load('visualization', '1', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);
function drawChart() {
// Create our data table out of JSON data loaded from server.
var data = new google.visualization.DataTable(<?=$jsonTable?>);
var options = {
title: 'My Weekly Plan',
is3D: 'true',
width: 800,
height: 600
};
// Instantiate and draw our chart, passing in some options.
// Do not forget to check your div ID
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<?php
$con=mysql_connect("localhost","root","password") or die("Failed to connect with database!!!!");
mysql_select_db("googlecharts", $con);
// The Chart table contains two fields: weekly_task and percentage
// This example will display a pie chart. If you need other charts such as a Bar chart, you will need to modify the code a little to make it work with bar chart and other charts
$sth = mysql_query("SELECT weekly_task, percentage FROM chart");
/*
---------------------------
example data: Table (Chart)
--------------------------
weekly_task percentage
Sleep 30
Watching Movie 40
work 44
*/
$rows = array();
//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(
// Labels for your chart, these represent the column titles
// Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
array('label' => 'Weekly Task', 'type' => 'string'),
array('label' => 'Percentage', 'type' => 'number')
);
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$temp = array();
// the following line will be used to slice the Pie chart
$temp[] = array('v' => (string) $r['Weekly_task']);
// Values of each slice
$temp[] = array('v' => (int) $r['percentage']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
//echo $jsonTable;
?>
<!--this is the div that will hold the pie chart-->
<div id="chart_div"></div>
</body>
</html>
UPDATE I split HTML and PHP codes into different files. Now I am getting a blank webpage. CODE: PHP:
<?php
$con=mysql_connect("localhost","root","pass") or die("Failed to connect with database!!!!");
mysql_select_db("googlechart", $con);
// The Chart table contains two fields: weekly_task and percentage
// This example will display a pie chart. If you need other charts such as a Bar chart, you will need to modify the code a little to make it work with bar chart and other charts
$sth = mysql_query("SELECT weekly_task, percentage FROM chart");
/*
---------------------------
example data: Table (Chart)
--------------------------
weekly_task percentage
Sleep 30
Watching Movie 40
work 44
*/
$rows = array();
//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(
// Labels for your chart, these represent the column titles
// Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
array('label' => 'Weekly Task', 'type' => 'string'),
array('label' => 'Percentage', 'type' => 'number')
);
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$temp = array();
// the following line will be used to slice the Pie chart
$temp[] = array('v' => (string) $r['Weekly_task']);
// Values of each slice
$temp[] = array('v' => (int) $r['percentage']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
//echo $jsonTable;
?>
HTML:
<?php
include('new.php');
ini_set('display_errors', 1);
error_reporting(E_ALL);
?>
<html>
<head>
<!--Load the Ajax API-->
<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">
// Load the Visualization API and the piechart package.
google.load('visualization', '1', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);
function drawChart() {
// Create our data table out of JSON data loaded from server.
var data = new google.visualization.DataTable(<?=$jsonTable?>);
var options = {
title: 'No. of Kills',
is3D: 'true',
width: 800,
height: 600
};
// Instantiate and draw our chart, passing in some options.
// Do not forget to check your div ID
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<!--this is the div that will hold the pie chart-->
<div id="chart_div"></div>
</body>
</html>
source to share
Several things can be configured. I will try to describe them.
- Put everything in one file, it will reduce confusion and there is no real benefit to separating codes.
- Change your functions
mysql_
to at leastmysqli_
. - Move the google render javascript code block to the line that follows the DOM element that will receive it. (if you try to put content in a non-existent element, you won't see anything.)
- Your question doesn't contain any sample data, so I don't know if it provides the
$jsonTable
correct data - you'll need to estimate this yourself.
Be sure to check your code against server errors as you make the above fix.
If you have no server side errors but the graphic is not displayed then:
- Check the source code of the page and make sure the function
DateTable()
contains the correct / actual data string from your variable$jsonTable
. - Access the developer browser interface and search for error messages. These will be client side errors.
If you are still stuck, want more information, or want to see similar working examples, go here .
And here is the Google Charts Reference Page
source to share