Google charts get data from a row

Question

I have a table of different hours (teaching hours, service hours and research hours) done by a staff. I need to plot bar chart and pie chart of the staff but the x-axis and legends are just a repeat of the hours

Example of the data from sql after the sql statement

  $query ="SELECT teachinghours ,servicehours ,researchhours 
   FROM staff_hour sh , staff s
   WHERE s.staffid = sh.staffid
   AND `staffperiodyear`= 'FY2018'
   AND s.staffid in (SELECT s.staffid from staff s where s.username = '$staffid1')";
 $res=mysqli_query($connect,$query);

|     teachinghours   |     servicehours | researchhours   |
|---------------------|------------------|-----------------|
|          1379       |         99       |     247         |
|---------------------|------------------|-----------------|

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['bar']}).then(function() {
google.charts.load('current', {'packages':['bar']}).then(function() {
         var barsButton = document.getElementById('b1');
         var pieButton = document.getElementById('b2');
         barsButton.onclick = function() {
           drawSarahChart('ColumnChart');
         }
         pieButton.onclick = function() {
           drawSarahChart('PieChart');
         }
         drawSarahChart('ColumnChart');
       });
       function drawSarahChart(chartType) {
         var chart = new google.visualization.ChartWrapper({
         containerId: 'Sarah_chart_div'});
         var data = google.visualization.arrayToDataTable([
               ['teachinghours','Hour'],
               <?php 
                    while($row=mysqli_fetch_array($res))
                    {
                    echo "['".$row["servicehours"]."', ".$row[1]."],";
                    echo "['".$row["teachinghours"]."', ".$row[0]."],";
                    echo "['".$row["researchhours"]."', ".$row[2]."],";} 
                     ?>]);
         var options = {
           title: 'FY 2018',
           width: 400,
           height: 300,
           legend: { position: 'top', alignment: 'start' },
           tooltip: { isHtml: true }
         };
         chart.setOptions(options);
         chart.setChartType(chartType);
         chart.setDataTable(data);
         chart.draw();

The code i have shows the screenshot below The expected result is that the x-axis of bar chart should show teachinghours,researchhours and servicehours, both piechart and barchart legend should reflect that as well

Answer 1

if you want the words --> 'teachinghours', 'servicehours', 'researchhours'

to be on the x-axis, then the data needs to be structured as follows...

var data = google.visualization.arrayToDataTable([
  ['Type', 'Hours'],
  ['teachinghours', 1379],
  ['servicehours', 99],
  ['researchhours', 247]
]);

see following working snippet...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  var data = google.visualization.arrayToDataTable([
    ['Type', 'Hours'],
    ['teachinghours', 1379],
    ['servicehours', 99],
    ['researchhours', 247]
  ]);

  var container = document.getElementById('chart_div');
  var chart = new google.visualization.ColumnChart(container);

  var options = {
    title: 'FY 2018',
    width: 400,
    height: 300,
    legend: { position: 'top', alignment: 'start' },
    tooltip: { isHtml: true }
  };

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

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

EDIT

as for loading the data table, you can simply hard code the column name...

var data = google.visualization.arrayToDataTable([
  ['teachinghours','Hour'],
  <?php 
    while($row=mysqli_fetch_array($res))
    {
      echo "['servicehours', ".$row["servicehours"]."],";
      echo "['teachinghours', ".$row["teachinghours"]."],";
      echo "['researchhours', ".$row["researchhours"]."],";
    } 
  ?>
]);