将用户设置为活动/非活动PHP MySQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

<?php
// 连接数据库
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 更新用户活动状态
$user_id = 1; // 假设要更新的用户ID为1
$is_active = true; // 设置为活动状态

$sql = "UPDATE users SET is_active = ?, last_activity = NOW() WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $is_active, $user_id);

if ($stmt->execute()) {
    echo "用户活动状态更新成功";
} else {
    echo "更新失败: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

<?php
// 连接数据库
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 查询活动用户
$sql_active = "SELECT * FROM users WHERE is_active = TRUE AND last_activity > NOW() - INTERVAL 30 MINUTE";
$result_active = $conn->query($sql_active);

echo "活动用户:<br>";
while($row = $result_active->fetch_assoc()) {
    echo "ID: " . $row["id"]. " - 用户名: " . $row["username"]. "<br>";
}

// 查询非活动用户
$sql_inactive = "SELECT * FROM users WHERE is_active = TRUE AND last_activity <= NOW() - INTERVAL 30 MINUTE";
$result_inactive = $conn->query($sql_inactive);

echo "非活动用户:<br>";
while($row = $result_inactive->fetch_assoc()) {
    echo "ID: " . $row["id"]. " - 用户名: " . $row["username"]. "<br>";
}

$conn->close();
?>