如何通过MySQL+PHP筛选指定时间段内登录的用户？

Alright, let's walk through how to solve this problem with MySQL and PHP—here's a straightforward, practical approach:

1. 编写MySQL查询语句 #

First, we need a query that checks the login status of all three users in one go. Using conditional aggregation is clean here: we'll count how many times each user logged in during the 19:00-20:00 window, then use those counts to validate our conditions.

SELECT
    -- 统计user1在目标时间段的登录次数
    SUM(CASE WHEN username = 'user1' AND TIME(login_time) BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END) AS user1_logged,
    -- 统计user2的登录次数
    SUM(CASE WHEN username = 'user2' AND TIME(login_time) BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END) AS user2_logged,
    -- 统计user3的登录次数
    SUM(CASE WHEN username = 'user3' AND TIME(login_time) BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END) AS user3_logged
FROM login_table;

小说明： #

• TIME(login_time) extracts just the time portion from your datetime/timestamp field—perfect for checking the hour window regardless of the date.
• If you need to restrict this to today's 19:00-20:00, add AND DATE(login_time) = CURDATE() to each CASE condition.
• Adjust the time boundary if 20:00 counts as part of the window (change 19:59:59 to 20:00:00).

2. PHP代码实现（使用PDO，更安全可靠） #

We'll use PDO for database interactions (it's more secure and flexible than mysqli). Here's the full code with error handling and result validation:

<?php
// 数据库配置信息，替换成你的实际参数
$dbHost = 'localhost';
$dbName = 'your_database_name';
$dbUser = 'your_db_username';
$dbPass = 'your_db_password';

try {
    // 初始化PDO连接
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 执行查询
    $sql = "
        SELECT
            SUM(CASE WHEN username = 'user1' AND TIME(login_time) BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END) AS user1_logged,
            SUM(CASE WHEN username = 'user2' AND TIME(login_time) BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END) AS user2_logged,
            SUM(CASE WHEN username = 'user3' AND TIME(login_time) BETWEEN '19:00:00' AND '19:59:59' THEN 1 ELSE 0 END) AS user3_logged
        FROM login_table;
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $loginStatus = $stmt->fetch(PDO::FETCH_ASSOC);

    // 验证条件
    $user1LoggedIn = $loginStatus['user1_logged'] > 0;
    $user2LoggedIn = $loginStatus['user2_logged'] > 0;
    $user3NotLoggedIn = $loginStatus['user3_logged'] === 0;

    // 输出结果
    if ($user1LoggedIn && $user2LoggedIn && $user3NotLoggedIn) {
        echo "✅ 验证通过：user1、user2在19:00-20:00期间登录，user3未在此时间段登录。";
    } else {
        $errorMessages = [];
        if (!$user1LoggedIn) $errorMessages[] = "user1未在19:00-20:00期间登录";
        if (!$user2LoggedIn) $errorMessages[] = "user2未在19:00-20:00期间登录";
        if (!$user3NotLoggedIn) $errorMessages[] = "user3在19:00-20:00期间登录了";
        
        echo "❌ 验证不通过：" . implode("；", $errorMessages);
    }

} catch(PDOException $e) {
    echo "数据库操作出错：" . $e->getMessage();
}
?>

3. 关键注意事项 #

• 字段类型: Make sure your login_time column is of type datetime or timestamp—the TIME() function won't work correctly on plain text fields.
• SQL注入防护: Even though we're using fixed usernames here, using prepared statements (as shown) is a good habit to prevent injection attacks in dynamic scenarios.
• 日期范围: If you only care about the current day's login activity, don't forget to add the DATE(login_time) = CURDATE() condition mentioned earlier.

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