Online Script

[dean7]

Hey guys, I'm currently trying to code an online script which simply displays whos online and a link to their profile.

 

I have coded something similar in MySQL but changing to PDO has made it harder, but anyway, I have a weird problem in which it don't always show when the user is online then when it does after some little time it disappears showing, but when it don't show the user whos online it counts that there is one user online but when it shows the user online it counts two users even though there is only one

 

This is how I update my timestamp in my functions file:

$TimeNow = time() + (60 * 10);
$LoggedIn1 = $db->prepare("UPDATE UsersRegTable SET timestamp = :timestamp WHERE username = :username");
$LoggedIn1->bindValue(':timestamp', $TimeNow);
$LoggedIn1->bindValue(':username', $Username);
$LoggedIn1->execute();

This is my online code:

<?php
$timenow = time();
$UsersTable = $db->prepare("SELECT * FROM UsersRegTable WHERE timestamp > $timenow ORDER by id DESC");
$UsersTable->execute();
$UsersTable1 = $UsersTable->fetchObject(); 
 
$NoUsers = "SELECT COUNT(*) as totalusers FROM `UsersRegTable` WHERE timestamp > $timenow";
$NumQuery = $db->prepare($NoUsers);
$NumQuery->execute();
 
 
$UsersObj = $NumQuery->fetchObject();
$UsersObj->totalusers;
 
$HowManyOnline = ($UsersObj->totalusers) ? "{$UsersObj->totalusers} " : "0";
 
// Shows correct amount when it shows nobody online but shows wrong amount when displays someones online
 
 
// $num = mysql_num_rows($select);
while ($UsersTable11 = $UsersTable->fetchObject()){
 
 
 
$UsersInfo = $db->prepare("SELECT * FROM UserInfoTable WHERE username = :username");
$UsersInfo->bindParam(":username", $Username);
$UsersInfo->execute();
$UsersInfo1 = $UsersInfo->fetchObject();
 
 
if($UsersInfo1->userlevel == "2"){ // Admin
$Name = "<font color='#000000'>$UsersInfo1->username</font>";
}elseif ($UsersInfo1->userlevel == "3"){ // Owner
$Name = "<font color='A7A7A7'>$UsersInfo1->username</font>"; 
}else{ 
$Name = "$UsersTable11->username";
}
 
echo "<a href='profile.php?view=$UsersInfo1->username'> $Name </a>,";
}
?>  

Any help to get me around this would be great appreciated!

 

Thanks  

[Jacques1]

You skip the first user, because you fetch one row manually (for whatever reason) and then immediately overwrite the variable in the loop.

 

And you really shouldn't do three queries when you only need one. Not only does it bloat the code. It can (at least theoretically) lead to wrong results if a user is logged out just between selecting the users and counting them.

 

Simply join the two tables and then select all users who are currently online. This gives you both the count and the details.

Edited July 22, 2016 by Jacques1

[Psycho]

I would also add that it would be a better idea to handle the timestamps using MySQL than in the PHP code. Also, I think it makes more sense to make the timestamd represent the last activity of the user instead of some arbitrary time in the future. It give you a lot more flexibility - e.g. select the users active within the last 5 minutes or 10 minutes or whatever.

 

Don't use "SELECT *" when you don't really need all the columns. There are performance and security reasons why it is not a best practice.

 

Plus, never run queries within loops - it is a huge resource hog. Learn to do proper JOINs as Jacques1 suggest. Your current loop is running a query for every record in the prior result set, but it pulling the data for the current logged in user - not the user in each record of the result set.

[dean7]

I would also add that it would be a better idea to handle the timestamps using MySQL than in the PHP code. Also, I think it makes more sense to make the timestamd represent the last activity of the user instead of some arbitrary time in the future. It give you a lot more flexibility - e.g. select the users active within the last 5 minutes or 10 minutes or whatever.

 

Don't use "SELECT *" when you don't really need all the columns. There are performance and security reasons why it is not a best practice.

 

Plus, never run queries within loops - it is a huge resource hog. Learn to do proper JOINs as Jacques1 suggest. Your current loop is running a query for every record in the prior result set, but it pulling the data for the current logged in user - not the user in each record of the result set.

Would it be ideal to hold the timestamp in session then?

 

Thank you for the advice about the query in the loop and selecting all so have change them.

 

These JOINS, I've looked up on them on Google as I've not actually used them and they say about Inner Join, Outter Join, Left join, Right join etc, what join would I need to use for my code? Getting my head around them is seeming rather hard right now 

[mac_gyver]

before proceeding with a JOIN'ed query, do you even need two tables?

 

if the UsersRegTable is only for the user online data, yes, you would need it and you would need to join it with the UserInfoTable table. however, based on the name UsersRegTable, this is general user information that happens to have a column to hold the last active time. if this is so, why do you have a second table UserInfoTable that is also holding general user information?

 

if you do need two tables, you should relate them with the user_id, an auto-increment integer column in the primary table, not the username.

 

also, if the UsersRegTable is only for the user online data, you likely have a SELECT query, followed by either an INSERT or the UPDATE query that you posted. this can all be replaced with a single INSERT ... ON DUPLICATE KEY UPDATE query.