What are the best practices for storing user activity in a database for online status tracking in PHP?

Storing user activity in a database for online status tracking in PHP involves creating a table to store user activity records with timestamps, updating the records when users log in or log out, and regularly checking and updating the status based on the last activity time. 

// Create a table to store user activity records
CREATE TABLE user_activity (
    user_id INT NOT NULL,
    last_activity TIMESTAMP NOT NULL,
    PRIMARY KEY (user_id)
);

// Update user activity record when user logs in
$user_id = 1;
$timestamp = date('Y-m-d H:i:s');
$query = "INSERT INTO user_activity (user_id, last_activity) VALUES ($user_id, '$timestamp') ON DUPLICATE KEY UPDATE last_activity = '$timestamp'";
// Execute the query

// Update user activity record when user logs out
$user_id = 1;
$query = "DELETE FROM user_activity WHERE user_id = $user_id";
// Execute the query

// Regularly check and update user status based on last activity time
$inactive_threshold = 5; // define the threshold in minutes
$current_time = time();
$query = "UPDATE user_activity SET status = IF(last_activity > DATE_SUB(NOW(), INTERVAL $inactive_threshold MINUTE), 'online', 'offline')";
// Execute the query

Keywords

database design user activity tracking online status PHP data storage

Related Questions