Search code examples
chartsgoogle-visualizationline

GOOGLE LINE CHART ERROR: All series on a given axis must be of the same data type. EMPTY QUERY

i've a problem with a line chart which displays the error "All series on a given axis must be of the same data type" when a query gives null result or no rows.

If you look carefully there are 7 queries (everyone draw a line on the same chart), if any query gives back an empty result (no rows) i get the error in the topic title. When all queries are not empty, chart correctly works.

Is there a way to avoid this error ?

<?php
$curyear = date('Y');
 $con = mysqli_connect('xxx','xxx','xxx','xxx');
?>
<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', 'LaMotta'],
         <?php 
            $query = "SELECT responsabile, sum(val_fattu) as LaMotta, data_dichiarato FROM dichiarati_myennova WHERE responsabile = 'ADMRZ01' and dichiarati_myennova.DATA_DICHIARATO > (NOW() - INTERVAL 270 DAY) GROUP BY MONTH(data_dichiarato), responsabile ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("Y/m", strtotime($row['data_dichiarato']))."',".$row['LaMotta']."],";


         }
        ?> 

       ]);

var data2 = google.visualization.arrayToDataTable([
        ['Date', 'Spadone'],
         <?php 
            $query = "SELECT responsabile, sum(val_fattu) as Spadone, data_dichiarato FROM dichiarati_myennova WHERE responsabile = 'ADMRZ02' and dichiarati_myennova.DATA_DICHIARATO > (NOW() - INTERVAL 270 DAY) GROUP BY MONTH(data_dichiarato), responsabile ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("Y/m", strtotime($row['data_dichiarato']))."',".$row['Spadone']."],";

         }
        ?> 

       ]);

var data3 = google.visualization.arrayToDataTable([
        ['Date', 'Ivo'],
         <?php 
            $query = "SELECT responsabile, sum(val_fattu) as Ivo, data_dichiarato FROM dichiarati_myennova WHERE responsabile = 'ADMRZ11' and dichiarati_myennova.DATA_DICHIARATO > (NOW() - INTERVAL 270 DAY) GROUP BY MONTH(data_dichiarato), responsabile ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("Y/m", strtotime($row['data_dichiarato']))."',".$row['Ivo']."],";


         }
        ?> 

       ]);


 var data4 = google.visualization.arrayToDataTable([
        ['Date', 'Montagliani'],
         <?php 
            $query = "SELECT responsabile, sum(val_fattu) as Montagliani, data_dichiarato FROM dichiarati_myennova WHERE responsabile = 'ADMRZ12' and dichiarati_myennova.DATA_DICHIARATO > (NOW() - INTERVAL 270 DAY) GROUP BY MONTH(data_dichiarato), responsabile ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("Y/m", strtotime($row['data_dichiarato']))."',".$row['Montagliani']."],";


         }
        ?> 

       ]);

  var data5 = google.visualization.arrayToDataTable([
        ['Date', 'Galtieri'],
         <?php 
            $query = "SELECT responsabile, sum(val_fattu) as Galtieri, data_dichiarato FROM dichiarati_myennova WHERE responsabile = 'ADMRZ21' and dichiarati_myennova.DATA_DICHIARATO > (NOW() - INTERVAL 270 DAY) GROUP BY MONTH(data_dichiarato), responsabile ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("Y/m", strtotime($row['data_dichiarato']))."',".$row['Galtieri']."],";


         }
        ?> 

       ]);


  var data6 = google.visualization.arrayToDataTable([
        ['Date', 'Giacometti'],
         <?php 
            $query = "SELECT responsabile, sum(val_fattu) as Giacometti, data_dichiarato FROM dichiarati_myennova WHERE responsabile = 'ADMRZ22' and dichiarati_myennova.DATA_DICHIARATO > (NOW() - INTERVAL 270 DAY) GROUP BY MONTH(data_dichiarato), responsabile ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("Y/m", strtotime($row['data_dichiarato']))."',".$row['Giacometti']."],";


         }
        ?> 

       ]);


  var data7 = google.visualization.arrayToDataTable([
        ['Date', 'Mantovani'],
         <?php 
            $query = "SELECT responsabile, sum(val_fattu) as Mantovani, data_dichiarato FROM dichiarati_myennova WHERE responsabile = 'ADMRZ23' and dichiarati_myennova.DATA_DICHIARATO > (NOW() - INTERVAL 270 DAY) GROUP BY MONTH(data_dichiarato), responsabile ORDER BY data_dichiarato";
            $exec = mysqli_query($con,$query);
            while($row = mysqli_fetch_array($exec)){

            echo "['".date("Y/m", strtotime($row['data_dichiarato']))."',".$row['Mantovani']."],";


         }
        ?> 

       ]);



var joinedData = google.visualization.data.join(data1, data2, 'full', [[0, 0]], [1], [1]);
var joinedData2 = google.visualization.data.join(joinedData, data3, 'full', [[0, 0]], [1,2], [1]);
var joinedData3 = google.visualization.data.join(joinedData2, data4, 'full', [[0, 0, 0]], [1,2,3], [1]);
var joinedData4 = google.visualization.data.join(joinedData3, data5, 'full', [[0, 0, 0, 0]], [1,2,3,4], [1]);
var joinedData5 = google.visualization.data.join(joinedData4, data6, 'full', [[0, 0, 0, 0, 0]], [1,2,3,4,5], [1]);
var joinedData6 = google.visualization.data.join(joinedData5, data7, 'full', [[0, 0, 0, 0, 0, 0]], [1,2,3,4,5,6], [1]);

// sort by start and end dates


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

});
}

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

  <body><br /><br />  
       <div style="width:1500px;" align="center">  
                <h1 align="center" style="font-size:22px">Produzione SMARTEASY: MYENNOVA (Ultimi 10 Mesi)</h1>  
            <br />  
            <div align="center" id="piechart2" style="float:left; width: 700px; height: 390px;"></div> 
            <div align="center" id="piechart1" style="float:left; width: 700px; height: 390px;"></div>   
       </div>
    <!--Divs that will hold the charts-->
    <div id="chart_div"></div>

  </body>
</html>
Solution

  • you could use a data view to draw the chart,
    and only include columns from the joined data that have values.

    using data table method getColumnRange,
    we can determine if the column has data.

    columns that don't have data,
    getColumnRange will return null for both min & max 

    // build view columns
var viewColumns = [];
for (var i = 0; i < joinedData6.getNumberOfColumns(); i++) {
  var range = joinedData6.getColumnRange(i);

  // determine if column has values
  if (!((range.min === null) && (range.min === null))) {
    viewColumns.push(i);
  }
}

// build data view
var view = new google.visualization.DataView(joinedData6);
view.setColumns(viewColumns);

var chart = new google.visualization.LineChart(document.querySelector('#chart_div'));
chart.draw(view, {  // <-- use view to draw chart
  height: 500,
  width: 1400,
  interpolateNulls: true,
});

    see following working snippet for an example... 

    google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  var data1 = google.visualization.arrayToDataTable([
    ['Date', 'LaMotta'],
    ['test1', 5],
    ['test1', 6],
    ['test1', 7],
  ]);

  var data2 = google.visualization.arrayToDataTable([
    ['Date', 'Spadone'],
  ]);

  var data3 = google.visualization.arrayToDataTable([
    ['Date', 'Ivo'],
    ['test3', 8],
    ['test3', 9],
    ['test3', 0],
  ]);

  var data4 = google.visualization.arrayToDataTable([
    ['Date', 'Montagliani'],
  ]);

  var data5 = google.visualization.arrayToDataTable([
    ['Date', 'Galtieri'],
    ['test5', 1],
    ['test5', 2],
    ['test5', 3],
  ]);

  var data6 = google.visualization.arrayToDataTable([
    ['Date', 'Giacometti'],
  ]);

  var data7 = google.visualization.arrayToDataTable([
    ['Date', 'Mantovani'],
    ['test7', 4],
    ['test7', 5],
    ['test7', 6],
  ]);

  var joinedData = google.visualization.data.join(data1, data2, 'full', [[0, 0]], [1], [1]);
  var joinedData2 = google.visualization.data.join(joinedData, data3, 'full', [[0, 0]], [1,2], [1]);
  var joinedData3 = google.visualization.data.join(joinedData2, data4, 'full', [[0, 0, 0]], [1,2,3], [1]);
  var joinedData4 = google.visualization.data.join(joinedData3, data5, 'full', [[0, 0, 0, 0]], [1,2,3,4], [1]);
  var joinedData5 = google.visualization.data.join(joinedData4, data6, 'full', [[0, 0, 0, 0, 0]], [1,2,3,4,5], [1]);
  var joinedData6 = google.visualization.data.join(joinedData5, data7, 'full', [[0, 0, 0, 0, 0, 0]], [1,2,3,4,5,6], [1]);

  // build view columns
  var viewColumns = [];
  for (var i = 0; i < joinedData6.getNumberOfColumns(); i++) {
    var range = joinedData6.getColumnRange(i);

    // determine if column has values
    if ((range.min !== null) || (range.max !== null)) {
      viewColumns.push(i);
    }
  }

  // build data view
  var view = new google.visualization.DataView(joinedData6);
  view.setColumns(viewColumns);

  var chart = new google.visualization.LineChart(document.querySelector('#chart_div'));
  chart.draw(view, {  // <-- use view to draw chart
    height: 500,
    width: 1400,
    interpolateNulls: true,
  });
});
    <script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>