折线图来自html / javascript / php中的json响应 [英] Line chart from json response in html / javascript / php
问题描述
我试图在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 } bb
$ p
$ b 到目前为止我已经达到了（不多）：
< script type =text / javascript >
google.charts.load（'current'，{'packages'：['line']}）;
google.charts.setOnLoadCallback（drawChart）;
函数drawChart（）{
var data = new google.visualization.DataTable（）;
data.addColumn（'number'，'Datetime'）;
data.addColumn（'number'，'Values'）;
var options = {
图表：{
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 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）;
$ b $ 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）;
解决方案首先，不要推荐混合php和javascript相同的文件
将html / javascript从php中分离出来
使用ajax从php获取数据对于谷歌图表，请使用此处找到的json格式...
构造函数的JavaScript Literal数据参数的格式参数
这将允许您直接从json创建谷歌数据表
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 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（）;
$ b $ 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）;
$ b $ 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）;
$ b} 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 （$'$'$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ .then（function（）{
$ .ajax（{
url：'getdata.php'，
dataType：'json'
}）。done函数（jsonData）{
var data = new google.visualization.DataTable（jsonData）;
var options = {
图：{
title ：'值'
}，
宽度：900，
高度：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中所示）
它允许您使用图表选项，例如 format ，对于x轴
，您可以通过日期值作为字符串，格式如下：
使用日期字符串表示法的日期和时间
日期（年，月，日，小时，分钟，秒，毫秒）
date ...
array（'label'=>'Date'，'type'=>'date'） ，
然后当加载数据时，使用以下如上格式化...
$ rowDate =Date（。date_format（$ row ['datetime']，'' （'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中为零...
I'm trying to create a single line chart of a json response(sql) in html/javascript. What im trying to create is a chart with only one line of the tag_name, in the case below: "phone 4". This line should represent all values and datetimes of all json strings.
JSON response:
[{"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"}]
What i've reached so far(not much):
<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>
How my php code looks like:
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);
first, don't recommend mixing php and javascript in the same file
separate the html / javascript from the php
use ajax to get the data from php to javascript
for google charts, use the json format found here...
Format of the Constructor's JavaScript Literal data Parameter
this will allow you to create the google data table, directly from the json
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);
});
});
this will give you a better structure to handle multiple charts / data tables
now, if you wanted to use a real date in the chart, instead of a string (as in the above php)
it would allow you to use chart options such as format, for the x-axis
you can pass the date value as a string, in the following format found here...
Dates and Times Using the Date String Representation
"Date(Year, Month, Day, Hours, Minutes, Seconds, Milliseconds)"
to do this, change the column type to date...
array('label' => 'Date', 'type' => 'date'),
then when loading the data, using the following to format as above...
$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);
note: month numbers are zero-based in javascript...
这篇关于折线图来自html / javascript / php中的json响应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
