displaying members that has log in on my site

[zoreli]

Hi everyone

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:

$username=mysql_real_escape_string($_POST['username']);
    $password=mysql_real_escape_string($_POST['password']);
    $loggedfromip=mysql_real_escape_string($_POST['loggedfromip']);
    $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,
Zoreli

[Amit Yaron]

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.

[zoreli]

Hi Amit

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.

Regards, Zoreli

[Amit Yaron]

Hi Amit

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.

Regards, Zoreli

USING THE function TIMESTAMPDIFF

as follows:

SELECT *
FROM `whoisonline`
WHERE
  TIMESTAMPDIFF(MINUTE, lastlogin, CURRENT_TIMESTAMP) <= 15

For more precision, you can use:

...
TIMESTAMPDIFF(SECOND, lastlogin, CURRENT_TIMESTAMP) <= 900

[zoreli]

Hi Amit

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.

Regards, Zoreli

[zoreli]

Hi everyone

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?