How to create a chart from mysql data? (using Google visualisation api)

Question

Hello,

I have some data and want to create some dynamic charts. I have looked on Google visualisation api .. It looks great but the problem is I am not very familiar with it. Any ideas, how I can set the data.setValue from mysql data.

  <script type='text/javascript'>
   google.load('visualization', '1', {'packages': ['geomap']});
   google.setOnLoadCallback(drawMap);

    function drawMap() {
      var data = new google.visualization.DataTable();
      data.addRows(6);
      data.addColumn('string', 'Country');
      data.addColumn('number', 'Popularity');
      data.setValue(0, 0, 'Germany');
      data.setValue(0, 1, 200);
      data.setValue(1, 0, 'United States');
      data.setValue(1, 1, 300);
      data.setValue(2, 0, 'Brazil');
      data.setValue(2, 1, 400);
      data.setValue(3, 0, 'Canada');
      data.setValue(3, 1, 500);
      data.setValue(4, 0, 'France');
      data.setValue(4, 1, 600);
      data.setValue(5, 0, 'RU');
      data.setValue(5, 1, 700);

      var options = {};
      options['dataMode'] = 'regions';

      var container = document.getElementById('map_canvas');
      var geomap = new google.visualization.GeoMap(container);
      geomap.draw(data, options);
  };
  </script>

I can create chart using some other methods but just interested in using Google Visualisation API.

Thanks.

Answer 1

i recommend http://pchart.sourceforge.net/ pchart for graphing. it works perfectly.

Answer 2

Update:

Have a look at how you can add data to the chart. You have the possibility to add data in JSON.

The only thing you have to do is to prepare a corresponding PHP array. Then you can serialize this array and set the data. E.g.

<?php 
// $data is an array and already has the correct structure...
$jdata = json_encode($data);

?>

<!-- later ... -->

<script type='text/javascript'>
   google.load('visualization', '1', {'packages': ['geomap']});
   google.setOnLoadCallback(drawMap);

   function drawMap() {
      var data = new google.visualization.DataTable(<?php echo $jdata ?>);
      var options = {};
      options['dataMode'] = 'regions';

      var container = document.getElementById('map_canvas');
      var geomap = new google.visualization.GeoMap(container);
      geomap.draw(data, options);
   };
</script>

I recommend to read the documentation / API reference. I basically found this just by searching...

---

Without further information we cannot give a specific answer but a general approach is:

Assuming that you already fetched the records from your DB in a result set $results than you can just loop over it:

<?php foreach($results as $row): ?>

    data.setValue(<?php echo $row['column1']; ?>, <?php echo $row['column2']; ?>);
    // depends on what type of char you want to create, on your actual data etc.

<?php endforeach; ?>