Google Pie Charts and PHP

Question

I would like to have a Google pie chart on my website. The pie chart would be filled with data from the database. I was loooking at some examples at https://developers.google.com/chart/interactive/docs/php_example, but I'm lost when it comes to the JSON format.

Here are some examples:

 <html>
   <head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="jquery-1.6.2.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() {
  var jsonData = $.ajax({
      url: "getData.php",
      dataType:"json",
      async: false
      }).responseText;

  // Create our data table out of JSON data loaded from server.
  var data = new google.visualization.DataTable(jsonData);

  // Instantiate and draw our chart, passing in some options.
  var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
  chart.draw(data, {width: 400, height: 240});
}

</script>
</head>

<body>
  <!--Div that will hold the pie chart-->
  <div id="chart_div"></div>
</body>
</html>

And here is the snippet where I get lost (getData.php):

 <?php 

// This is just an example of reading server side data and sending it to the client.
// It reads a json formatted text file and outputs it.

 $string = file_get_contents("sampleData.json");
 echo $string;

// Instead you can query your database and parse into JSON etc etc

 ?>

I have data stored in a database and not in JSON format. How do I work with the JSON format using MySQL database queries? If you have some examples or demos, I would appreciate it.

score 1 · Answer 1 · edited May 23 '17 at 10:09

1

The first thing to do would be to have a look at the PHP manual about the json_encode() method. It provides examples on how to generate JSON with PHP.

Here is a short example from another similar SO question:

// Get your data from DB
$sth = mysql_query("SELECT ...");
// Create an array
$rows = array();
// Loop over the DB result and add it to your array
while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
}
// Use json_encode() to turn the array into JSON
print json_encode($rows);

If you need to rename your database columns, so that your JSON-data get other names on the properties than those used in your DB, you can use AS in your SQL.

SELECT blog_title as title ...

edited May 23 '17 at 10:09

Community

1
1

answered Jul 15 '12 at 17:52

Christofer Eliasson

32,939
7
74
103

Thanks for help. Do I have to set labels inside json or in the options of api? – Mitja Rogl Jul 15 '12 at 18:13
@extra90 I believe the name of the properties in you JSON-data will be used as labels on the chart, if that is what you mean? – Christofer Eliasson Jul 15 '12 at 18:26
I was looking documenation for google charts and json has to be right json format. json_encode is not enough. Do you have any experiences? – Mitja Rogl Jul 15 '12 at 18:33
@extra90 Don't have any experience of it, but if you describe what your JSON will have to look like, I'm sure we can set something up using `json_encode()`, it should be sufficient. – Christofer Eliasson Jul 15 '12 at 18:39
It has to be something like that: https://developers.google.com/chart/interactive/docs/php_example – Mitja Rogl Jul 15 '12 at 18:49

score 0 · Answer 2 · answered Mar 12 '13 at 08:56

just echo php result in var data = new google.visualization.DataTable([<?php echo $result ;?>]);

u will get data from database as per as below format

             ['Task', 'Hours per Day'],
                      ['Work',     11],
                      ['Eat',      2],
                      ['Commute',  2],
                      ['Watch TV', 2],
                      ['Sleep',    7]

i think it's useful to u ,i am use same logic in heat map

Google Pie Charts and PHP

2 Answers2