php - 使用 php 和 mysql 两个日期之间的动态 Google 图表

Question

这是我的动态谷歌图表代码:

     <?php
        $dateone="20-09-2013";
                $datetwo="15-10-2013";
               $qry1=mysql_query("SELECT count(call_type) FROM tb_name WHERE call_date>='".$dateone." 00:00:00' AND call_date<='".$dateone." 23:59:59'");
               $row1=mysql_fetch_array($qry1);
               $qry2=mysql_query("SELECT count(call_type) FROM tb_name WHERE call_date>='".$datetwo." 00:00:00' AND call_date<='".$datetwo." 23:59:59'");
               $row2=mysql_fetch_array($qry2);

               $chart= array( 
                     array('Date', 'Total Calls' ),  
                     array("$dateone", intval($row1[0])),
                     array("$datetwo", intval($row2[0])));
                $data=json_encode($chart);
        ?>
      <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript">
        google.load("visualization", "1", {packages:["corechart"]});
        google.setOnLoadCallback(drawChart);

          function drawChart() {

            var data = google.visualization.arrayToDataTable(<?php echo $data; ?>);

             var options = {'title':'Total Calls'
                          };
            var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));
            chart.draw(data, options);
          }
        </script> 

这段代码工作正常。但是如果我运行这段代码，它会在谷歌图表中显示该特定日期的详细信息。我需要在给定的两个日期之间进行所有计数。如何修改代码。

Answer 1

您只需要一个查询。对于 $dateone 和 $datetwo 之间的每个日期，您需要计算行数并选择日期。您需要按日期对其进行分组。

你的 MySQL 查询将是这样的。

SELECT count(1), DATE(call_date) FROM tb_name WHERE call_date>='".$dateone." 00:00:00' AND call_date<='".$datetwo." 23:59:59' GROUP BY DATE(call_date)

只需使用 while 语句循环遍历每一行并将其添加到图表数组中..

例如:

$qry=mysql_query("SELECT count(1) as count, DATE(call_date) as Ymd FROM tb_name WHERE call_date>='".$dateone." 00:00:00' AND call_date<='".$datetwo." 23:59:59' GROUP BY DATE(call_date)");
$chart= array( 
    array('Date', 'Total Calls' ));
while($row = mysql_fetch_assoc($qry))
{
    $chart[] = array($row['Ymd'], intval($row['count']));
}
$data=json_encode($chart);

但我建议您使用 PDO 库进行 MySQL 连接和查询..