使用MYSQL中的数据动态填充Google折线图

Question

我的最终目标是获得一个图表，显示多条线状图（取决于所涉及的年数），所有月份都显示在X轴上，金额显示在Y轴上。这是我见过的最接近我想要实现的东西：

上面的图片展示了我想要实现的内容。如前所述，X轴为月份，Y轴为数量。然后“Values”将是从查询中返回的年份，即每年都会有自己的折线图。

以下是返回json的php代码：

<?php
    $results = array('cols' => array (array('label' => 'Date', 'type' => date'),
               array('label' => 'Amount', 'type' => 'number')
               ),
               'rows' => array()
              );

    $query = $db->prepare('SELECT * FROM Claims GROUP BY EXTRACT(MONTH FROM ClaimDate ) , EXTRACT( YEAR FROM ClaimDate ) ');

    $query->execute();
    $rows1 = $query->fetchAll(PDO::FETCH_ASSOC);

    foreach($rows1 as $row)
    {
        $ClaimDate = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('Y-m-d');

        $dateArr = explode('-', $ClaimDate);
        $year = (int) $dateArr[0];
        $month = (int) $dateArr[1] - 1; 
        $day = (int) $dateArr[2];

        $results['rows'][] = array('c' => array(array('v' => "Date($year, $month, $day)"), array('v' => $row['amount'])
        ));
    }
    $json = json_encode($results, JSON_NUMERIC_CHECK);
    // print_r($json);exit;
?>

这是返回的JSON：

{"cols":[{"label":"Date","type":"date"},{"label":"Amount","type":"number"}],"rows":[{"c":[{"v":"Date(2015, 5, 23)"},{"v":6000}]},{"c":[{"v":"Date(2016, 5, 23)"},{"v":16000}]},{"c":[{"v":"Date(2015, 6, 23)"},{"v":10000}]},{"c":[{"v":"Date(2016, 6, 23)"},{"v":10000}]},{"c":[{"v":"Date(2015, 7, 23)"},{"v":5000}]},{"c":[{"v":"Date(2016, 7, 23)"},{"v":60000}]}]}

最后，这是呈现图表的函数：

<script type="text/javascript">
google.load("visualization", "1", { packages: ["corechart"]});
google.setOnLoadCallback(drawChart);

function drawChart() 
{
   var data = new google.visualization.DataTable(<?php echo json_encode($json); ?>);
   var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
   chart.draw(data, {width: 400, height: 240});
}   
</script>
<div id="line_chart"></div>

所有这些的输出如下：

如上所示，我已经取得了一定的进展。但是我现在遇到了困难，非常感谢任何帮助。

编辑：

根据Ram Kannan Raj的答案，我已经在我的查询中添加了“ORDER BY ClaimDate ASC”，折线图看起来更加整洁。

但仍未得到我期望的输出。每年应该有自己的折线图，X轴应该是所有月份。

- suo

Answer 1

尝试使用查询按升序排序ClaimDate

$query = $db->prepare('SELECT * FROM Claims GROUP BY EXTRACT(MONTH FROM ClaimDate ) , EXTRACT( YEAR FROM ClaimDate ) ORDER BY ClaimDate ASC');

- Ram Kannan Raj

Answer 2

为了得到您所描述的图表...

1）每年单独一行需要为每个列（系列）分开
2）横轴上所有月份需要离散轴（字符串值）
3）图表还需要足够宽以显示所有标签

生成如下的DataTable，其中有12行，每个月一个。

{
  "cols":[
    {"label":"Month","type":"string"},
    {"label":"2014","type":"number"},
    {"label":"2015","type":"number"},
    {"label":"2016","type":"number"}
  ],
  "rows":[
    {"c":[{"v":"January"},{"v":1000},{"v":1200},{"v":1400}]},
    {"c":[{"v":"February"},{"v":1600},{"v":1800},{"v":2000}]},
    {"c":[{"v":"March"},{"v":2200},{"v":2400},{"v":2600}]},
    ...
  ]
}

要动态创建此内容，请参见以下 PHP 中的注释

<?php
    $query = $db->prepare('SELECT * FROM Claims GROUP BY EXTRACT(MONTH FROM ClaimDate ) , EXTRACT( YEAR FROM ClaimDate ) ORDER BY ClaimDate ASC');

    $query->execute();
    $rows1 = $query->fetchAll(PDO::FETCH_ASSOC);

    $dates = array();

    // create data table with initial month column
    $results = array(
      'cols' => array(
        array('label' => 'Month', 'type' => 'string')
      ),
      'rows' => array()
    );

    // create a column for each year in the data
    $colYear = "";
    foreach($rows1 as $row) {
      $year = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('Y');
      $year = strval($year);

      if ($year != $colYear) {
        $results['cols'][] = array('label' => $year, 'type' => 'number');
        $colYear = $year;
      }
    }

    // create hAxis ticks
    $hTicks = array('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');

    // create 12 blank rows for each month
    $chartRows = array();
    foreach($hTicks as $hMonth) {
      $blankRow = array(array('v' => $hMonth));
      for ($x = 1; $x < count($results['cols']); ++$x) {
        $blankRow[] = array('v' => null);
      }
      $chartRows[] = array('c' => $blankRow);
    }

    // fill in the rows from the data
    foreach($rows1 as $row) {
      // get year and month for current row
      $year = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('Y');
      $month = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('F');
      $colYear = strval($year);

      // find month row
      for ($i = 0; $i < count($hTicks); ++$i) {
        if ($hTicks[$i] == $month) {
          // find year column
          for ($x = 0; $x < count($results['cols']); ++$x) {
            if ($results['cols'][$x]['label'] == $colYear) {
              $chartRows[$i]['c'][$x]['v'] = $row['amount'];
            }
          }
        }
      }
    }

    $results['rows'] = $chartRows;

    $json = json_encode($results);

?>

而客户端则...

请注意hAxis.ticks选项
并删除width选项

<script src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", { packages: ["corechart"]});
google.setOnLoadCallback(drawChart);

function drawChart()
{
  var data = new google.visualization.DataTable(<?php echo json_encode($json); ?>);
    var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
    chart.draw(data, {
      hAxis: {
        ticks: <?php echo json_encode($hTicks); ?>
      },
      height: 400
    });
}
</script>
<div id="line_chart"></div>

- WhiteHat

---

希望这对你有所帮助，这是一个我用来测试静态数据的PHP Fiddle。 - WhiteHat