User online count with PHP session

In process of learning Core php with development of simple cms im stuck with user online process. im trying to count user online. somebody tod me thats it will be very easy with ajax but for now i want do it with php only.
some how i have achieve the user online at the moment but if i Login with user-(1) and log out user-(1) and again login user-(2) from same browser, user-(1) still shown online. And main use is that when i logout, user is still shown online still the time set (i have set 5min time for in active user). how can i send user offline after user is logout so that i dont to wait 5min to see him offline.
Below is my code what i have done till now.

//page to display user online
<?php include "../includes/db.php"; 
php ob_start(); 
php session_start(); 
?>

<?php
$user_id = $_SESSION['user_id'];
$username = $_SESSION['syner'];
$session_id = session_id();
$date = date('d-m-y');
$time = time();
$time_out_session = 300;
$time_out = $time - $time_out_session;


$query = "SELECT * FROM online_users WHERE session_id = '$session_id' AND user_id = '$user_id'";
        $send_query = mysqli_query($connection, $query);

        if(!$send_query){
                      die("Send Query Failed. " .mysqli_error($connection));
             }
$count = mysqli_num_rows($send_query);

    if($count == NULL){
      $session_insert = 
      "INSERT INTO online_users(user_id,username,session_id,time,date) 
       VALUES($user_id,'$username','$session_id','$time',now() )";

       $my_query = mysqli_query($connection, $session_insert);

       if(!$my_query){
                  die("Count Query Failed. " .mysqli_error($connection));
             }
          }

$st=mysqli_query($connection, "UPDATE online_users SET status='ON', time = '$time' WHERE session_id = '$session_id'  ");
        if(!$st){
             die("Set ON Query Failed. " .mysqli_errormysqli_error($connection));
          }

 $qt=mysqli_query($connection, "SELECT user_id FROM online_users WHERE status = 'ON' AND time > '$time_out' ");
       $count_user = mysqli_num_rows($qt);


 $ut=mysqli_query($connection, "UPDATE online_users SET status='OFF' WHERE time < '$time_out'");
      if(!$ut){
             die("Set OFF Query Failed. " .mysqli_errormysqli_error($connection));
            }

 ?>

<h1>User Online:<?php echo $count_user; ?> </h1>

Code For Log out page:

<?php 
session_start(); 
$q = mysqli_query($connection,"UPDATE online_users SET status='OFF' WHERE session_id='$session_id' ");

session_unset();
session_destroy();

//header("Location: ../index.php");

?>

User is still shown online even after logout… how i show user offline once he is logout so that i dont have to wait 5min.

The user is still in the online_users table, but with status set to OFF. You would need to filter for that in the query (add a WHERE clause).

Also, it’s much more performant to do a SELECT count(*) and get a result in a single row that contains the count, then fetching all rows and counting them.

Though on that train of thought… why are you saving session information after a session has been terminated?

If a user logs off, delete the row from online_users.
If a user times out, delete the row from online_users.

Otherwise, your online_users table will grow interminably.

Additional lines of thought: INSERT...ON DUPLICATE KEY UPDATE could save you a whole lot of hassle here…

And stops having an appropriate table name, as soon as it also includes users that are not on line.

1 Like

im newbies so unaware of ‘SELECT count (*)’ i will look into this… and in logout page i have used where clause:

$q = mysqli_query($connection,"UPDATE online_users SET status=‘OFF’ WHERE session_id=’$session_id’ ");

after i unset all the session i have created it working fine when logout.
i thought that with session_unset(); session_destroy(); it will completely destroy session but i have unset all session that i have created…

i cant delete the row because i need to display user information with login details so that we can know when users last login…

and im sorry but could get this line what you meant to say… could please be more specific, it will help me lot…

… so store a field in the users table that has a date of last login? Or is there more being reported that would require you to store a record of every login from every user ever?

Okay… So right now, you’re doing the following queries:

SELECT to find out if the user’s session already exists
INSERT if it doesnt,
UPDATE the row.

We can combine ALL of that into a single query.
MySQL (and most SQL databases) have a concept of a conditional insert.
INSERT...ON DUPLICATE KEY UPDATE queries do the following:

Try to INSERT the record.
If there was an error due to a Key collision (Your table does have a key, right?), then instead UPDATE the record.

Making a query of this kind is as simple as combining the two (INSERT and UPDATE) statements.

So if I take your queries, and reconstruct them as an INSERT…ON DUPLICATE KEY UPDATE query, assuming you have a key on either the session_id, or a combination of user_id and session_id

INSERT INTO online_users(user_id,username,session_id,time,date) 
VALUES ($user_id,'$username','$session_id','$time',now() )
ON     DUPLICATE KEY UPDATE status='ON', time = '$time'

(I would suggest you also look into prepared statements, but that may be beyond your scope at this time.)

Also… is your time field a string?

This doesnt seems to work for me,may be i didnt something wrong, i tried following too.

INSERT INTO online_users(user_id,username,session_id,time,date) 
VALUES ($user_id,'$username','$session_id','$time',now() )
ON     DUPLICATE KEY UPDATE status=values(status), time =values(status);

my database snapshot, i have remove primary key as well.
7

mysql prepared statements or PDO. i was tod to move with PDO rather then mysql prepared statements. which one do you recommenced

You should use PDO and prepared statements.

When a user is a guest user (not logged in), how are you recording that they’re a guest user?

Is the condition that I gave for the code working true?

Also why are you setting the time to the value of status?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.