在Google饼图中按列值/类别对行进行分组

Question

我有一个表格，看起来像下面这样：

sub_note | item_amount | sub_cat       |
---------------------------------------|
Spotify  |  $5.99      | entertainment |
Vodafone |  $35        | phone         |
Netflix  |  $5.99      | entertainment |

我想显示一个Google饼图，告诉我每个类别的支出情况，比如娱乐、电话等等。

我已经使用了Google Quick Start示例的以下改编，但是我看到的只是一个空白图表，上面写着“无数据”。

<script type="text/javascript">

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

  // Set a callback to run when the Google Visualization API is loaded.
  google.charts.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 = new google.visualization.DataTable();
    data.addColumn('string', 'Category');
    data.addColumn('number', 'Total');
    data.addRows([
     <?php 
     $query = "SELECT SUM(sub_amount) AS sub_cat, subs_total FROM subscriptions WHERE sub_user_id ='".$user_id."' GROUP BY sub_cat ORDER BY sub_cat";
      $exec = mysqli_query($connect,$query);
      while($row = mysqli_fetch_array($exec)){
      echo "['".$row['sub_cat']."',".$row['subs_total']."],";
      }
 ?>]);

    // Set chart options
    var options = {'title':'Spending by Category',
                   'width':600,
                   'height':400};

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

很明显查询代码有问题，以及它如何分组，但我正在苦思冥想来弄清楚这个问题。

任何提示都将是极好的！

更新：感谢 @WhiteHat 的 SQL 更正，以下是运行良好的代码：

<script type="text/javascript">

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

  // Set a callback to run when the Google Visualization API is loaded.
  google.charts.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 = new google.visualization.DataTable();
    data.addColumn('string', 'Category');
    data.addColumn('number', 'Total');
    data.addRows([
     <?php 
     $query = "SELECT sub_cat, SUM(sub_amount) as subs_total FROM subscriptions WHERE sub_user_id ='".$user_id."' GROUP BY sub_cat ORDER BY sub_cat";
      $exec = mysqli_query($connect,$query);
      while($row = mysqli_fetch_array($exec)){
      echo "['".$row['sub_cat']."',".$row['subs_total']."],";
      }
   ?>]);

    // Set chart options
    var options = {'title':'How Much Pizza I Ate Last Night',
                   'width':600,
                   'height':400};

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

- tristanojbacon

---

不，它们是相同的。在我发布这个主题之后，我进行了更改，因此我已经更新了上面的代码以反映当前状态。 - tristanojbacon

Answer 1

请尝试此 SQL： $query = "SELECT sub_cat, SUM(sub_amount) as subs_total FROM subscriptions WHERE sub_user_id ='".$user_id."' GROUP BY sub_cat ORDER BY sub_cat";

类别应该是第一列，小计为第二列。

- WhiteHat

---

我添加了你的SQL，它完美地工作了，谢谢！ 编辑：另外，不，我在控制台上没有收到任何错误信息。 - tristanojbacon

Answer 2

请问您能否测试一下您的SQL代码是否有返回结果？ 如果您能提供正确的数据集，我可以帮助您。 尝试在JavaScript之外使用echo或vardump来确定您的SQL是否正确。

- thecassion