amokske amokske - 1 month ago 13
SQL Question

time calculation working on localhost but not live site

I have the following block of code, which works perfectly on localhost but does not work on the live site. I have updated my xampp so that i am running PHP 7 as i am on my server just incase that was an issue.

<?php

try {
$minutes = 10;
$status = date('Y-m-d H:i:s', time() - $minutes * 60);
$getOnlineUsers = $db->query("SELECT user_online.*, members.username,
(SELECT COUNT(id) FROM user_online WHERE last_active > '$status' AND memberID = '0') AS guest
FROM user_online
LEFT JOIN members ON members.memberID = user_online.memberID
WHERE last_active > '$status' ");
$total = ($getOnlineUsers->rowCount());

if ($total > 0) {
while ($row = $getOnlineUsers->fetch()) {
echo '<ul>';
if ($row['memberID'] == '0') {
echo '<li>guest' . $row['id'] . '</li>';
} else {
if ($row['memberID'] == '1') {
echo '<li>' . $row['memberID'] . ' ' . $row['username'] . ' <a>ADMIN</a></li>';
} else {
echo '<li>' . $row['memberID'] . ' ' . $row['username'] . '</li>';
}
}
echo '<li>online: ' . $row['last_active'] . '</li>';
echo '</ul>';
echo '<br />';
}
} else {
echo 'nobody online';
}
} catch (PDOException $e) {
echo $e->getMessage();
}

echo '<br />';
echo $total; // added just to see number rows returned
?>


What i am trying to do is list all members and guests that are 'online/visiting my site. The
last_active
value is stored as a timestamp and updated when a user views a new page, with the idea being that any user logged that hasn't had their
last_active
updated within 10 minutes
$minutes=10;
of 'real-time'is not shown in the results.

As it stands i am not getting any errors, but if i mess around with the code then errors are created, so reporting is working fine too.

i have added
$total;
at the foot of the script to show me how many rows are being returned, and here i am seeing an issue as it is returning '0' - ONLY on live site tho, localhost is fine.

If i alter
$minutes=10
to
$minutes=90
then on the live site it works! however, if i go below '90' then it doesn't.

I am just completley baffled as to what the issue is as it works on localhost but then not on the live site.
I have checked that all database fields are the same, that values are being updated ok.. and all seems just fine.

Is there anything obvious that i am not seeing here, or any suggestions as to why this could be happening?

Answer

I notice that you are calculating the timestamp in PHP, and then comparing that to MySQL values. This does come at the risk that the PHP and MySQL server aren't on the same time.

On localhost, there's really no possibility for confusion, but it's possible that your live server has PHP running on, say, BST while your MySQL server is on UTC. (Sadly, I speak from experience T_T) The problem gets worse if the MySQL server is a different physical machine, as they could be seconds or even minutes apart (or, again from experience, 46 minutes out of sync...)

The safest way is to not cross-calculate dates like this. Instead, consider using:

WHERE last_active > DATE_SUB(NOW(), INTERVAL $minutes MINUTE)

But in addition to this you should make sure that PHP and MySQL are in agreement on what the time is:

SELECT NOW();
echo date("Y-m-d H:i:s");
Comments