Google图表值来自不同的时间 [英] Google chart values from different times
本文介绍了Google图表值来自不同的时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
使用谷歌折线图。存在两个值（T_Temperatur）和（T_Badende_per_Time）通过一次提交同时插入数据库的问题。但不是T_Lufttemperatur。这是造成图表的问题，也显示了空值。所以这是我的sql：
这是谷歌图表：
代码：
<？php
$ con = mysql_connect（localhost，root，）或die（无法连接数据库!!!! ）;
mysql_select_db（nih_bw，$ con）;
$ sth = mysql_query（
SELECT routines.date，routines.time，
SUM（IF（measurements.title ='T_Temperatur'，CAST（REPLACE（routines.value ，''，'。'）AS DECIMAL（18,2）），0））AS温度，
SUM（IF（measurements.title ='T_Badende_per_Time'，CAST（REPLACE（routines。 AS DECIMAL（18,2）），0））AS Badende，
SUM（IF（measurements.title ='T_Luft_Temperatur'，CAST（REPLACE（routines） 。）AS AS DECIMAL（18，2）），0））AS Luft
FROM例程
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN测量ON measure_routine.measure_id = measurements.id
GROUP BY routines.date，routines.time
ORDER BY routines.date，routines.time;
;）;
$ rows = array（）;
//不需要
$ flag = true;
$ table = array（）;
$ table ['cols'] = array（
array（'label'=&'; routines.date'&'routines.time'，'type' =>'datetime'），
array（'label'=>'Temperatur'，'type'=>'number'），
array（'label'=>'Badende' ，'type'=>'number'），
array（'label'=>'Luft'，'type'=>'number'），
）;
$ rows = array（）;
while（$ r = mysql_fetch_assoc（$ sth））{
$ temp = array（）;
//假定日期格式为yyyy-MM-dd
$ dateString = $ r ['date'];
$ dateArray = explode（' - '，$ dateString）;
$ year = $ dateArray [0];
$ month = $ dateArray [1] - 1; //减1以转换为javascript的0索引月份
$ day = $ dateArray [2];
//假定时间格式为hh：mm：ss
$ timeString = $ r ['time'];
$ timeArray = explode（'：'，$ timeString）;
$ hours = $ timeArray [0];
$分钟= $ timeArray [1];
$秒= $ timeArray [2];
$ temp = array（）;
$ temp [] = array（'v'=>Date（$ year，$ month，$ day，$ hours，$ minutes，$ seconds））;
$ temp [] = array（'v'=>（string）$ r ['Temperatur']）;
$ temp [] = array（'v'=>（string）$ r ['Badende']）;
$ temp [] = array（'v'=>（string）$ r ['Luft']）;
$ rows [] = array（'c'=> $ temp）;
}
$表['rows'] = $ rows;
$ jsonTable = json_encode（$ table）;
// echo $ jsonTable;
？>
< html>
< head>
< script type =text / javascriptsrc =https://www.google.com/jsapi>< / script>
< script type =text / javascript>
google.load（visualization，1，{packages：[corechart]}）;
google.setOnLoadCallback（drawChart）;
$ b函数drawChart（）{
var data = new google.visualization.DataTable（<？= $ jsonTable？>）;
var options = {
/ * width：900，height：900，* /
title： '可视化'，
curveType：'function'，
legend：{position：'bottom'}，
pointSize：5，
vAxis：{title：Values，titleTextStyle ：{italic：false}}，
hAxis：{title：Time，titleTextStyle：{italic：false}}，
explorer：{'bbToZoom'，'rightClickToReset ']，
轴：'vertical'}
};
var chart = new google.visualization.LineChart（document.getElementById（'chart_div'））;
chart.draw（data，options）;
}
< / script>
< / head>
< body>
< div id =chart_divstyle =width：900px; height：500px;>< / div>
< / body>
< / html>
{cols：[{label：routines.dade，type：datetime}，{label：Temperatur，type：number}，
{label：Badende，type：number}，{label：Luft，type：number}]，rows：[{c：
[{v：日期（2014,3,02,09,04,12）}，{v：29.23}，{v：55.00}，{ （2014，3，02，10，04，01）}，{v：23.34 }，{v：34.00}，{v：}]}，{c：
[{v：Date（2014，3，02，10，04 ，39）}，{v：43.54}，{v：39.00}，{v：}]}，{c：
[{v ：日期（2014,3,02,10,04,53）}，{v：}，{v：}，{v：23.23}]}] }
请随时询问您是否需要提供更多信息。
> SELECT routines.date，routines.time，
SUM（IF（measurements.title ='T_Temperatur'，CAST（REPLACE（routines.value，'，'，'。'）AS DECIMAL（18，2）） ， 空值 ） ） AS温度，
SUM（IF（measurements.title ='T_Badende_per_Time'，CAST（REPLACE（routines.value，'，'，'。'）AS DECIMAL（18,2）），null））AS Badende，
SUM（IF（measurements.title ='T_Luft_Temperatur'，CAST（REPLACE（routines.value，'，'，'。）AS DECIMAL（18，2）），null））AS Luft
FROM例程
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN测量值ON measure_routine.measure_id = measurements.id
GROUP BY routines.date，routines.time
ORDER BY routines.date，routines.time;
输入数据时，不要将它们转换为字符串：
$ temp [] = array（'v'=> $ r ['Temperatur']）;
$ temp [] = array（'v'=> $ r ['Badende']）;
$ temp [] = array（'v'=> $ r ['Luft']）;
并使用 JSON_NUMERIC_CHECK code> json_encode 调用：
$ jsonTable = json_encode（$ table，JSON_NUMERIC_CHECK）;
Working on a google line chart. Having the problem that two of the values (T_Temperatur) And (T_Badende_per_Time) is inserted into the database at the same time with one submit. But not T_Lufttemperatur. That is creating problem with the chart, which is also showing the nulls. So this is my sql:
This is the google chart:
Code:
<?php
$con=mysql_connect("localhost","root","") or die("Failed to connect with database!!!!");
mysql_select_db("nih_bw", $con);
$sth = mysql_query("
SELECT routines.date, routines.time,
SUM( IF( measurements.title = 'T_Temperatur', CAST( REPLACE( routines.value, ',', '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS Temperatur,
SUM( IF( measurements.title = 'T_Badende_per_Time', CAST( REPLACE( routines.value, ',', '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS Badende,
SUM( IF( measurements.title = 'T_Luft_Temperatur', CAST( REPLACE( routines.value, ',', '.' ) AS DECIMAL( 18, 2 ) ), 0 ) ) AS Luft
FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
GROUP BY routines.date, routines.time
ORDER BY routines.date, routines.time;
;");
$rows = array();
//flag is not needed
$flag = true;
$table = array();
$table['cols'] = array(
array('label' => 'routines.date' & 'routines.time', 'type' => 'datetime'),
array('label' => 'Temperatur', 'type' => 'number'),
array('label' => 'Badende', 'type' => 'number'),
array('label' => 'Luft', 'type' => 'number'),
);
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$temp = array();
// assumes dates are in the format "yyyy-MM-dd"
$dateString = $r['date'];
$dateArray = explode('-', $dateString);
$year = $dateArray[0];
$month = $dateArray[1] - 1; // subtract 1 to convert to javascript's 0-indexed months
$day = $dateArray[2];
// assumes time is in the format "hh:mm:ss"
$timeString = $r['time'];
$timeArray = explode(':', $timeString);
$hours = $timeArray[0];
$minutes = $timeArray[1];
$seconds = $timeArray[2];
$temp = array();
$temp[] = array('v' => "Date($year, $month, $day, $hours, $minutes, $seconds)");
$temp[] = array('v' => (string) $r['Temperatur']);
$temp[] = array('v' => (string) $r['Badende']);
$temp[] = array('v' => (string) $r['Luft']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
// echo $jsonTable;
?>
<html>
<head>
<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 = new google.visualization.DataTable(<?=$jsonTable?>);
var options = {
/*width: 900, height: 900, */
title: 'Visualization',
curveType: 'function',
legend: { position: 'bottom' },
pointSize: 5,
vAxis: {title: "Values", titleTextStyle: {italic: false}},
hAxis: {title: "Time", titleTextStyle: {italic: false}},
explorer: {
actions: ['dragToZoom', 'rightClickToReset'],
axis: 'vertical'}
};
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="chart_div" style="width: 900px; height: 500px;"></div>
</body>
</html>
jsontable:
{"cols":[{"label":"routines.dade","type":"datetime"},{"label":"Temperatur","type":"number"},
{"label":"Badende","type":"number"},{"label":"Luft","type":"number"}],"rows":[{"c":
[{"v":"Date(2014, 3, 02, 09, 04, 12)"},{"v":"29.23"},{"v":"55.00"},{"v":""}]},{"c":
[{"v":"Date(2014, 3, 02, 10, 04, 01)"},{"v":"23.34"},{"v":"34.00"},{"v":""}]},{"c":
[{"v":"Date(2014, 3, 02, 10, 04, 39)"},{"v":"43.54"},{"v":"39.00"},{"v":""}]},{"c":
[{"v":"Date(2014, 3, 02, 10, 04, 53)"},{"v":""},{"v":""},{"v":"23.23"}]}]}
Please feel free to ask if you need more info provided.
解决方案
Try replacing the 0's in the query with nulls:
SELECT routines.date, routines.time,
SUM( IF( measurements.title = 'T_Temperatur', CAST( REPLACE( routines.value, ',', '.' ) AS DECIMAL( 18, 2 ) ), null ) ) AS Temperatur,
SUM( IF( measurements.title = 'T_Badende_per_Time', CAST( REPLACE( routines.value, ',', '.' ) AS DECIMAL( 18, 2 ) ), null ) ) AS Badende,
SUM( IF( measurements.title = 'T_Luft_Temperatur', CAST( REPLACE( routines.value, ',', '.' ) AS DECIMAL( 18, 2 ) ), null ) ) AS Luft
FROM routines
INNER JOIN measure_routine ON routines.id = measure_routine.routine_id
INNER JOIN measurements ON measure_routine.measure_id = measurements.id
GROUP BY routines.date, routines.time
ORDER BY routines.date, routines.time;
[EDIT - added PHP code to avoid casting the numbers as strings]
When inputting your data, don't cast them as strings:
$temp[] = array('v' => $r['Temperatur']);
$temp[] = array('v' => $r['Badende']);
$temp[] = array('v' => $r['Luft']);
and use the JSON_NUMERIC_CHECK option in the json_encode call:
$jsonTable = json_encode($table, JSON_NUMERIC_CHECK);
这篇关于Google图表值来自不同的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
