Lastactive users up to 15 minutes

[Russia]

I have a database with Users as a table. It has the normal stuff, id, username, password, and I have anther in it called lastactive. It is int(11) , and I am wondering how I would make a sql query select the users in the database 'users' and see who has been active in the past 15 mins.

 

By the way, the lastactive time gets updated by the function time()

 

Here is my code:

 

<?php
$online = mysql_query("SELECT * from users WHERE (TIMESTAMPDIFF(MINUTE, `lastactive`, NOW()) < 15) ORDER by lastactive DESC") or die (mysql_error());
while($online = mysql_fetch_assoc($online)) {
echo '<a style="color:#F0CD87;" href="profile?id='.$online['user_id'].'">';
echo ucFirst($online['username']);
echo '</a>, ';
}
?>

 

Here is how the last active gets updated.

<?php
if(isset($_SESSION['logged'])) {
mysql_query("UPDATE `users` SET `lastactive`='" . time() . "' WHERE `username`='" . $_SESSION['username'] . "'");
}
?>

 

Again its not showing the users that have been on since 15 minutes ago, even tho it updates the users last active on every page since its in the footer.php part. which is on every page.

 

Thanks for the upcoming help.

[nethnet]

Try something like this in your query:

 

SELECT * FROM `users` WHERE `lastactive` >= '$fifteenminsago' ORDER BY `lastactive` DESC

 

You would define your timer variables as such:

 

$fifteenminsago = time() - (15 * 60);

 

Hope that helps.

[gizmola]

Why don't you just use a column of type timestamp?

[gizmola]

This might help you as well:  http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

[Russia]

Thanks it works, but for some reason it gives this error under the usernames.

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Program Files\EasyPHP5.2.10\www\yanille\includes\footer.php on line 34

[Russia]

Giz, the reason is, because I have about 100 members in my database, and if I change the column type it will default it to I think 0000000 which is very annoying.

[nethnet]

It works AND gives you a MySQL error?

 

Usually when I run into that error, it is because I have prematurely called mysql_fetch_assoc before confirming that there are indeed rows returned by the query (if the query returns 0 rows, it will throw that error).

 

Usually I would confirm that there is at least 1 record returned using mysql_num_rows before I call mysql_fetch_assoc, but if your script is working and returning records from the database, I'm not sure why/how there would be a MySQL error.

[Russia]

Thats the thing, I have no idea either, how would I add an if statement for checking if any rows are found.

[Russia]

i did:

 

$num_rows = mysql_num_rows($online);
echo "$num_rows Rows\n";

 

It returned 1 row. which is correct, because I am logged in the site as an admin.

[nethnet]

<?php
$fifteenminsago = time() - (15 * 60);
$sql = "SELECT * FROM `users` WHERE `lastactive` >= '$fifteenminsago' ORDER BY `lastactive` DESC";
$query = mysql_query($sql) or die (mysql_error());

if (mysql_num_rows($query) > 0) {
     while($online = mysql_fetch_assoc($query)) {
          echo '<a style="color:#F0CD87;" href="profile?id='.$online['user_id'].'">';
          echo ucFirst($online['username']);
          echo '</a>, ';
     }
} else {
     // No records found
}

?>

[Russia]

Thanks alot by the way.

 

But the thing is, when I log out, it still says im an active user,  guess because there is a 15 minute limit.

 

How do I edit it so It checks by every minute instead.

[nethnet]

Just change the $fifteenminsago variable to:

 

$fifteenminsago = time() - (1 * 60);

 

All you have to do is change the integer '1' to however many minutes you want (it was at 15 before).

[Russia]

Okay great and a last thing that has nothing to do with it, how would I do it so the last user in the while doesnt have a ,(comma) after its name Is that possible? Or do I have to add a bunch more code to it so it doesnt make it show for the last one?

[nethnet]

A better way to make users who log off not show up anymore would be to create a boolean `loggedin` field in your users database.  Whenever a user logs in, set this value to TRUE.  When a user logs out, change it to FALSE.

 

Then, in your online users list, change the SQL to exclude any user who has a FALSE `loggedin` flag.

 

SELECT * FROM `users` WHERE `lastactive` >= '$fifteenminsago' AND `loggedin` = TRUE ORDER BY `lastactive` DESC

[gizmola]

Giz, the reason is, because I have about 100 members in my database, and if I change the column type it will default it to I think 0000000 which is very annoying.

 

Yes conversion is a detail that requires you to add a temporary column and then update the column using FROM_UNIXTIME(). 

[nethnet]

It wouldn't be very difficult to do that, try something like this (I added the `loggedin` field to the script that I mentioned in my last post.. just remove that part from the SQL syntax if you don't want to do that):

 

<?php
$fifteenminsago = time() - (5 * 60);
$sql = "SELECT * FROM `users` WHERE `lastactive` >= '$fifteenminsago' AND `loggedin` = TRUE ORDER BY `lastactive` DESC";
$query = mysql_query($sql) or die (mysql_error());
$rows = mysql_num_rows($query);

if ($rows > 0) {
     $i = 1;
     while($online = mysql_fetch_assoc($query)) {
          echo '<a style="color:#F0CD87;" href="profile?id='.$online['user_id'].'">';
          echo ucFirst($online['username']);
          echo '</a>';
          if ($i < $rows) {
               echo ', ';
          }
          $i++;
     }
} else {
     // No records found
}

?>

 

I also made the timer set to 5 minutes.  This is the most common 'timeout' that most websites use to determine which users are online.  But again, just change it to whatever if you don't want to make it 5 minutes.

[Russia]

Okay, cool thanks, also, what if a person logs out by not clicking LOGOUT but clearing there session or something in firefox. How does I know to set the row to FALSE?

 

Or it doesnt? and keeps it as TRUE.

 

----

EDIT:

WOW, I really do appreciate you taking your own time coding that up for me, even tho you may have been busy before you read my thread.

 

[nethnet]

Well, if they clear their sessions or close their browser, you would have no way of knowing through PHP unless you used a more robust AJAX ping system, which for most practical purposes, is unnecessary (unless, of course, you are building some chat interface or game schematic where showing users online in realtime is important).

 

So yeah, in those cases it would stay TRUE, but after 5 minutes of inactivity, they would still show up as offline (the timer acts as a bit of a fallback for users who don't click logout or close their browsers).

[nethnet]

WOW, I really do appreciate you taking your own time coding that up for me, even tho you may have been busy before you read my thread.

 

Don't mention it, that's what I'm on these forums to do

 

As long as you understand what the code is doing, I don't feel too guilty writing it myself, haha.

[Russia]

incase you want some more things to help me out on, and brush up your skills just pm me.

 

Il be glad to give you a few things and payments too.