谷歌线条图mysql与更多的线

x7yiwoj4  于 2021-06-20  发布在  Mysql
关注(0)|答案(3)|浏览(353)

我想画一个谷歌折线图，但我有一些问题，绘制第二条线。我使用的是mysql数据库中的数据，它允许我显示2018年按月份分组的总销量。
但是我想显示为每个责任组划分的金额总和，这是我的每一行中的一个字符串值。
这是我的密码：

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

如您所见，我有一个查询，它只提取admrz01的数据。但在我的数据库中，有admrz02、admrz11和其他行。
我想为我要增加的每一项责任画一个折线图。
如何修改代码？是否需要添加另一个查询？还是连续剧？对不起，我只是个图表初学者
谢谢

mysqlchartsLineChartgoogle-visualization

3条答案

按热度按时间
new9mtju

new9mtju1#

为了拥有多条生产线/系列，
google数据表需要如下所示。。。

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

但是，如果没有一堆硬编码，从查询中构建它将非常困难
相反，增加一列责任，
然后我们可以使用google数据方法将行转换为列。
首先，数据表看起来像。。。

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],
]);

你要保留日期，因为当我们合计时，
月份名称将按字母顺序排列，并且将不按顺序排列。
e、 g.-->4月、8月、12月、2月等。。。
我们可以在以后格式化为月份名称。
首先，将查询更改为包含所有职责。。。

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']."],";
      }
    ?>
]);

然后可以使用以下javascript将行转换为列，
请参阅以下工作片段。。。

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>

注： jsapi 不应再用于加载图表库，
根据发行说明。。。
谷歌图表的版本仍然可以通过 jsapi 加载程序不再持续更新。为了安全起见，上一次更新是v45的预发行版。请使用新的gstatic loader.js 从现在开始。
这只会改变 load 语句，请参见上面的代码段。。。

赞(0）分享  回复(0）举报  2021-06-20
5jvtdoz2

5jvtdoz22#

找到了另一个方法google.visualization.data.join的解决方案，它可以工作，但我有一个按字母顺序月份的愚蠢问题。

赞(0）分享  回复(0）举报  2021-06-20
jckbn6z7

jckbn6z73#

<?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>
赞(0）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com