多行Google Line Chart，使用mySQL db中的数据 [英] Multi line Google Line Chart, using data from mySQL db
本文介绍了多行Google Line Chart，使用mySQL db中的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在经历一些学习过程，为我的公司创建一个小型的数据库支持的报告系统。
I'm going through a bit of a learning process, in order to create a small database backed reporting system for my company.
我们的目的是使用基于mysql数据库的Google Charts绘制多线图。
The intent is to draw a multi line chart using Google Charts, based on a mysql database.
I已经设法让数据从mysql数据库回显，但它不会生成图表。我所得到的只是回声，图表应该是空白处。回声显示用于调试目的。
I've managed to get the data to echo from the mysql database, but it's not generating the chart. All I get is the echo, and a blank space where the chart should be. The echo is shown for debugging purposes.
以下是代码：
Here is the code :
<?php include 'confile.php';
$qry = "SELECT time,p1,p2,p3,p4 from $db WHERE date = '2016-03-02' ORDER BY time ASC";
$result = $conn->query($qry);
if($result === FALSE) {
echo mysql_errno($result) .": ". mysql_error($result) ."/n";
die(mysql_error());
}
$rows = array();
$table = array();
$table['cols'] = array(
array('label' => 'Time', 'type' => 'datetime'),
array('label' => 'Probe 1', 'type' => 'number'),
array('label' => 'Probe 2', 'type' => 'number'),
array('label' => 'Probe 3', 'type' => 'number'),
array('label' => 'Probe 4', 'type' => 'number')
);
while($r = mysqli_fetch_assoc($result)) {
$temp = array();
$temp[] = array($r['time']);
$temp[] = array($r['p1']);
$temp[] = array($r['p2']);
$temp[] = array($r['p3']);
$temp[] = array($r['p4']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
?>
<html>
<head>
<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(<?$jsonTable?>);
var options = {
title: 'Recorded Temperatures',
legend: {position: 'bottom' },
width: 800,
height: 600
};
var chart = new google.visualization.Table(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="curve_chart" style="width: 900px; height: 500px"></div>
</body>
</html>
这就是'echo'输出
And this is the 'echo' output
{"cols":[{"label":"Time","type":"datetime"},{"label":"Probe 1","type":"number"},{"label":"Probe 2","type":"number"},{"label":"Probe 3","type":"number"},{"label":"Probe 4","type":"number"}],"rows":[{"c":[["03:02:07"],["270.26"],["298.40"],["111.54"],["228.06"]]},{"c":[["03:28:42"],["273.23"],["190.43"],["245.69"],["283.21"]]},{"c":[["07:26:04"],["144.33"],["217.26"],["206.53"],["167.68"]]},{"c":[["12:13:20"],["153.15"],["277.23"],["167.20"],["240.88"]]}]}
这是测试数据，使用db上的测试查询。一旦我理解了呈现图表的格式，它将被设置为允许用户选择要查看的日期等。
This is test data, using a test query on the db. Once I understand the formatting to render the chart, it will be setup to allow the user to select which date to view, etc.
这是我可以接近的现存问题发现，但似乎并没有回答这个问题。
This was the closest existing question I can find, but doesn't seem to answer the question.
Not able to generate a Google Chart using MySQL table data as the data source
在@MickMackusa的回答之后，我设法通过破解这个工作来确保mysql / php数组以Google Charts可以接受的方式输出。
Following the answer of @MickMackusa, I managed to hack this together to get it to work, by ensuring the mysql/php array was output in a manner acceptable to Google Charts.
感谢@MickMacUSA的帮助。
Thanks to @MickMacUSA for his assistance.
最后的工作代码如下。
<?php include 'confile.php';
$qry = "SELECT time,p1,p2,p3,p4 from $db WHERE date = '2016-04-16' ORDER BY time ASC";
$result = $conn->query($qry);
if($result === FALSE) {
echo mysqli_errno($result) .": ". mysqli_error($result) ."/n";
die(mysqli_error());
}
$i = 0; //iteration counter - start at 0
$totalRows = mysqli_num_rows($result); // we need this to know when to change the output
$targetRows = $totalRows - 1; //row indies start from 0, not 1.
foreach ($result as $row){
$comTime = str_replace(":",",",$row['time']); // for each row, remove the : and put , in its place
if ($targetRows == $i) { // if the index is the same value as the target (ie, it's the last row)...
$temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."]". PHP_EOL;
} else {
$temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."],". PHP_EOL;
}
$i = $i + 1;
$rows[] = $temp;
}
$table = $rows;
$data = implode($table); //format the table as a single string, with line returns
//echo $i;
//echo $data;
?>
<html>
<head>
<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>
</head>
<body>
<div id="chart" style="width: 900px; height: 500px"></div>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart(){
var data = new google.visualization.DataTable();
data.addColumn('timeofday','Time');
data.addColumn('number','Probe 1');
data.addColumn('number','Probe 2');
data.addColumn('number','Probe 3');
data.addColumn('number','Probe 4');
data.addRows([
<?php echo $data; ?> //dump the result into here, as it's correctly formatted
]);
var options = {
title: 'Recorded Temperatures',
legend: { position: 'bottom' },
width: 900,
height: 500,
hAxis: { format: 'hh:mm:ss' }
};
var chart = new google.visualization.LineChart(document.getElementById('chart'));
chart.draw(data, options);
}
</script>
</body>
</html>
推荐答案
您的号码值必须采用不同的格式， code> timeofday 不是 datetime 。
Your number values must be formatted differently and you want timeofday not datetime.
根据： https://developers.google.com/chart/interactive/docs/reference#dataparam
格式化您的数据如下所示：
Format your data to look like this:
{cols:
[
{"label":"Time","type":"timeofday"},
{"label":"Probe 1","type":"number"},
{"label":"Probe 2","type":"number"},
{"label":"Probe 3","type":"number"},
{"label":"Probe 4","type":"number"}
],
rows:
[
{c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
{c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
{c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
{c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
]
}
您必须在javascript中回显它：
And you must echo it in the javascript:
更改：
change:
<?$jsonTable?>
到：
<?php echo $jsonTable; ?>
将您的javascript代码块放在< / body> 标签。
And put your javascript code block just before your </body> tag.
这是使用我在服务器上测试的上述数据格式的完整工作代码：
This is the full working code using the above data format that I tested on my server:
<html>
<head>
<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>
</head>
<body>
<div id="curve_chart" style="width: 900px; height: 500px"></div>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart(){
var data = new google.visualization.DataTable(
{cols:[
{"label":"Time","type":"timeofday"},
{"label":"Probe 1","type":"number"},
{"label":"Probe 2","type":"number"},
{"label":"Probe 3","type":"number"},
{"label":"Probe 4","type":"number"}
],
rows:[
{c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
{c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
{c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
{c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
]
});
var options = {
title: 'Recorded Temperatures',
legend: { position: 'bottom' },
width: 900,
height: 500,
hAxis: { format: 'hh:mm:ss' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</body>
</html>
这是一种替代格式，可以更简单/更清晰/更容易地使用您的mysqli结果来构建/理解：
This is an alternative format that will be simpler/clearer/easier to build/comprehend using your mysqli results:
<html>
<head>
<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>
</head>
<body>
<div id="curve_chart" style="width: 900px; height: 500px"></div>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart(){
var data = new google.visualization.DataTable();
data.addColumn('timeofday','Time');
data.addColumn('number','Probe 1');
data.addColumn('number','Probe 2');
data.addColumn('number','Probe 3');
data.addColumn('number','Probe 4');
data.addRows([
[[03,02,07],270.26,298.40,111.54,228.06],
[[03,28,42],273.23,190.43,245.69,283.21],
[[07,26,04],144.33,217.26,206.53,167.68],
[[12,13,20],153.15,277.23,167.20,240.88]
]);
var options = {
title: 'Recorded Temperatures',
legend: { position: 'bottom' },
width: 900,
height: 500,
hAxis: { format: 'hh:mm:ss' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</body>
</html>
参见白帽：
google.charts.load('current', {
callback: drawChart,
packages: ['corechart', 'table']
});
function drawChart() {
var data = new google.visualization.DataTable({cols: [
{"label":"Time","type":"timeofday"},
{"label":"Probe 1","type":"number"},
{"label":"Probe 2","type":"number"},
{"label":"Probe 3","type":"number"},
{"label":"Probe 4","type":"number"}
],
rows: [
{c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
{c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
{c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
{c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
]
});
var table = new google.visualization.Table(document.getElementById('chart_0'));
table.draw(data);
var options = {
title: 'Recorded Temperatures',
legend: {position: 'bottom' },
width: 800,
height: 600,
hAxis: {
format: 'hh:mm:ss'
}
};
var chart = new google.visualization.LineChart(document.getElementById('chart_1'));
chart.draw(data, options);
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_0"></div>
<div id="chart_1"></div>
这篇关于多行Google Line Chart，使用mySQL db中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
