PHP Display amount of users online in the past 10 minutes

Question

I am trying to display the amount of users that have been active on my website within the last 10 minutes, however I am having trouble with the query syntax and receiving the error "mysql_num_rows() expects parameter 1 to be resource, boolean given..."

<?php 
$dt = date('Y-m-d h:i:s');
$checktime = $dt - 600;
$query = "SELECT * FROM users WHERE DATE(STR_TO_DATE(lastactive)) BETWEEN '$checktime' AND '$dt'";
$result = mysql_query($query);
$num = mysql_num_rows($result);
echo $num;
?>

Really need help to sort this out, and please don't comment on the deprecation of simple MySQL functions, I am aware of it.

Your SQL is failing. Try `mysql_error()` to see what the database is returning. — andrewsi, Jul 09 '13 at 19:09
`mysql_query() returns a resource on success, or FALSE on error.` From the PHP documentation. Add `or die(mysql_error())` after the query part (before the `;`). — Travis DePrato, Jul 09 '13 at 19:11
Okay, I got "Incorrect parameter count in the call to native function 'STR_TO_DATE'" — Engine, Jul 09 '13 at 19:13
I'd suggest that you look at `$checktime` - date() returns a formatted string, and you probably won't get what you expect if you subtract 600 from it. — andrewsi, Jul 09 '13 at 19:13
That's all fine, it works perfectly for returning all the users that were recently online and printing them, but I used PHP to display only them rows, so I couldn't show the amount of rows returned too. — Engine, Jul 09 '13 at 19:16
Possible duplicate [mysql_fetch_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select?answertab=votes#tab-top). — John Conde, Jul 09 '13 at 19:47
See [this answer](http://stackoverflow.com/a/11674313/250259) for how to troubleshoot this. — John Conde, Jul 09 '13 at 19:47

samayo · Answer 1 · 2017-03-29T15:50:54.203

At this time, using mysql_ functions, to interact with your database will allow people to hack your website easily. Which is why, we recommend you use mysqli_ or PDO. I am a fan of PDO so, I will provide a simple example, which will do the job, and plus keep your site safe from mysql injection attacks.

 // last seen time 
 $time = 10:00:00; 

try {
   # First let us connect to our database 
   $conn = new \PDO("mysql:host=localhost;dbname=xx;charset=utf8mb4", "xx", "xx", []); 
 } catch(\PDOException $e){
   echo "Error connecting to mysql: ". $e->getMessage();
 }

$users_online = $conn->prepare("
   SELECT id, username, online_time FROM users 
   WHERE TIMEDIFF(CURTIME(), online_time) < TIME (?) 
   ORDER BY online_time"
);


$users_online->execute(array($time));

foreach($users_online as $user){

   echo "<p> User: {$user['username']} is online </p>";

}

score 3 · Accepted Answer · answered Jul 09 '13 at 19:17

3

Never do what you are doing!

You are fetching a lot of data and counting rows when MySQL can do it all for you and return only a number... which is what you need! Like this:

SELECT COUNT(*) FROM `users` WHERE `lastactive` >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);

^ Assuming lastactive is a DATETIME field. And it's easy to play with the INTERVAL part. Can be:

INTERVAL 10 MINUTE
INTERVAL 1 HOUR
INTERVAL 1 DAY

:)

answered Jul 09 '13 at 19:17

CodeAngry

12,760
3
50
57

Thanks for this, it works perfectly, now my final problem is that when I am removing my 600 (10 minutes) in the $checktime variable, it obviously removes it from the year at the start in the format of "Y-m-d h:i:s", so how could I overcome this? – Engine Jul 09 '13 at 19:27
You don't care! MySQL does the date math for you. The query above means *count online users seen in the past 10 minutes calculated from now*. – CodeAngry Jul 09 '13 at 19:30
No, it is removing the 600 from 2013 (the year) rather than the time, so it isn't actually returning which users were online in the past 10 minutes :/ – Engine Jul 09 '13 at 19:48
@AqworldsDragon Did you notice my query? It does not need a date. It needs nothing other than the `table` name and the `lastactive` column. Really? **PS**: *It's worthless and sad to give a proper solution on SO if the beneficiary cannot understand it*... – CodeAngry Jul 09 '13 at 19:49
Yeah I realised, I was looking at my other query for returning the usernames and printing, sorry. But still, this is returning the number of who was online at the current date, not time :/ – Engine Jul 09 '13 at 19:50
@AqworldsDragon If `lastactive` is a `DATETIME`, like `SET lastactive = NOW()`... it tells you how many were online in the past 10 minutes from `NOW()`. – CodeAngry Jul 09 '13 at 19:54

score 0 · Answer 3 · answered Jul 09 '13 at 19:15

You need to check if the result is false first

if ($result === FALSE) {
    var_dump(mysql_error());
} else {
    $num = mysql_num_rows($result);
    echo $num;
}

On a side note, mysql_query, or mysql_ anything is deprecated, you should check out either MySQLi or PDO for running your queries.

PHP Display amount of users online in the past 10 minutes

3 Answers3