Language Switcher
Charting the data - Highcharts
- Details
Data stored in a SQL database can be processed in many ways. I will describe a method of presenting them as charts available through a web browser
Presenting the data requires 2 elements:
- Preparing the web server to sending data in a proper format. The description of how to place the data at the SQL server and what the structure of the used database is are presented in the article: Sending data to a SQL database.
- Preparing a html page, which will send reqests to the server and present the received data as charts.
Step I - PHP script to be placed at the web server. Examle name: "sql.php"
<?php
// if you want to send request from a html file stored
// locally uncomment the line below
//header('Access-Control-Allow-Origin: *');
// in the commands "mysql_..." change the following:
// xxxx - your SQL server address (np: www.abc.com)
// yyyy - user name
// zzzz - password
// qqqq - database name
// watch out for the capital letters!
mysql_connect("xxxx", "yyyy", "zzzz") or die(mysql_error());
mysql_select_db("qqqq") or die(mysql_error());
// describe the date ragne - here: the last 3 days
$phpdate=time()- (3*24 * 60 * 60);
$datefrom = date( 'Y-m-d H:i:s', $phpdate );
$dateto = date( 'Y-m-d H:i:s', time() );
$data = mysql_query("SELECT * FROM Temperatures WHERE `Timestamp` > '$datefrom'
AND `Timestamp` < '$dateto' ORDER BY Timestamp ASC") or die(mysql_error());
$rowcount = mysql_num_rows($data);
$counter=1;
// prepare the answer needed by Highcharts:
echo '{
"title": {"text": "Temperatura zewnętrzna"},
"chart": {"renderTo": "container"},
"series": [{"name": "Zewnętrzna", "data":[';
// fill the data from the database,
// here - the data of interest is stored in column "Temp4":
while($r = mysql_fetch_assoc($data)) {
echo '['.strtotime($r["Timestamp"])*1000 . ', '.$r["Temp4"]/10 .']';
if($counter < $rowcount){
echo ", ";
}
$counter=$counter+1;
}
// finalize the answer
echo' ]}]}';
mysql_free_result($data);
?>
Step II. Preparing the html file, which will read and present the data
<!DOCTYPE HTML>
<html>
<head>
<html xmlns="http://www.w3c.org/1999/xhtml"; xml:lang="pl" lang="pl">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Wykres Temperatury</title>
<!-- pliki js znajdziesz na www.jquery.com i www.highcharts.com-->
<script type="text/javascript" src="/js/jquery.min.js"></script>
<script type="text/javascript" src="/js/highcharts.js"></script>
<script type="text/javascript" src="/js/grid.js"></script>
<script type="text/javascript">
var options;
$(document).ready(function() {
options = {
//there are vary many....
//for details go to www.highcharts.com
//or check out the source of the example linked at the bottom
};
$.getJSON("http://www.twoj_serwer.pl/sql.php", function(json) {
var options1 = $.extend(json, options);
chart = new Highcharts.Chart(options1);
});
});
</script>
</head>
<body>
<div id="container" style="width: 800px; height: 400px; margin: 0 auto"></div>
</body>
</html>
An example of an extended list of used parameters is availabe at http://www.edom-plc.pl/wago/temperatury.html