Hi guys.
This is a bit of a long request, so please bear with me. I'll try and explain it as much as I can...
A new utility I need to have is something that records how long staff (who are the only people who can log into my website at the moment) spend on the website each time they visit, and add it to a weekly total
On a Sunday night (00:00 into Monday) those statistics should be reset, and a new week started, so it shows...
This week: x hours, x minutes
Last week: x hours, x minutes
2 weeks ago: x hours, x minutes
3 weeks ago: x hours, x minutes
Total: x hours, x minutes (of all time)
It should log this into a mySQL table, and keep it for every week (but just display it as above).
Accounts can be inserted into the new table when someone is added (I can hack it manually to add people already with us), so getting the accounts in there is no problem
As for the rest, I'm not really sure how to go about this
Our "login.php" is the script that sets all session information when the login is successful, I guess could start the logging.
We also have a logout.php to destroy the session, so this could stop the logging.
We also have a "users_online" table, consisting of:
[code]
-- Table structure for table users_online
CREATE TABLE users_online (
id int(11) NOT NULL auto_increment,
account varchar(50) NOT NULL default 'unknown',
ip varchar(15) NOT NULL default '',
time varchar(11) NOT NULL default '',
location varchar(200) NOT NULL default '',
ua varchar(200) NOT NULL default '',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=114206 DEFAULT CHARSET=latin1 AUTO_INCREMENT=114206 ;
[/code]
this is maintained via:
$ip1 = $_SERVER['REMOTE_ADDR'];
$time = time();
$ip_check = 'SELECT ip FROM `users_online` WHERE ip = \''.$ip1.'\'';
$ip_query = mysql_query($ip_check);
$num_rows = mysql_num_rows($ip_query);
// If no rows, meaning no ip's in db matched theirs, we will add them.
if(!$num_rows) {
if (isset($_SESSION['logged'])) {
$insert_new = mysql_query("INSERT INTO `users_online` (account, ip, time, location, ua) VALUES ('".mysql_real_escape_string($_SESSION['account'])."', '".mysql_real_escape_string($ip1)."', '".mysql_real_escape_string($time)."', '".mysql_real_escape_string($_SERVER['REQUEST_URI'])."', '".mysql_real_escape_string($_SERVER['HTTP_USER_AGENT'])."')");
} else {
$insert_new = mysql_query("INSERT INTO `users_online` (account, ip, time, location, ua) VALUES ('unknown', '".mysql_real_escape_string($ip1)."', '".mysql_real_escape_string($time)."', '".mysql_real_escape_string($_SERVER['REQUEST_URI'])."', '".mysql_real_escape_string($_SERVER['HTTP_USER_AGENT'])."')");
}
}
// This means that they're already in there, so we update info.
if($num_rows > 0) {
if (isset($_SESSION['logged'])) {
$update = mysql_query("UPDATE `users_online` SET time='".mysql_real_escape_string($time)."', account='".mysql_real_escape_string($_SESSION['account'])."', location='".mysql_real_escape_string($_SERVER['REQUEST_URI'])."', ua='".mysql_real_escape_string($_SERVER['HTTP_USER_AGENT'])."' WHERE ip = '".mysql_real_escape_string($ip1)."'");
} else {
$update = mysql_query("UPDATE `users_online` SET time='".mysql_real_escape_string($time)."', account='unknown', location='".mysql_real_escape_string($_SERVER['REQUEST_URI'])."', ua='".mysql_real_escape_string($_SERVER['HTTP_USER_AGENT'])."' WHERE ip = '".mysql_real_escape_string($ip1)."'");
}
if(!$update) die(mysql_error());
}
// If time now - start time > 600 then it has been 10 minutes, so we delete them
$delete_old = mysql_query("DELETE FROM `users_online` WHERE ((".time()."-time) > 600)");
if(!$delete_old) die(mysql_error());
so if they're inactive for 10 minutes, it could stop logging their time too.
Any ideas how to do the above? I'm hoping it all made sense!
The new mySQL table should be named statistics by the way 🙂
THANK YOU FOR ANY HELP YOU CAN PROVIDE WITH THIS, it's greatly appreciated :queasy:
