个人中心
gpt4 book ai didi

mysql - Google LineChart mysql 具有更多行

转载 作者：行者123 更新时间：2023-11-29 17:02:27 24 4
gpt4 key购买 nike

我正在尝试绘制 Google 折线图，但在绘制第二条线时遇到一些问题。我正在使用 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 等行。

我希望在折线图中为我添加的每项职责添加一条线。

如何修改我的代码？是否需要添加另一个查询？还是一个系列？抱歉，我只是图表初学者

谢谢

最佳答案

为了拥有多行/系列，
谷歌数据表需要如下所示...

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

但是如果没有一堆硬编码，这将很难从查询构建

相反，添加一个责任列，
然后我们可以使用谷歌数据方法将行转换为列。
首先，数据表看起来像...... 

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

您需要保留日期，因为当我们汇总时，
月份名称将按字母顺序排序，并且顺序不正确。
例如--> 四月、八月、十二月、二月等...
我们可以稍后格式化为月份名称。

<小时/>

首先，更改查询以包含所有职责...

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 不应再用于加载图表库，
根据发行说明...

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.

这只会更改load语句，请参阅上面的代码片段...

关于mysql - Google LineChart mysql 具有更多行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52168063/

24 4 0
文章推荐： javascript - 设置 Facebook 像素以在点击事件时触发
文章推荐： javascript - Angular 路由
文章推荐： mysql - PHP 从 3 个表中选择
文章推荐： mysql - 解决 perl DBD::mysql UTF-8 错误
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com