So, following on from my recent post on updating my Currentcost code I now have every update coming from the Currentcost unit popping directly into the database, every 6 seconds.
I had become aware of the Highcharts charts javascript and when reading through their Live Charts Demo decided that this was just waiting to be played with.
I now have essentially the same graph that they have in that demo, and the page HTML is very similar. The difference is obviously to call my code instead, and also a change to refresh every 6 seconds:
function requestData() { $.ajax({ url: '/power/latest.php', success: function(point) { var series = chart.series[0], shift = series.data.length > 60; // shift if the series is longer than 20 // add the point chart.series[0].addPoint(eval(point), true, shift); // call it again after one second setTimeout(requestData, 6000); }, cache: false }); }
My AJAX call is also to some PHP, but mine looks up the latest data from the database with a simple SQL statement:
select unix_timestamp(time) as time,data_value from data order by time desc limit 1
I quickly realised that this would slap the database, and more so if more than one person was viewing the page. That would be unnecessary, and so I installed memcached and the php5 memcached module.
<?php header("Content-type: text/json"); $m = new Memcached(); $m->addServer('127.0.0.1', 11211); if(!($row = $m->get('leccy'))) { //echo "didn't get from memcache\n"; $row = get_from_db(); $m->set('leccy', $row, 6); } else if(time() - $row['time'] > 5) { //echo "got, but is old\n"; $row = get_from_db(); $m->set('leccy', $row, 6); } // x is JS time which is unixtime x 1000 $x = $row['time'] * 1000; // y is the value at that point $y = $row['data_value'] * 1; $ret = array($x, $y); echo json_encode($ret); function get_from_db() { mysql_connect("db server","db user","db passwd") or die("cannot connect: ".mysql_error()); mysql_select_db("db name"); $sql = 'select unix_timestamp(time) as time,data_value from data order by time desc limit 1'; $sth = mysql_query($sql) or die("no query: ".mysql_error()); $row = mysql_fetch_assoc($sth); mysql_close(); return $row; } ?>
Edit: Slight tweak to the code, as there was some obvious lazyness that I tidied up.
Enjoy.