Google LineChart mysql with more lines

google line chart php mysql
google charts - line chart with points
google material charts
google chart demo
google chart y-axis interval
sunburst chart google sheets
line chart in html5 w3schools
google sheets interactive chart

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


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>

Line Chart | Charts, Configuration Options. Name. aggregationTarget. How multiple data selections are rolled up into tooltips: 'category'  The intent is to draw a multi line chart using Google Charts, based on a mysql database. I've managed to get the data to echo from the mysql database, but it's not generating the chart. All I get is the echo, and a blank space where the chart should be. The echo is shown for debugging purposes. Here is the code :


<?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>

Creating a Google Line Chart from MYSQL data, My aim is to create multiple line charts on the same graph using data i pull from mysql database. I have the code in place but I'm missing a step  Google Line Chart Php Mysql Hello Guys, In these video we learn how to use google line chart in php. how create a dyanamic line chart in php using google chart. For More Details and For Source


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

google line chart php mysql | Line chart (Dynamic), Google Line Chart Php Mysql Hello Guys, In these video we learn how to use google line Duration: 7:47 Posted: Apr 6, 2019 I have problem i want to display some data from my mysql DB in line chart, but still doesnt work. I am tired because i dont know why, and I read many, many forums..


DIYDave/MySQL-and-Google-Line-Chart: Populate Google , How can I populate Google line chart with data from mySQL database. (single file / Inline solution). Text: I have an MCU (ESP8266) with a temperature and  “Google Charts in PHP with MySQL Database using Google API”, Pie Chart, Column Chart, Bar Chart, Line Chart. Charts or graph are used for graphical representation of data and are useful when you want to show your data or information in quick overview format. Today we will discuss about Google charts with PHP and MySQL database.


Google line chart php mysql example, Google line chart php mysql example. with data from MySql Here's an example of the PHP/MySQL code you implement I have google search and ran several  This is one more post on Google Graph or Chart API, in which we will discuss one more Google Chart type. So Here we will discuss Google Line Chart and how to dynamic make Google Line Chart by using JSON data. In this blog we will check how to create Google API Line Chart by using PHP Array and convert into JSON and data get from Mysql table.


Table reference - Data Studio Help, Tables display your data in a grid of rows and columns. Click +BLEND DATA to see data from multiple data sources in the same chart. Google Sheets; Google BigQuery; MySQL; PostgreSQL; Cloud SQL for MySQL; File Import; Google  You load the Google Visualization API (although with the 'line' package instead of the 'corechart' package), define your datatable, and then create an object (but of class google.charts.Line instead of google.visualization.LineChart).