PHP+MySQL如何编写SQL查询用户通知的已读/未读状态

核心SQL语句 #

SELECT 
    n.ID,
    n.Body,
    n.Date,
    CASE WHEN w.UserID IS NOT NULL THEN 1 ELSE 0 END AS is_read
FROM Notifications n
LEFT JOIN IsWatchedNotifications w 
    ON n.ID = w.NotificationsID 
    AND w.UserID = :user_id;

关键细节说明 #

• 必须把用户ID过滤条件放在ON子句中，如果放在WHERE里会将未读通知（无对应关联记录）过滤掉，导致LEFT JOIN失去作用
• 通过CASE表达式生成is_read字段，直观区分已读（1）和未读（0）状态
• 使用表别名n和w简化语句结构，提升可读性

PHP代码实现示例（PDO版） #

// 假设已完成PDO数据库连接初始化
$currentUserId = 123; // 替换为当前登录用户的实际ID

try {
    $query = "SELECT 
                n.ID,
                n.Body,
                n.Date,
                CASE WHEN w.UserID IS NOT NULL THEN 1 ELSE 0 END AS is_read
            FROM Notifications n
            LEFT JOIN IsWatchedNotifications w 
                ON n.ID = w.NotificationsID 
                AND w.UserID = :user_id";
    
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(':user_id', $currentUserId, PDO::PARAM_INT);
    $stmt->execute();
    
    // 获取所有通知数据，包含已读状态
    $notifications = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // 返回JSON格式数据给Flutter应用
    header('Content-Type: application/json');
    echo json_encode($notifications);
} catch(PDOException $e) {
    // 异常处理
    echo json_encode(['error' => $e->getMessage()]);
}

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