Engine
Engine

Reputation: 89

PHP Display amount of users online in the past 10 minutes

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.

Upvotes: 1

Views: 2235

Answers (3)

samayo
samayo

Reputation: 16495

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>";

}

Upvotes: 4

CodeAngry
CodeAngry

Reputation: 12995

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

:)

Upvotes: 3

Richard Abercrombie
Richard Abercrombie

Reputation: 313

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.

Upvotes: 0

Related Questions