咨询：关闭浏览器/标签页时插入数据及用户活跃状态自动更新方案

Absolutely, this requirement is totally doable! Let’s walk through how to implement it, plus cover some more reliable alternatives for edge cases.

Short answer: Yes! You can track user activity and update their status when they close the browser, though you’ll need a mix of backend session handling and frontend events to cover both normal and edge cases.

1. Mark User as Active on Visiting index.php #

First, when a logged-in user hits index.php, you’ll want to update their status in your database (and ensure their session is active). Here’s a PHP example using PDO for database interactions:

<?php
session_start();

// Assume the logged-in username is stored in the session
if (isset($_SESSION['username'])) {
    $username = $_SESSION['username'];
    
    // Connect to your database (adjust credentials as needed)
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'db_user', 'db_password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Check if the user already has an activity record
    $checkStmt = $pdo->prepare("SELECT id FROM user_activity WHERE username = ?");
    $checkStmt->execute([$username]);
    
    if ($checkStmt->rowCount() > 0) {
        // Update existing record to mark as active
        $updateStmt = $pdo->prepare("UPDATE user_activity SET is_active = 1, last_active = NOW() WHERE username = ?");
        $updateStmt->execute([$username]);
    } else {
        // Insert new record if it doesn't exist
        $insertStmt = $pdo->prepare("INSERT INTO user_activity (username, is_active, last_active) VALUES (?, 1, NOW())");
        $insertStmt->execute([$username]);
    }
}
?>

Make sure your user_activity table has at least these columns: username (varchar), is_active (tinyint, default 0), last_active (datetime).

2. Handle Browser/Tab Closure to Mark as Inactive #

Browsers don’t trigger a standard backend request when closed, so you’ll need to use a frontend event to send a signal to your server. The beforeunload event works, but avoid synchronous AJAX (it can block the page from closing). Instead, use the navigator.sendBeacon() API—it’s designed for exactly this scenario, as it sends the request in the background without delaying page unload.

Add this script to your index.php (or your base template):

<script>
// Only run if the user is logged in
<?php if (isset($_SESSION['username'])): ?>
const currentUser = "<?php echo htmlspecialchars($_SESSION['username']); ?>";

window.addEventListener('beforeunload', (event) => {
    // Send a beacon request to mark the user as inactive
    navigator.sendBeacon('/mark_inactive.php', new URLSearchParams({
        username: currentUser
    }));
});
<?php endif; ?>
</script>

Then create the mark_inactive.php backend script to handle the request:

<?php
if (isset($_POST['username'])) {
    $username = $_POST['username'];
    
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'db_user', 'db_password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare("UPDATE user_activity SET is_active = 0, last_active = NOW() WHERE username = ?");
    $stmt->execute([$username]);
}
?>

The beforeunload method works for normal browser closures, but it won’t handle edge cases like:

• Browser crashes
• Network drops mid-session
• Users forcing the browser closed

For these scenarios, a heartbeat system combined with scheduled database cleanup is more robust:

Step 1: Frontend Heartbeat #

Add a script to send periodic signals to the server to update the user’s last active time:

<script>
<?php if (isset($_SESSION['username'])): ?>
const currentUser = "<?php echo htmlspecialchars($_SESSION['username']); ?>";

// Send a heartbeat every 30 seconds
setInterval(() => {
    navigator.sendBeacon('/heartbeat.php', new URLSearchParams({
        username: currentUser
    }));
}, 30000);
<?php endif; ?>
</script>

Step 2: Backend Heartbeat Handler (heartbeat.php) #

<?php
if (isset($_POST['username'])) {
    $username = $_POST['username'];
    
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'db_user', 'db_password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare("UPDATE user_activity SET last_active = NOW() WHERE username = ?");
    $stmt->execute([$username]);
}
?>

Step 3: Scheduled Cleanup Task #

Set up a cron job (Linux) or task scheduler (Windows) to run a script every minute that marks users as inactive if they haven’t sent a heartbeat in X minutes (e.g., 5 minutes):

Example cron job command (runs every minute):

* * * * * php /path/to/cleanup_inactive_users.php

And cleanup_inactive_users.php:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'db_user', 'db_password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Mark users inactive if last active was over 5 minutes ago
$stmt = $pdo->prepare("UPDATE user_activity SET is_active = 0 WHERE last_active < DATE_SUB(NOW(), INTERVAL 5 MINUTE) AND is_active = 1");
$stmt->execute();
?>

Combine both approaches:

• Use beforeunload + beacon for immediate status updates when users close the browser normally.
• Use the heartbeat + scheduled cleanup to handle unexpected scenarios where the browser doesn’t send the unload signal.

This way, you’ll cover all bases for accurate user activity tracking.

内容的提问来源于stack exchange，提问作者Jerad