Get Currently online users with php pdo

Question

I want to get currently online users with the help of their last login time . My timezone in php is "Asia/Karachi".

What i have tried so far:

$stmt = $db_con->prepare("SELECT COUNT(*) AS id 
                        FROM users 
                        WHERE last_login_time > DATE_SUB(NOW(), INTERVAL 5 MINUTE)");
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_rows = $row['id'];
echo total_rows;

Login process php:

$login_time = date('y-m-d h:i:s');
$stmt =$db_con->prepare("UPDATE users SET last_login_time='".$login_time."',last_login_ip='".$login_ip."' WHERE email='".$user_email."'");
$stmt->execute();

SQL TABLE:

The problem: i want to show the online users in my php file and there is always digit 0 even through my last login time is smaller than 5minutes .but when i change the INTERVAL 5 MINUTE to INTERVAL 50000 MINUTE ,it shows the number of online users. I have tried a lot but not getting to any point!

Is your MySQL hosted in the same time zone as your website works out login times? — Martin, Feb 05 '18 at 13:55
`AND last_login_time IS NOT NULL` I am assuming you set the table up to set the `last_login_time` to default to NULL — RiggsFolly, Feb 05 '18 at 13:56
You do not need to prepare queries that contain no binable parameters. You could use a simple `$db_con->query()` — RiggsFolly, Feb 05 '18 at 13:58
[Try this solution](https://stackoverflow.com/questions/34428563/set-timezone-in-php-and-mysql) — Martin, Feb 05 '18 at 13:58
is your mysql connection in PHP `localhost` or is it remote? — Martin, Feb 05 '18 at 14:08

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

Please See UPDATE at the bottom:

I believe your issue is based on the MySQL using a different time zone to your PHP. This will be almost certainly true if your MySQL connection from PHP is not localhost.

However, even if you are using localhost, you may have somehow miss-set your MySQL to be using a different time zone to your PHP system.

So first; read this post to get the current timezone of your MySQL, and to output a Hours / Minutes result:

SELECT TIMEP_FORMAT(TIMEDIFF(NOW(), UTC_TIMESTAMP), '%H%i') as MySQLTime

And compare this with your PHP timezone value retrieved from date_default_timezone_get() and your PHP system time:

print date("r");

Once you've compared and found these are different time values then adjust your MySQL time zone using this answer here .

In PHP:

<?php
 define('TIMEZONE', 'Europe/Paris');
 date_default_timezone_set(TIMEZONE);

For MySQL:

<?php
$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;
$offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);

//Your DB Connection - sample
$db = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpassword');
$db->exec("SET time_zone='$offset';");

UPDATE:

My timezone in php is "Asia/Karachi".

This is your problem. Your MySQL is using UTC and your PHP is using "Asia/Karachi".

So assuming you want to keep your PHP timezone and apply this timezone to your MySQL you need to set the MySQL Timezone, using this excellent answer.

If you can't use the core methods outlined in the link above, then you can do this at script execution time with PDO:

$pdo = new PDO('mysql:host=localhost;dbname=exampletable', 'user', 
       'pass', [PDO::MYSQL_ATTR_INIT_COMMAND =>"SET time_zone = 'Asia/Karachi'"]);

You will need to manually update all timestamps already set in the DataBase but new timestamps added will be added in the correct timezone.

Thank you very much for this anwers.It made my day after a long research i finnally solved it .My php time was different from mysql time.Followed the above steps and it solved the issue. — Abdulmanan, Feb 12 '18 at 07:42
I have a similar question .instead of making time zone change in php.ini .How can i make it so that it should be equal to the timezone of server ,suppose if a user is from Europe timezone its time should be different. — Abdulmanan, Feb 12 '18 at 07:50
By default PHP uses the server timezone as supplied to it. If not, then it uses the timezone specified in `php.ini`. Server time zone does not change depending on where the *visitor* is located. — Martin, Feb 12 '18 at 14:17

score 0 · Accepted Answer · answered Feb 06 '18 at 13:16

This is most likely because you are using Asia/Karachi as your default timezone in PHP but your database is defaulting to UTC. It's better to use UTC both in MySql and PHP, and just use the local timezone for display.

If you must store your dates in Asia/Karachi, you have a few options. One would be to set your MySql timezone to Asia/Karachi. The other would be to do the date arithmetic in PHP. So instead you would do:

$five_min_ago = date('Y-m-d H:i:s', strtotime('-5 minutes'));  

$stmt = $db_con->prepare("
    SELECT COUNT(*) AS id FROM users 
    WHERE last_login_time > :five_min_ago");   

$stmt ->execute([':five_min_ago' => $five_min_ago]);

Also pay attention to your date format string, use capital Y and H.

Thanks for such a straight forward answer.Works perfectly! – Abdulmanan Feb 12 '18 at 08:43 — Abdulmanan, Feb 12 '18 at 08:43

Get Currently online users with php pdo

2 Answers2

UPDATE: