How to generate bar chart according to the number of code

Question

Table name: data_detail
primary key: id
id | kpi_code | result_date | result_data | target |
1  | KPI1     | 11/13/2019  | 100         | 300    |
2  | KPI1     | 11/14/2019  | 150         | 300    |
3  | KPI2     | 11/13/2019  | 15          | 50     |
4  | KPI2     | 11/14/2019  | 30          | 50     |

<?php
require_once("dbconfig.php");
$query= "SELECT result_date, result_data FROM data_detail ";
$result = mysqli_query($conn,$query);
?>
<script type="text/javascript">
        google.charts.load('current', {'packages':['bar']});
        google.charts.setOnLoadCallback(drawChart);

        function drawChart() {

            var data = google.visualization.arrayToDataTable([
              ['Date', 'Sales'],
              <?php
                while($row = mysqli_fetch_array($result))
                {
                  echo "['" .$row["result_date"]."', ".$row["result_data"]."],";
                }
               ?>

            ]);

            var options = {
              chart: {
                title: 'Sales for this week',
                subtitle: 'Number of sales for 11-13-2019 - 11-15-2019 ',
              }
            };

            var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

            chart.draw(data, google.charts.Bar.convertOptions(options));
          }

      </script>
<div id="columnchart_material"></div>

I am expecting to create two charts due to the 2 different kpi_code in the table. But the result is it created only 1 chart with 4 bars of result_data. One of the issue should be the sql statement as I have no idea on how to get the number of difference kpi_code in the table.

Answer 1

instead of changing the sql or the php,
we can load all the data in one google data table,
then use a data view to filter each kpi,
and draw a chart for each.

first, load all the data, the same way you are, except add the kpi column...

var data = google.visualization.arrayToDataTable([
  ['KPI', 'Date', 'Sales'],
  <?php
    while($row = mysqli_fetch_array($result))
    {
      echo "['" .$row["kpi_code"]."', '" .$row["result_date"]."', ".$row["result_data"]."],";
    }
   ?>
]);

then we can use a data table method,
to get an array of the distinct kpi codes --> getDistinctValues(columnIndex)

// get distinct kpi codes
var kpiCodes = data.getDistinctValues(0);

after this, we can build a data view and a chart for each kpi...

// add a div container and chart for each kpi
kpiCodes.forEach(function (kpi, index) {
  // build data view for kpi
  var view = new google.visualization.DataView(data);

  // filter view rows for kpi code
  view.setRows(data.getFilteredRows([{
    column: 0,
    value: kpi
  }]));

  // remove kpi column from data view
  view.hideColumns([0]);

  // create kpi chart container
  container.insertAdjacentHTML('beforeEnd', '<div id="kpi-' + index + '"></div>');

  // create kpi chart
  var chart = new google.charts.Bar(document.getElementById('kpi-' + index));

  // draw kpi chart using view
  chart.draw(view, google.charts.Bar.convertOptions(options));
});

see following working snippet...

google.charts.load('current', {
  packages:['bar']
}).then(function () {
  var data = google.visualization.arrayToDataTable([
    ['KPI', 'Date', 'Sales'],
    ['KPI1', '11/13/2019', 100],
    ['KPI1', '11/14/2019', 150],
    ['KPI2', '11/13/2019', 15],
    ['KPI2', '11/14/2019', 30],
  ]);

  // use div container for all charts
  var container = document.getElementById('columnchart_material');

  // get distinct kpi codes
  var kpiCodes = data.getDistinctValues(0);

  // add a div container and chart for each kpi
  kpiCodes.forEach(function (kpi, index) {
    // options for kpi
    var options = {
      chart: {
        title: 'Sales for this week - KPI: ' + kpi,
        subtitle: 'Number of sales for 11-13-2019 - 11-15-2019 ',
      }
    };

    // build data view for kpi
    var view = new google.visualization.DataView(data);

    // filter view rows for kpi code
    view.setRows(data.getFilteredRows([{
      column: 0,
      value: kpi
    }]));

    // remove kpi column from data view
    view.hideColumns([0]);

    // create kpi chart container
    container.insertAdjacentHTML('beforeEnd', '<div id="kpi-' + index + '"></div>');

    // create kpi chart
    var chart = new google.charts.Bar(document.getElementById('kpi-' + index));

    // draw kpi chart using view
    chart.draw(view, google.charts.Bar.convertOptions(options));
  });
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="columnchart_material"></div>