How to query to DB & plot the values on dashboard dynamically?

Question

I'm very new to dashboard stuffs.Learning PHP & javascript. I'm trying to create a pie-chart with the help of already available google-chart. I could able to make it (Because, data is hard coded). I'm trying same to plot the pie-chart with dynamic values (querying to DB & plot the values on pie-chart). I'm trying to do it, but couldn't. Could you please help me to achieve this (MySQL, say 2 columns Name & Score).

Working code [For static data]:

<html>
  <head>
    <script type="text/javascript" src="loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);
      function drawChart() {

        **var data = google.visualization.arrayToDataTable([
          ['Task', 'Hours per Day'],
          ['Work',     11],
          ['Eat',      2],
          ['Commute',  2],
          ['Watch TV', 2],
          ['Sleep',    7]
        ]);**

        var options = {
          title: 'My Daily Activities'
        };

        var chart = new google.visualization.PieChart(document.getElementById('piechart'));

        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="piechart" style="width: 900px; height: 500px;"></div>
  </body>
</html>

I understand above highlighted part does the work of loading static data.

Tried embedding above script with db related PHP. Probably, i might be missing to call it in right way. Could you please help me to provide the missing interface. I'm very new to all these technologies.

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbName = "test";
$conn = new mysqli($servername, $username, $password, $dbName);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$query = "SELECT * FROM student";

$result = $conn->query($query);

$jsonArray = array();

if ($result->num_rows > 0) {

  while($row = $result->fetch_assoc()) {
    $jsonArrayItem = array();
    $jsonArrayItem['label'] = $row['Name'];
    $jsonArrayItem['value'] = $row['Scores'];
    array_push($jsonArray, $jsonArrayItem);
  }
}

$conn->close();

header('Content-type: application/json');

echo json_encode($jsonArray);
?>

score 0 · Answer 1 · edited May 23 '17 at 11:59

Intoduction

Ok, if your code works correctly I suppose currently you have made a php script that spits out a JSON document in the format you need.

Now you need to actually load the data with javascript and feed it into the charting API.

So instead of feeding the hardcoded array at var data = google.visualization.arrayToDataTable you need to load it from the php script.

Have a look at the following links that solve this problem either with pure JS or with JQuery:

Example with JQuery

Keep in mind calls are asynchronous so you need to have your charting logic triggered in ( or by ) the listener that handles the ajax call.

$.getJSON('http://localhost/myApp/myScript.php&callback', function(data) {
    var data = google.visualization.arrayToDataTable(data);

        var options = {
          title: 'My Daily Activities'
        };

        var chart = new google.visualization.PieChart(document.getElementById('piechart'));

        chart.draw(data, options);
});

How to query to DB & plot the values on dashboard dynamically?

1 Answers1

Intoduction

Example with JQuery