We draw interactive graphics using Flot php and mysql
Working for a long time with different monitoring systems, I was very fond of building all kinds of graphics. For some time rrdtool rescued me, but I always wanted to get more interactivity, for the sake of which I even screwed a web face on php to it. But once stumbled upon graphics from flot could not pass by. What I have long dreamed about for a long time - zooming of graphs without delays on updating the page, tooltips - everything was there.
Immediately I will warn you that my level in javascript is ~ 0, and about 0.5 in php, so the target group for my further story is more like admins, who just like me wandered on the internet looking for a complete solution flot + mysql, because I myself have not found this in the network.
So. Let's start with the download of the fleet with google . We drop it into the web server directory (or link it) and from Tracking curves with crosshair (with crosshair plugin) from index.html, you can choose absolutely any, but it will be used in the example.
Its already revised code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Flot Examples</title>
<link href="layout.css" rel="stylesheet" type="text/css"></link>
<!--[if IE]><script language="javascript" type="text/javascript" src="../excanvas.min.js"></script><![endif]-->
<script language="javascript" type="text/javascript" src="../jquery.js"></script>
<script language="javascript" type="text/javascript" src="../jquery.flot.js"></script>
<script language="javascript" type="text/javascript" src="../jquery.flot.crosshair.js"></script>
</head>
<body>
<h1>Flot crosshairs Example</h1>
<div id="placeholder" style="width:620px;height:225px"></div> //
<p>Love plots.</p>
<p id="hoverdata"></p>
<script id="source" language="javascript" type="text/javascript">
var plot;
$(function () {
get_data();
});
function get_data() {
$.ajax({ url: "getdata.php", // php , mysql
dataType: "json",
success: function(sin)
{
for (var i = 0; i < sin.length; ++i)
sin[i][0] = sin[i][0]*1000 + 3 * 1000 * 60 * 60; // , mysql unix_timestamp(time) flot + UTC+3
function weekendAreas(axes) {
var markings = [];
var d1 = new Date(axes.xaxis.min);
// go to the first Saturday
d1.setUTCDate(datasets.getUTCDate() - ((d1.getUTCDay() + 1) % 7))
d1.setUTCSeconds(0);
d1.setUTCMinutes(0);
d1.setUTCHours(0);
var i = d1.getTime();
do {
// when we don't set yaxis, the rectangle automatically
// extends to infinity upwards and downwards
markings.push({ xaxis: { from: i, to: i + 2 * 24 * 60 * 60 * 1000 } });
i += 7 * 24 * 60 * 60 * 1000;
} while (i < axes.xaxis.max);
return markings;
}
plot = $.plot($("#placeholder"),
[ { data: sin, label: "hz = 00000"} ], {
series: {
lines: { show: true }
},
crosshair: { mode: "x" },
grid: { hoverable: true, autoHighlight: false },
yaxis: { min: 0 },
xaxis: { mode: "time" }
});
var i = 0;
(function () {
i = i + 1;
$.ajax({ url: "getlastdata.php", // php , mysql
dataType: "json",
success: function(ans)
{
ans[0][0] = ans[0][0]*1000 + 3 * 1000 * 60 * 60; // -
sin.push([ans[0][0],ans[0][1]]);
plot = $.plot($("#placeholder"),
[ { data: sin, label: "hz=0" } ], {
series: {
lines: { show: true }
},
crosshair: { mode: "x" },
grid: { hoverable: true, autoHighlight: false },
yaxis: { min: 0 },
xaxis: { mode: "time" }
});
var legends = $("#placeholder .legendLabel");
legends.each(function () {
$(this).css('width', $(this).width() + 30);
});
var updateLegendTimeout = null;
var latestPosition = null;
function updateLegend() {
updateLegendTimeout = null;
var pos = latestPosition;
var axes = plot.getAxes();
if (pos.x < axes.xaxis.min || pos.x > axes.xaxis.max ||
pos.y < axes.yaxis.min || pos.y > axes.yaxis.max)
return;
var i, j, dataset = plot.getData();
for (i = 0; i < dataset.length; ++i) {
var series = dataset[i];
// find the nearest points, x-wise
for (j = 0; j < series.data.length; ++j)
if (series.data[j][0] > pos.x)
break;
legends.eq(i).text(series.label.replace(/=.*/, "= " + series.data[j][1])); // , , .
}
}
$("#placeholder").bind("plothover", function (event, pos, item) {
latestPosition = pos;
if (!updateLegendTimeout)
updateLegendTimeout = setTimeout(updateLegend, 50);
});
}
});
setTimeout(arguments.callee, 60000) //
})();
}
});
}
</script>
</body>
</html>
///////////////////////////////////////////////// getdata.php
<?
$hostname = "localhost";
$username = "user";
$password = "pass";
$dbName = "mon";
$time="2010-01-22 23:40:00";
/* MySQL, */
$table = "hztest";
/* */
mysql_connect($hostname,$username,$password) OR DIE(" ");
/* . - */
mysql_select_db($dbName) or die(mysql_error());
$dataset1 = array();
/* */
$query = "SELECT UNIX_TIMESTAMP(time),hztest FROM $table WHERE time > '$time'";
/* . - . */
$res = mysql_query($query) or die(mysql_error());
while ($row=mysql_fetch_array($res)) {
$dataset1[] = array( $row['UNIX_TIMESTAMP(time)'], $row['hztest'] );
}
echo json_encode($dataset1);
/* */
mysql_close();
?>
Accordingly, getlastdata.php differs only
line $ query
$ query = "SELECT UNIX_TIMESTAMP (time), hztest FROM $ table ORDER BY time DESC LIMIT 0,1";
The main advantage, despite the fact that most likely there is a lot to be optimized here - the whole structure works. However, I wrote all this for those who want to try the possibilities of flot, without understanding the details of the interaction of php & javascript. In a nutshell, the initial loading of data from mysql takes place at a specified time, and then one minute is loaded one minute each time and the schedule is rebuilt to reflect it without refreshing the entire page. The timeout can be set not hard with the time recorded as SELECT NOW () - interval n hours, but to always have the same time interval on the graph, use sin.shift () each cycle, it deletes the first value in the array.
Threat For correct data transfer from php to javascript, php json is required (I tested in fedora, I had to install an additional eponymous package for its work)
ZYY thanks DmitryGushin for invite
')
Source: https://habr.com/ru/post/81829/
All Articles