I have a website on which registered members can log in. I would like to be able to display the members that logged in on my website in last 15 minutes.
Many sites have this feature especially forums.
For that purpose, I have created the following table in my database:
CREATE TABLE IF NOT EXISTS `whoisonline` (
`recordid` int(11) NOT NULL AUTO_INCREMENT,
`memberid` int(11) NOT NULL,
`lastlogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`loggedfromip` varchar(255) NOT NULL,
PRIMARY KEY (`recordid`),
KEY `memberidwho` (`memberid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
On my validateMembers page (this is the page that verify that member can login) after the validation I have the following code:
$lastlogin = time();
$query1 = "INSERT INTO whoisonline VALUES (NULL,'$memberid', '$lastlogin', '$loggedfromip')";
At this time, insert works, but in php my admin, the inserted record display "0000-00-00 00:00:00" in the field lastlogin. But that is not my only problem. I am wondering how I will make the select after, to check which members are logged in in the last 15 minutes.
Can anyone help me to solve this?
Thanks in advance,
The value in the lastlogin field is a string of zeros because you've inserted an illegal date, you should have converted it using the function FROM_UNIXTIME as follows:
$query1 = "INSERT INTO whoisonline VALUES (NULL,'$memberid', FROM_UNIXTIME('$lastlogin'), '$loggedfromip')";
Read more about DATE, TIME and TIMESTAMP here.
Thanks for your solution, the insert now works like a charm. I will read the content of the page from the link that you post to learn how & why it works now.
Do you or anyone else know how can I select the members that logged in in last 15 minutes from the table whoisonline?
How the query should look like? Should I use between in the query? Between now and??? How can I subtracts time from the time stamp? Any idea?
Any help will be deeply appreciated.
USING THE function TIMESTAMPDIFF
TIMESTAMPDIFF(MINUTE, lastlogin, CURRENT_TIMESTAMP) <= 15
For more precision, you can use:
TIMESTAMPDIFF(SECOND, lastlogin, CURRENT_TIMESTAMP) <= 900
Well, this is amazing. Your knowledge and willing to share it are really amazing. My hat is off ... Really..
Thanks a lot. I really appreciate it.
Sorry for bumping this thread, but I have problem with the insert query again.
While I was working on my localhost, everything was great. After I upload the site , insert query is inserting in mySql server time, no matter what I am doing.
How can I insert local time Europe/London, instead of server time?