javascript - 来自 html/javascript/php 中的 json 响应的折线图

Question

我正在尝试在 html/javascript 中创建 json 响应 (sql) 的单折线图。我试图创建的是一个只有 一个 行的 tag_name 的图表，在下面的例子中:“phone 4”。此行应表示所有 json 字符串的所有值和日期时间。

JSON 响应:

[{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:20:09"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:19:49"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:19:29"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:19:09"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:18:49"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:18:29"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:18:09"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:17:49"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:17:29"},
{"tag_name":"phone 4","value":"4","datetime":"2017-10-03 14:17:09"}]

到目前为止我已经达到了什么(不多):

<script type="text/javascript">
        google.charts.load('current', {'packages': ['line']});
        google.charts.setOnLoadCallback(drawChart);

        function drawChart() {
             var data = new google.visualization.DataTable();
             data.addColumn('number', 'Datetime');
             data.addColumn('number', 'Values');

            var options = {
                chart: {
                    title: 'Values',
                },
                width: 900,
                height: 500
            };

            var chart = new google.charts.Line(document.getElementById('chart_div'));
            chart.draw(data, google.charts.Line.convertOptions(options));
        }
</script>

我的 php 代码是这样的:

 require_once 'db_connection.php';
 header('Content-type: application/json');

 $data = array();
 $Chart = "SELECT *
             FROM (
                  SELECT *,
                  @rn := if( @tag_name = tag_name,
                   @rn + 1,
                   if(@tag_name := tag_name, 1, 1)
                ) as tag_count
            FROM waardes 
            CROSS JOIN ( SELECT @rn := 0, @tag_name := '') as vars
            ORDER BY tag_name
        ) as T          
      WHERE tag_count < 11 AND machine_id LIKE 3 AND tag_name LIKE 'phone 4'
      ORDER BY datetime DESC";
 $result = mysqli_query($connection, $Chart);

while ($row = mysqli_fetch_array($result)) {
     if ($row["int_value"] == 0 && $row["real_value"] == 0.0 && $row["bool_value"] != "") {
    array_push($data, array('tag_name' => $row['tag_name'], 'value' => $row['bool_value'], 'datetime' => $row['datetime']));
} elseif ($row["int_value"] == 0 && $row["real_value"] != 0 && $row["bool_value"] == "") {
    array_push($data, array('tag_name' => $row['tag_name'], 'value' => $row['real_value'], 'datetime' => $row['datetime']));
} elseif ($row["int_value"] != 0 && $row["real_value"] == 0 && $row["bool_value"] == "") {
    array_push($data, array('tag_name' => $row['tag_name'], 'value' => $row['int_value'], 'datetime' => $row['datetime']));
}
}

 echo json_encode($data);

Answer 1

首先，不建议在同一个文件中混合使用 php 和 javascript

将 html/javascript 与 php 分开

使用ajax从php获取数据到javascript

对于谷歌图表，请使用此处找到的 json 格式...
Format of the Constructor's JavaScript Literal data Parameter

这将允许您直接从 json 创建 google 数据表

getdata.php

require_once 'db_connection.php';
header('Content-type: application/json');

$data = array();
$Chart = "SELECT *
             FROM (
                  SELECT *,
                  @rn := if( @tag_name = tag_name,
                   @rn + 1,
                   if(@tag_name := tag_name, 1, 1)
                ) as tag_count
            FROM waardes
            CROSS JOIN ( SELECT @rn := 0, @tag_name := '') as vars
            ORDER BY tag_name
        ) as T
      WHERE tag_count < 11 AND machine_id LIKE 3 AND tag_name LIKE 'phone 4'
      ORDER BY datetime DESC";
$result = mysqli_query($connection, $Chart);


$data = array();
$data['cols'] = array(
    array('label' => 'Date', 'type' => 'string'),
    array('label' => 'phone 4', 'type' => 'number')
);
$data['rows'] = array();

while ($row = mysqli_fetch_array($result)) {
  if ($row["int_value"] == 0 && $row["real_value"] == 0.0 && $row["bool_value"] != "") {

      $row = array();
      $row[] = array('v' => (string) $row['datetime']);
      $row[] = array('v' => (float) $row['bool_value']);
      $data['rows'][] = array('c' => $row);

  } elseif ($row["int_value"] == 0 && $row["real_value"] != 0 && $row["bool_value"] == "") {

      $row = array();
      $row[] = array('v' => (string) $row['datetime']);
      $row[] = array('v' => (float) $row['real_value']);
      $data['rows'][] = array('c' => $row);

  } elseif ($row["int_value"] != 0 && $row["real_value"] == 0 && $row["bool_value"] == "") {

      $row = array();
      $row[] = array('v' => (string) $row['datetime']);
      $row[] = array('v' => (float) $row['int_value']);
      $data['rows'][] = array('c' => $row);

  }
}

$data['rows'] = $rows;

echo json_encode($data);

---

JavaScript

google.charts.load('current', {
    packages: ['line']
}).then(function () {

    $.ajax({
        url: 'getdata.php',
        dataType: 'json'
    }).done(function (jsonData) {

        var data = new google.visualization.DataTable(jsonData);

        var options = {
            chart: {
                title: 'Values'
            },
            width: 900,
            height: 500
        };

        var chart = new google.charts.Line(document.getElementById('chart_div'));
        chart.draw(data, google.charts.Line.convertOptions(options));

    }).fail(function (jq, text, errMsg) {
      console.log(text + ': ' + errMsg);
    });

});

---

这会给你一个更好的结构来处理多个图表/数据表

现在，如果您想在图表中使用真实日期，而不是字符串(如上面的 php)
它将允许您为 x 轴使用图表选项，例如 format

您可以将日期值作为字符串传递，格式如下...
Dates and Times Using the Date String Representation

“日期(年、月、日、时、分、秒、毫秒)”

为此，将列类型更改为日期...

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

然后在加载数据时，使用以下格式如上...

      $rowDate = "Date(".date_format($row['datetime'], 'Y').", ".((int) date_format($row['datetime'], 'm') - 1).", ".date_format($row['datetime'], 'd').", ".date_format($row['datetime'], 'H').", ".date_format($row['datetime'], 'i').", ".date_format($row['datetime'], 's').")";

      $row = array();
      $row[] = array('v' => (string) $rowDate);
      $row[] = array('v' => (float) $row['bool_value']);
      $data['rows'][] = array('c' => $row);

注意:月份数字在 javascript 中是从零开始的...