PHP Session script update users MySQL table column

[system]

Hi All,

I have a script that runs on every page to check whether the user is logged in and if not re-direct to the login page. I wanted this script to also update the users database column ‘LastTimeSeen’ so that I can run a query to see who is online or at least within 5 minutes.

My current code is;-
[php]include(‘login_config.php’);
session_start();
$user_check=$_SESSION[‘login_user’];

$ses_sql=mysqli_query($db,"select username, level, id from users where username=’$user_check’ ");

$row=mysqli_fetch_array($ses_sql,MYSQLI_ASSOC);

$login_session =$row[‘username’];
$login_level =$row[‘level’];
$login_id =$row[‘id’];

if(!isset($login_session))
{
header(“Location: …/login/”);
}[/php]

I know I need something along the lines of this;-
UPDATE users SET LastTimeSeen = NOW() WHERE id = $the_user_id
However I’m struggling to implement???

Once its working I will run the following query;-
SELECT COUNT(*) FROM users WHERE LastTimeSeen > DATE_SUB(NOW(), INTERVAL 5 MINUTE)

Any help is appreciated!!!

[Strider64]

I would at least suggest putting you login user in the you main configuration script. I think you call it login_config.php, but I would call it utilities.inc.php or config.php to be more general. That way you can only have to call session_start() once for that too will be in the configuration script. Then you could do something like

[php]/* Use $user for sessions variable */
$user = isset($_SESSION[‘user’]) ? $_SESSION[‘user’] : NULL;[/php]

then when the user logs in you can set $_SESSION['user];

This is how I do it:
[php] public function read(array $data = NULL) {
$this->query = ‘SELECT id, username, password, security_level, first_name, last_name, email, home_phone, cell_phone, gender, birthday FROM users WHERE username=:username’;
$this->query_params = [’:username’ => $data[‘username’]];

    try {
        $this->stmt = $this->pdo->prepare($this->query);
        $this->result = $this->stmt->execute($this->query_params);
    } catch (Exception $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }

    $this->stmt->setFetchMode(PDO::FETCH_OBJ);
    $this->user = $this->stmt->fetch();

    if ($this->user) {
        $this->loginStatus = password_verify($data['password'], $this->user->password);
        unset($data['password']); // Unset the password:
        unset($this->user->password);
    }

    if ($this->loginStatus) {
        $_SESSION['user'] = $this->user; // Set the session variable of user:
        return TRUE;
    } else {
        return FALSE;
    }
}[/php]

This is just an example. When you login user logs in that is when I would create a log of users who is logging in (write to a database table). You’re probably going to need some form of JavaScript / Ajax if you want to display the results live in real time. HTH…I sure other people here have some other ideas that might be better.

[DanTipple]

I would at least suggest putting you login user in the you main configuration script. I think you call it login_config.php, but I would call it utilities.inc.php or config.php to be more general. That way you can only have to call session_start() once for that too will be in the configuration script. Then you could do something like

[php]/* Use $user for sessions variable */
$user = isset($_SESSION[‘user’]) ? $_SESSION[‘user’] : NULL;[/php]

then when the user logs in you can set $_SESSION['user];

This is how I do it:
[php] public function read(array $data = NULL) {
$this->query = ‘SELECT id, username, password, security_level, first_name, last_name, email, home_phone, cell_phone, gender, birthday FROM users WHERE username=:username’;
$this->query_params = [’:username’ => $data[‘username’]];

    try {
        $this->stmt = $this->pdo->prepare($this->query);
        $this->result = $this->stmt->execute($this->query_params);
    } catch (Exception $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }

    $this->stmt->setFetchMode(PDO::FETCH_OBJ);
    $this->user = $this->stmt->fetch();

    if ($this->user) {
        $this->loginStatus = password_verify($data['password'], $this->user->password);
        unset($data['password']); // Unset the password:
        unset($this->user->password);
    }

    if ($this->loginStatus) {
        $_SESSION['user'] = $this->user; // Set the session variable of user:
        return TRUE;
    } else {
        return FALSE;
    }
}[/php]

This is just an example. When you login user logs in that is when I would create a log of users who is logging in (write to a database table). You’re probably going to need some form of JavaScript / Ajax if you want to display the results live in real time. HTH…I sure other people here have some other ideas that might be better.

Hi, thanks for your feedback… I altered my code and somehow I’ve managed to resolve my issue. My final code is;-

[php]<?php
include(‘login_config.php’);
session_start();
$user_check=$_SESSION[‘login_user’];

$ses_sql=mysqli_query($db,"select username, level, id from users where username=’$user_check’ ");

$row=mysqli_fetch_array($ses_sql,MYSQLI_ASSOC);

$login_session =$row[‘username’];
$login_level =$row[‘level’];
$login_id =$row[‘id’];

if(!isset($login_session))
{
header(“Location: …/login/”);
}
else
mysqli_query($db,“UPDATE users SET LastTimeSeen = NOW() WHERE id = $login_id”);

?>[/php]

Again thanks for your assistance.