时间戳

首页 > 解决方案 > 具有两个图表系列的 Google 图表 (SQL/PHP)

php - 具有两个图表系列的 Google 图表 (SQL/PHP)

问题描述

我尝试使用 API 谷歌图表构建图表，但我非常迷茫......我在 PhpMyAdmin 上有一个数据库，其中有 2 列：numberPeople 和 date。

所以，我想在图表上显示 N 年的人数和 N-1 年的其他行......

但我不明白。你可以帮帮我吗？我把我所做的放在附录中

<?php

$pdoSynchro = new PDO("mysql:dbname=store17;host=localhost","root","");

$date_now = date("Y");
$date_old = date("Y")-1;

$anneeN = $pdoSynchro->query('SELECT nombrePersonne, date FROM compteur_client WHERE YEAR(date) ='.$date_now);
$anneeN1 = $pdoSynchro->query('SELECT nombrePersonne, date FROM compteur_client WHERE YEAR(date) ='.$date_old);



$table = array();
$table['cols'] = array(

    // Labels for your chart, these represent the column titles
    // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
    array('label' => 'Date', 'type' => 'string'),
    array('label' => 'Nbr de personne en '.$date_now, 'type' => 'number'),
    array('label' => 'Nbr de personne en '.$date_old, 'type' => 'number')

);

$rows = array();

while ($r = $anneeN->fetch()) {
    $temp = array();
    $temp[] = array('v' => $r['date']);
    $temp[] = array('v' => (int) $r['nombrePersonne']);
    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);


var_dump($jsonTable);
//echo $jsonTable;
?>

<html>
  <head>
    <!--Load the Ajax API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">

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

    // Set a callback to run when the Google Visualization API is loaded.
    google.charts.setOnLoadCallback(drawChart);

   

    function drawChart() 
    {

        
      // Create our data table out of JSON data loaded from server.
        var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);
        var options = {
        title: 'Nombre de clients par jour',
          width: 1000,
          height: 500,
        };



      // Do not forget to check your div ID
      var chart = new google.visualization.LineChart(document.getElementById('linechart_material'));
      chart.draw(data, options);
    }

    </script>
  </head>

  <body>
    <!--this is the div that will hold the pie chart-->
    <div id="linechart_material"></div>
  </body>
</html>

<?php

$pdoSynchro = new PDO("mysql:dbname=store17;host=localhost","root","");

$query = $pdoSynchro->query("SELECT date, nombrePersonne FROM compteur_client"); // select column
?>

<!DOCTYPE html>
<html>
<head>
    <title>Massive Electronics</title>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">

        google.charts.load('current', {'packages':['corechart']});
        google.charts.setOnLoadCallback(drawChart);
        function drawChart(){
            var data = new google.visualization.DataTable();
            var data = google.visualization.arrayToDataTable([
                ['Date','Nombre client par jour'],
                <?php
                    while($row = $query->fetch()){
                        echo "['".$row["date"]."', ".$row["nombrePersonne"]."],";
                    }
                ?>
               ]);

            var options = {
                title: 'Nombre client/J',
                curveType: 'function',
                legend: { position: 'bottom' }
            };

            var chart = new google.visualization.AreaChart(document.getElementById('areachart'));
            chart.draw(data, options);
        }

    </script>
</head>
<body>
     <div id="areachart" style="width: 900px; height: 400px"></div>
</body>
</html>

对于我的 JSON 数据的结果（它是数据的一部分，因为并非所有数据都没有显示）：

'{"cols":[
    {"label":"Date N","type":"string"},
    {"label":"Nbr de personne en 2018","type":"number"},
    {"label":"Nbr de personne en 2017","type":"number"}],
"rows":[
    {"c":[{"v":"2017-07-05 00:00:00.000000"},{"v":null},{"v":10}]},
    {"c":[{"v":"2017-07-21 00:00:00.000000"},{"v":null},{"v":15}]},
    {"c":[{"v":"2017-07-22 00:00:00.000000"},{"v":null},{"v":12}]},
    {"c":[{"v":"2017-07-23 00:00:00.000000"},{"v":null},{"v":13}]},
    {"c":[{"v":"2017-07-19 00:00:00.000000"},{"v":null},{"v":15}]},
    {"c":[{"v":"2017-07-19 00:00:00.000000"}'

非常感谢您的帮助 100000x 谢谢！

编辑

在此处输入图像描述

和 JSON 的结果：

{"cols":[
   {"label":"Date","type":"date"},
   {"label":"Nbr de personne en 2018","type":"number"},
   {"label":"Nbr de personne en 2017","type":"number"}],
"rows":[
   {"c":[{"v":"Date(2018, -1, , , , )","f":"2017--"},{"v":null},{"v":10}]},
   {"c":[{"v":"Date(2018, -1, , , , )","f":"2017--"},{"v":null},{"v":15}]},
   {"c":[{"v":"Date(2018, -1, , , , )","f":"2017--"},{"v":null},{"v":12}]},
   {"c":[{"v":"Date(2018, -1, , , , )","f":"2017--"},{"v":null},{"v":13}]},
   {"c":[{"v":"Date(2018, -1, , , , )","f":"2017--"},{"v":null},{"v":15}]}'

我有一条消息：'a.getTime' 不是函数。

标签： phpsqldatabasechartsgoogle-visualization

解决方案

由于您有两个不同的查询，您需要为每个查询加载单独的行，
但您仍然需要填充两列...

只需null在查询中未使用的列中使用，
因此...... 

// old date
while ($r = $anneeN1->fetch()) {
    $temp = array();
    $temp[] = array('v' => $r['date']);
    $temp[] = array('v' => null);
    $temp[] = array('v' => (int) $r['nombrePersonne']);
    $rows[] = array('c' => $temp);
}

// now date
while ($r = $anneeN->fetch()) {
    $temp = array();
    $temp[] = array('v' => $r['date']);
    $temp[] = array('v' => (int) $r['nombrePersonne']);
    $temp[] = array('v' => null);
    $rows[] = array('c' => $temp);
}

注意：首先加载旧的可能是有意义的，所以日期的顺序是正确的......

编辑

要逐年比较，首先，我们需要使用真实日期，而不是字符串......

将第一列'string'从'date'... 

    array('label' => 'Date', 'type' => 'date'),

这里... 

$table = array();
$table['cols'] = array(
    array('label' => 'Date', 'type' => 'date'),
    array('label' => 'Nbr de personne en '.$date_now, 'type' => 'number'),
    array('label' => 'Nbr de personne en '.$date_old, 'type' => 'number')
);

接下来，日期需要在 x 轴上为同一年，
因此我们需要将旧日期更改为当前年份。
但是，我们可以使用格式化值在工具提示中显示真实日期...

'v'= 值，'f'= 格式化值

我们可以使用 google 的 json 日期字符串来传递真实日期 -->"Date(y, m, d, h, n, s)"

所以在“旧”例程中，我们使用当前年份创建日期，
并以格式化值提供真实日期，
然后以相同方式格式化“现在”日期...... 

// old date
while ($r = $anneeN1->fetch()) {
    // value - old date converted to now year
    $rowDate = "Date(".$date_now.", ".((int) date_format($r['date'], 'm') - 1).", ".date_format($r['date'], 'd').", ".date_format($r['date'], 'H').", ".date_format($r['date'], 'i').", ".date_format($r['date'], 's').")";

    // formatted value - real value
    $rowStr = $date_old."-".date_format($r['date'], 'm')."-".date_format($r['date'], 'd');

    $temp = array();
    $temp[] = array('v' => $rowDate, 'f' => $rowStr);  // <-- use formatted value for real date
    $temp[] = array('v' => null);
    $temp[] = array('v' => (int) $r['nombrePersonne']);
    $rows[] = array('c' => $temp);
}

// now date
while ($r = $anneeN->fetch()) {
    $rowDate = "Date(".$date_now.", ".((int) date_format($r['date'], 'm') - 1).", ".date_format($r['date'], 'd').", ".date_format($r['date'], 'H').", ".date_format($r['date'], 'i').", ".date_format($r['date'], 's').")";
    $rowStr = $date_now."-".date_format($r['date'], 'm')."-".date_format($r['date'], 'd');

    $temp = array();
    $temp[] = array('v' => $rowDate, 'f' => $rowStr);
    $temp[] = array('v' => (int) $r['nombrePersonne']);
    $temp[] = array('v' => null);
    $rows[] = array('c' => $temp);
}

要在 x 轴上显示月份缩写，请将其添加到图表选项中... 

hAxis: {format: 'MMM'}

编辑 2

需要将日期字符串从查询转换为实际日期，请参阅 -->$realDate 

// old date
while ($r = $anneeN1->fetch()) {
    $realDate = date_create($r['date']);

    // value - old date converted to now year
    $rowDate = "Date(".$date_now.", ".((int) date_format($realDate, 'm') - 1).", ".date_format($realDate, 'd').", ".date_format($realDate, 'H').", ".date_format($realDate, 'i').", ".date_format($realDate, 's').")";

    // formatted value - real value
    $rowStr = $date_old."-".date_format($realDate, 'm')."-".date_format($realDate, 'd');

    $temp = array();
    $temp[] = array('v' => $rowDate, 'f' => $rowStr);
    $temp[] = array('v' => null);
    $temp[] = array('v' => (int) $r['nombrePersonne']);
    $rows[] = array('c' => $temp);
}

// now date
while ($r = $anneeN->fetch()) {
    $realDate = date_create($r['date']);
    $rowDate = "Date(".$date_now.", ".((int) date_format($realDate, 'm') - 1).", ".date_format($realDate, 'd').", ".date_format($realDate, 'H').", ".date_format($realDate, 'i').", ".date_format($realDate, 's').")";
    $rowStr = $date_now."-".date_format($realDate, 'm')."-".date_format($realDate, 'd');

    $temp = array();
    $temp[] = array('v' => $rowDate, 'f' => $rowStr);
    $temp[] = array('v' => (int) $r['nombrePersonne']);
    $temp[] = array('v' => null);
    $rows[] = array('c' => $temp);
}

推荐阅读