在用户会话销毁时清理数据库：实时在线状态管理的挑战与解决方案

// 假设这是WebSocket服务器的一部分
use Ratchet\MessageComponentInterface;
use Ratchet\ConnectionInterface;

class Chat implements MessageComponentInterface {
    protected $clients;
    protected $db; // 数据库连接

    public function __construct() {
        $this->clients = new \SplObjectStorage;
        // 初始化数据库连接
        // $this->db = new PDO(...); 
    }

    public function onOpen(ConnectionInterface $conn) {
        $this->clients->attach($conn);
        // 获取用户ID (例如从Session或认证信息中获取)
        $userId = $conn->resourceId; // 实际应用中需要更可靠的用户识别

        // 将用户标记为在线
        // $stmt = $this->db->prepare("INSERT INTO activeuserlist (user_id) VALUES (?) ON DUPLICATE KEY UPDATE last_active = NOW()");
        // $stmt->execute([$userId]);
        echo "New connection! ({$userId})\n";
    }

    public function onMessage(ConnectionInterface $from, $msg) {
        // 处理消息...
    }

    public function onClose(ConnectionInterface $conn) {
        $this->clients->detach($conn);
        $userId = $conn->resourceId; // 同上，需要更可靠的用户识别

        // 将用户标记为离线或从activeuserlist中移除
        // $stmt = $this->db->prepare("DELETE FROM activeuserlist WHERE user_id = ?");
        // $stmt->execute([$userId]);
        echo "Connection {$userId} has disconnected\n";
    }

    public function onError(ConnectionInterface $conn, \Exception $e) {
        echo "An error has occurred: {$e->getMessage()}\n";
        $conn->close();
    }
}

// 启动WebSocket服务器
// $server = IoServer::factory(new Chat(), 8080);
// $server->run();

// 当用户登录后，尝试建立WebSocket连接
const ws = new WebSocket('ws://your-websocket-server.com:8080');

ws.onopen = function() {
    console.log('WebSocket connection established.');
    // 此时服务器会收到onOpen事件并更新用户在线状态
};

ws.onclose = function() {
    console.log('WebSocket connection closed.');
    // 此时服务器会收到onClose事件并更新用户离线状态
};

ws.onerror = function(error) {
    console.error('WebSocket error:', error);
};

// ... 其他消息处理逻辑

// 假设用户已登录
function sendHeartbeat() {
    fetch('/api/update_online_status.php', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({ userId: 'current_user_id' }) // 实际中可能通过session或token识别
    })
    .then(response => response.json())
    .then(data => {
        if (data.status === 'success') {
            // console.log('Online status updated.');
        }
    })
    .catch(error => {
        console.error('Error updating online status:', error);
    });
}

// 每20秒发送一次心跳
setInterval(sendHeartbeat, 20000); 

// 首次加载页面时立即发送一次
sendHeartbeat();

<?php
session_start();
header('Content-Type: application/json');

// 假设已经建立了数据库连接 $pdo
// 确保用户已认证
if (!isset($_SESSION['user_id'])) {
    echo json_encode(['status' => 'error', 'message' => 'Unauthorized']);
    exit;
}

$userId = $_SESSION['user_id']; // 从会话中获取用户ID

try {
    $stmt = $pdo->prepare("INSERT INTO activeuserlist (user_id, last_active) VALUES (:user_id, NOW()) ON DUPLICATE KEY UPDATE last_active = NOW()");
    $stmt->execute([':user_id' => $userId]);
    echo json_encode(['status' => 'success']);
} catch (PDOException $e) {
    error_log("Database error: " . $e->getMessage());
    echo json_encode(['status' => 'error', 'message' => 'Database update failed']);
}
?>

<?php
// 假设已经建立了数据库连接 $pdo

// 定义离线阈值，例如3分钟（3 * 60秒）
$offlineThresholdSeconds = 3 * 60; 

try {
    // 从activeuserlist中删除超过阈值未活跃的用户
    $stmt = $pdo->prepare("DELETE FROM activeuserlist WHERE last_active < (NOW() - INTERVAL :threshold SECOND)");
    $stmt->execute([':threshold' => $offlineThresholdSeconds]);

    echo "Cleaned up " . $stmt->rowCount() . " offline users.\n";
} catch (PDOException $e) {
    error_log("Cron job database error: " . $e->getMessage());
    echo "Error during cleanup: " . $e->getMessage() . "\n";
}
?>