I want to make a bar diagram on my website that shows what days of the week and times-of-day has the most activity/logins.
So i'm wondering if someone could give me some tips on how they would do this? How i should organize the database table(s), what data to gather, and how i can present the data recorded as a bargraph using PHP.
Probably the best way to do this is to store a datetime stamp for each visit or login. Then, you can use clever SQL queries to group the information however you want (days of week, times of day, or anything else.)
So, the database table would likely only need a primary key and the timestamp of the visit, though you could definitely store information about the user or the action as well.
For presentation, try either Google Charts (http://code.google.com/apis/chart/) or a javascript-based charting library (There are several out there, I've had good experience with Highcharts http://www.highcharts.com/)
ps: Since you flagged this post as MySQL, I'm assuming that's your database of choice. Here is a table creation statement you can use.
CREATE TABLE `visits` (
`id` int(11) unsigned NOT NULL auto_increment,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
If you must do it yourself and not use a pre-existing package. Start small. Simply create one table that logs each request, who made it, the time, the referer, whatever info you want.
Once you have the data logged, if you still don't want to use something like Google Charts you can try making your own simple PHP charts with css and html.
Bar diagrams can be pretty easy, but require some math to get right. The easiest way is to flip them on their side. You normally see them like buildings, but you want in this case to see them as worm-like tubes. The length of the tube can then represent the number of hits, pageviews, visits, so on.
I would define a fixed width for the whole chart and change the width of the bars to represent a percentage of the max value. So if the max value is 1000, and this bar is 500, you would make the bar 50% of the width of your chart.
I hope this points you in the right direction. Doing something like this can be great for learning new concepts.
Might be easier to do a visualisation using your server logs. Processing ( http://www.processing.org/ ) is good at things like that, or try http://processingjs.org/ for a non Java version.
To address your question though, as has been mentioned, store the data time for each visit (and maybe IP as well if you're after unique visits) then write a script to pull the records and do a total for each hour. You could use coloured divs, with the height set to the number (or a fraction) of the hits. Actually this is basically a hit counter isn't it.
Yeah, my advice would be to check out processing though, it's easy to pick up and produces nice looking results.