Google LineChart mysql with more lines

Question

I'm trying to draw a Google Line Chart but I've some problem drawing the second line. I'm using data from my MySQL database which allow me to display the sum amount of sold pieces grouped by months in 2018.

But I'd like to display the sum amount divided for every responsibility group which is a string value inside my every row.

This is my code:

<?php
 $curyear = date('Y');
 $con = mysqli_connect('xxxx','xxxx','xxxx','xxxx');
?>
<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

        // Create the data table.
       var data = google.visualization.arrayToDataTable([
        ['Date', 'Pezzi',],
         <?php 
            $query = "SELECT responsabile, sum(n_sim)+sum(n_accessi) as pezzi, data_dichiarato FROM dichiarati WHERE responsabile = 'ADMRZ01' and n_ragsoc != 'DICHIARATO ZERO' and YEAR(DATA_DICHIARATO) = '$curyear' GROUP BY MONTH(data_dichiarato) ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("M", strtotime($row['data_dichiarato']))."',".$row['pezzi']."],";

         }

        ?>

       ]);


        // Set chart options
        var options = {'title':'SIM CONSEGNATE NEL <?php echo $curyear; ?>',
                       'width':1200,
                       'height':300
                       // isStacked: true
                       };



        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);



      }
    </script>
  </head>

  <body>
    <!--Divs that will hold the charts-->
    <div id="chart_div"></div>

  </body>
</html>

So as you can see I have a single query which extracts data just for responsibility ADMRZ01. But in my database, I've rows with ADMRZ02, ADMRZ11, and others.

I'd like to have a line in Linechart for every responsibility I'm adding.

How can I modify my code? Is it necessary to add another query? Or a Series? I'm sorry I'm just a beginner in charts

Thanks

score 0 · Answer 1 · answered Sep 04 '18 at 16:52

in order to have multiple lines / series,
the google data table will need to look something like the following...

var data = google.visualization.arrayToDataTable([
  ['Date', 'ADMRZ01', 'ADMRZ02', 'ADMRZ11'],
  ['Jan', 10, 20, 30],
  ...
]);

but this would very difficult to build from the query, without a bunch of hard-coding

instead, add a column for responsibility,
then we can use google data methods to transform the rows to columns.
to begin, the data table will look something like...

var data = google.visualization.arrayToDataTable([
  ['Date', 'Responsibility', 'Pezzi'],
  ['01/01/2018', 'ADMRZ01', 10],
  ['01/01/2018', 'ADMRZ02', 20],
  ['01/01/2018', 'ADMRZ11', 30],
  ['02/01/2018', 'ADMRZ01', 40],
  ['02/01/2018', 'ADMRZ02', 50],
  ['02/01/2018', 'ADMRZ11', 60],
  ['03/01/2018', 'ADMRZ01', 70],
  ['03/01/2018', 'ADMRZ02', 80],
  ['03/01/2018', 'ADMRZ11', 90],
]);

you'll want to keep the dates, because when we aggregate,
the month names will get sorted alphabetically, and will be out of order.
e.g. --> Apr, Aug, Dec, Feb, etc...
we can format as month name later.

first, change the query to include all responsibilities...

var data = google.visualization.arrayToDataTable([
  ['Date', 'Responsabile', 'Pezzi'],
    <?php
      $query = "SELECT data_dichiarato, responsabile, sum(n_sim)+sum(n_accessi) as pezzi FROM dichiarati WHERE n_ragsoc != 'DICHIARATO ZERO' and YEAR(DATA_DICHIARATO) = '$curyear' GROUP BY data_dichiarato, responsabile ORDER BY data_dichiarato";
      $exec = mysqli_query($con,$query);
      while($row = mysqli_fetch_array($exec)){
        echo "['".$row['data_dichiarato']."','".$row['responsabile']."'".$row['pezzi']."],";
      }
    ?>
]);

then you can use the following javascript to transform the rows to columns,
see following working snippet...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  // create data table
  var data = google.visualization.arrayToDataTable([
    ['Date', 'Responsibility', 'Pezzi'],
    ['01/01/2018', 'ADMRZ01', 10],
    ['01/01/2018', 'ADMRZ02', 20],
    ['01/01/2018', 'ADMRZ11', 30],
    ['02/01/2018', 'ADMRZ01', 40],
    ['02/01/2018', 'ADMRZ02', 50],
    ['02/01/2018', 'ADMRZ11', 60],
    ['03/01/2018', 'ADMRZ01', 70],
    ['03/01/2018', 'ADMRZ02', 80],
    ['03/01/2018', 'ADMRZ11', 90],
  ]);

  // create data view
  var view = new google.visualization.DataView(data);

  // column arrays
  var aggColumns = [];
  var viewColumns = [{
    // convert string to date
    calc: function (dt, row) {
      return new Date(dt.getValue(row, 0));
    },
    label: data.getColumnLabel(0),
    type: 'date'
  }];

  // build view & agg columns for each responsibility
  data.getDistinctValues(1).forEach(function (responsibility, index) {
    viewColumns.push({
      calc: function (dt, row) {
        if (dt.getValue(row, 1) === responsibility) {
          return dt.getValue(row, 2);
        }
        return null;
      },
      label: responsibility,
      type: 'number'
    });

    aggColumns.push({
      aggregation: google.visualization.data.sum,
      column: index + 1,
      label: responsibility,
      type: 'number'
    });
  });

  // set view columns
  view.setColumns(viewColumns);

  // sum view by date
  var aggData = google.visualization.data.group(
    view,
    [0],
    aggColumns
  );

  // draw chart
  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
  chart.draw(aggData, {
    title: 'SIM CONSEGNATE NEL...',
    hAxis: {
      format: 'MMM',
      ticks: view.getDistinctValues(0)
    }
  });
});

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

note: jsapi should no longer be used to load the charts library,
according to the release notes...

The version of Google Charts that remains available via the jsapi loader is no longer being updated consistently. The last update, for security purposes, was with a pre-release of v45. Please use the new gstatic loader.js from now on.

this will only change the load statement, see above snippet...

Now i've the problem with the date sorted alphabetically on the x line. I'm, trying to find the solution. — Francesco Minniti, Sep 04 '18 at 20:04

Francesco Minniti · Answer 2 · 2018-09-04T20:40:48.120

0

Found another solution with method google.visualization.data.join and it works but I've this silly problem with alphabetical months.

edited Sep 04 '18 at 20:40

answered Sep 04 '18 at 20:28

Francesco Minniti

31
3

score 0 · Answer 3 · answered Sep 04 '18 at 20:33

<?php
$curyear = date('Y');
$con = mysqli_connect('localhost','root','root','tetra');
?>

<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {
var data1 = google.visualization.arrayToDataTable([
        ['Date', 'Pezzi01'],
         <?php 
            $query = "SELECT responsabile, sum(n_sim)+sum(n_accessi) as pezzi01, data_dichiarato FROM dichiarati WHERE responsabile = 'ADMRZ01' and n_ragsoc != 'DICHIARATO ZERO' and YEAR(DATA_DICHIARATO) = '$curyear' GROUP BY MONTH(data_dichiarato), responsabile order by data_dichiarato asc";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("M", strtotime($row['data_dichiarato']))."',".$row['pezzi01']."],";


         }
        ?> 

       ]);

var data2 = google.visualization.arrayToDataTable([
        ['Date', 'Pezzi02'],
         <?php 
            $query = "SELECT responsabile, sum(n_sim)+sum(n_accessi) as pezzi02, data_dichiarato FROM dichiarati WHERE responsabile = 'ADMRZ02' and n_ragsoc != 'DICHIARATO ZERO' and YEAR(DATA_DICHIARATO) = '$curyear' GROUP BY MONTH(data_dichiarato), responsabile order by data_dichiarato asc";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("M", strtotime($row['data_dichiarato']))."',".$row['pezzi02']."],";

         }
        ?> 

       ]);


var joinedData = google.visualization.data.join(data1, data2, 'full', [[0, 0]], [1], [1]);


var chart = new google.visualization.LineChart(document.querySelector('#chart_div'));
chart.draw(joinedData, {
    height: 300,
    width: 600,
    interpolateNulls: true,

});
}

google.load('visualization', '1', {packages:['corechart'], callback: drawChart});
    </script>
  </head>

  <body>
    <!--Divs that will hold the charts-->
    <div id="chart_div"></div>

  </body>
</html>

Nothing ... always date on haxis is alpahbetically showing months. — Francesco Minniti, Sep 04 '18 at 21:34

Google LineChart mysql with more lines

3 Answers3