Highcharts从数据库中获取数据
I'm trying to graph some data from my database on a Highchart, but I can't seem to get the data to show.
I have the following PHP (snippet) that gets the data from the database and json_encodes it:
<?php
$result = mysqli_query($cxn,"SELECT * FROM counter");
while($row = mysqli_fetch_array($result)) {
$value = $row['value'];
$timestamp = strtotime($row['datetime']);
$data[] = "[$value, $timestamp]";
}
json_encode($data);
?>
The json_encode prints the following (I'm using datetime):
["[500, 1384122794]","[600, 1384153203]"]
I then have the following to graph the data:
<html>
<body>
<div id="container" style="height: 500px; min-width: 500px"></div>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script src="http://code.highcharts.com/stock/highstock.js"></script>
<script>
$(function() {
$.getJSON('http://www.website.com/graph.php', function(data) { // I put website.com on purpose
// Create the chart
$('#container').highcharts('StockChart', {
rangeSelector : {
selected : 1
},
title : {
text : 'Title'
},
series : [{
name : 'AAPL',
data : data,
tooltip: {
valueDecimals: 2
}
}]
});
});
});
</script>
</body>
</html>
The problem is that the graph doesn't actually show the points, it's just a blank graph.
What am I doing wrong?
To answer my own question, I changed the while loop to:
while($row = mysqli_fetch_array($result)) {
$value = $row['value'];
$timestamp = strtotime($row['datetime']) * 1000;
$data[] = [$timestamp, (int)$value];
}
you want to assign an array of "data" to the single column data which is wrong
see here :
$(function () {
$('#container').highcharts({
title: {
text: 'Monthly Average Temperature',
x: -20 //center
},
subtitle: {
text: 'Source: WorldClimate.com',
x: -20
},
xAxis: {
categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
},
yAxis: {
title: {
text: 'Temperature (°C)'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
valueSuffix: '°C'
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle',
borderWidth: 0
},
series: [{
name: 'Tokyo',
data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6]
}, {
name: 'New York',
data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
}, {
name: 'Berlin',
data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
}, {
name: 'London',
data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8]
}]
});
});
one solution is to use a for loop and assign data and name in each loop
The problem is that you return string, you need to return data as numbers. I recommend to familar with json_encode() flags, like JSON_NUMERIC_CHECK.
Secondly $data[] = "[$value, $timestamp]"; line needs to be array not string with printed bracket.
Step-1 Create Database [highchartdemo]
Step-2 Create Two tables [demo_viewer, demo_click]
CREATE TABLE IF NOT EXISTS `demo_viewer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`numberofview` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
CREATE TABLE IF NOT EXISTS `demo_click` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`numberofclick` int(12) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
demo_viewer table:
demo_click table:
Step3- Create Database Configuration File config.php
$dbHost = "localhost";
$dbDatabase = "highchartdemo";
$dbPasswrod = "root";
$dbUser = "root";
$mysqli = mysqli_connect($dbHost, $dbUser, $dbPasswrod, $dbDatabase);
?>
Step-4 Create Index.php file index.php
<?php
require('config.php');
/* Getting demo_viewer table data */
$sql = "SELECT SUM(numberofview) as count FROM demo_viewer
GROUP BY YEAR(created_at) ORDER BY created_at";
$viewer = mysqli_query($mysqli,$sql);
$viewer = mysqli_fetch_all($viewer,MYSQLI_ASSOC);
$viewer = json_encode(array_column($viewer, 'count'),JSON_NUMERIC_CHECK);
/* Getting demo_click table data */
$sql = "SELECT SUM(numberofclick) as count FROM demo_click
GROUP BY YEAR(created_at) ORDER BY created_at";
$click = mysqli_query($mysqli,$sql);
$click = mysqli_fetch_all($click,MYSQLI_ASSOC);
$click = json_encode(array_column($click, 'count'),JSON_NUMERIC_CHECK);
?>
<!DOCTYPE html>
<html>
<head>
<title>Highcharts get data from database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>
</head>
<body>
<script type="text/javascript">
$(function () {
var data_click = <?php echo $click; ?>;
var data_viewer = <?php echo $viewer; ?>;
$('#container').highcharts({
chart: {
type: 'column'
},
title: {
text: 'Yearly Website Ratio'
},
xAxis: {
categories: ['2013','2014','2015', '2016']
},
yAxis: {
title: {
text: 'Rate'
}
},
series: [{
name: 'Click',
data: data_click
}, {
name: 'View',
data: data_viewer
}]
});
});
</script>
<div class="container">
<br/>
<h2 class="text-center">Highcharts get data from database</h2>
<div class="row">
<div class="col-md-10 col-md-offset-1">
<div class="panel panel-default">
<div class="panel-heading">Graphical View</div>
<div class="panel-body">
<div id="container"></div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>