Hit statistics from visiting website users stored in a database. Then what? How do I list these log entries in a very simple way. The only important thing for now, is when the visitor came by the site.

The database stores a timestamp and which page the user visited. This can mean a massive amount of data and I don’t know how this first attempt will perform. At the moment, there is a relative small amount of data, but in time this should build up and if the performance starts lacking, I will take a look at it again.

To output the data to the user I will be using jQuery and the flot graphic tool. Before starting drawing I how to figure out, how to group my visitors. There no point in plotting a bunch of ’1 hits’ – is will make no sence, since the graph never will go above 1 hit pr entry. The data should therefor be grouped and I’m going to use the GROUP BY function i MySQL.

Which interval should the data be grouped in? I don’t know – it probably depends on the data. In my case I will group it into 5 minuttes (300 seconds) interval and this in done here:

SELECT tstamp, count(tstamp) as hits FROM visitorlog GROUP BY FLOOR(tstamp/300)

This gives me an output like this:

1244825201 7
1244826384 8
1244826803 16
1244826904 3
1244827259 1
1244827506 10

And now all I have to do, is plot the numbers using flot. The output will be generated from PHP and will end up looking like this:

$(document).ready (function() {

var data = [
  [[1244825201*1000,0], [1244825201*1000,7]],
  [[1244826384*1000,0], [1244826384*1000,8]],
  [[1244826803*1000,0], [1244826803*1000,16]],
  [[1244826904*1000,0], [1244826904*1000,3]],
  [[1244827259*1000,0], [1244827259*1000,1]],
  [[1244827506*1000,0], [1244827506*1000,10]],
];
$.plot($("#chartdiv"), data);

});

Because I want a the graph to draw pins I will generate a line from point 0 to the number of hits in the given time period. The unix-timestamp is multiplied by 1000 to comply with the flot documentation.

This will generate a graph, which will be insert in the HTML ID chartdiv.

Result:

Remember – this is just a simple demo. With more data added (and better timestamps) it will be a lot better

Resources:
jQuery
flot
flot examples
flot API