SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    displaying members that has log in on my site

    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:

    PHP Code:
    CREATE TABLE IF NOT EXISTS `whoisonline` (
      `
    recordidint(11NOT NULL AUTO_INCREMENT,
      `
    memberidint(11NOT NULL,
      `
    lastlogintimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `
    loggedfromipvarchar(255NOT NULL,
      
    PRIMARY KEY (`recordid`),
      
    KEY `memberidwho` (`memberid`)
    ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=
    On my validateMembers page (this is the page that verify that member can login) after the validation I have the following code:

    PHP 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

  2. #2
    SitePoint Zealot Amit Yaron's Avatar
    Join Date
    Jan 2011
    Location
    Hod Haharon, Israel
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
     $query1 = "INSERT INTO whoisonline VALUES (NULL,'$memberid', FROM_UNIXTIME('$lastlogin'), '$loggedfromip')";
    Read more about DATE, TIME and TIMESTAMP here.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Zealot Amit Yaron's Avatar
    Join Date
    Jan 2011
    Location
    Hod Haharon, Israel
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by zoreli View Post
    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:
    Code:
    SELECT *
    FROM `whoisonline`
    WHERE
      TIMESTAMPDIFF(MINUTE, lastlogin, CURRENT_TIMESTAMP) <= 15
    For more precision, you can use:
    Code:
    ...
    TIMESTAMPDIFF(SECOND, lastlogin, CURRENT_TIMESTAMP) <= 900

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •