如何用PHP关联两张表？需筛选已发布公告并标记已读/未读

Hey there! Let's break this down step by step—JOINs can feel super confusing at first, but we'll get this sorted out for you.

First, let's unpack your core problem:
You want to show all Publish status announcements, and for each one, tell if a specific user has read it or not. Your current SQL uses an INNER JOIN, which only returns rows that exist in both tables. That means it'll only show announcements that the user has already read, but you probably want to see all Publish announcements (even unread ones) with their read status marked.

Let's start with JOIN basics to clear up the confusion: #

• INNER JOIN: Returns rows where there's a match in both tables. Think of it as the overlap between the two tables.
• LEFT JOIN: Returns all rows from the left table (here, Announcements), plus matching rows from the right table (ReadAnnouncements). If there's no match, the right table's columns will be NULL. This is exactly what we need to get all Publish announcements, even unread ones.

Fixing your SQL #

Assuming you're checking the read status for the current logged-in user (since your ReadAnnouncements table has a Username column), here's the corrected SQL. We'll also fix field aliases to avoid confusion between the two Status columns:

SELECT 
    a.AnnouncementID,
    a.Subject,
    a.Header,
    a.Status AS AnnouncementStatus, -- Rename to avoid clash with ReadAnnouncements.Status
    a.Created,
    a.Body,
    r.Status AS ReadStatus,
    r.Username
FROM Announcements a
LEFT JOIN ReadAnnouncements r 
    ON a.AnnouncementID = r.AnnouncementID 
    AND r.Username = ? -- Bind the current user's username here
WHERE a.Status = 'Publish'

What this does:

• LEFT JOIN ensures we get every Publish announcement from Announcements, regardless of whether the user has read it.
• The ON clause links announcements to the current user's read records only—so we don't get other users' read statuses mixed in.
• We alias the Status columns so we can tell apart the announcement's status (Publish/Draft) and the user's read status (Read/Unread).

Fixing your PHP code #

We'll adjust the code to handle unread announcements (where ReadStatus is NULL), and add security with prepared statements (never directly insert user data into SQL—it's a huge security risk!):

<?php
// Get the current logged-in user's username (adjust this to match your login system, e.g., from session)
$currentUsername = $_SESSION['username'];

// Use prepared statements to prevent SQL injection
$sql = "SELECT 
            a.AnnouncementID,
            a.Subject,
            a.Header,
            a.Status AS AnnouncementStatus,
            a.Created,
            a.Body,
            r.Status AS ReadStatus,
            r.Username
        FROM Announcements a
        LEFT JOIN ReadAnnouncements r 
            ON a.AnnouncementID = r.AnnouncementID 
            AND r.Username = ?
        WHERE a.Status = 'Publish'";

$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "s", $currentUsername);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

while($rows = mysqli_fetch_array($result)){ 
    $time = date('h:i:s a', strtotime($rows['Created'])); 
    $date = date('Y-m-d', strtotime($rows['Created'])); 
    // Mark unread announcements (where ReadStatus is NULL)
    $readStatus = $rows['ReadStatus'] ?? 'Unread';
    
    // Set row class based on read status (add styling for unread if you want)
    $rowClass = $readStatus === 'Unread' ? 'Publish clickable-row unread' : 'Publish clickable-row';
?>
    <tr class="<?php echo $rowClass; ?>">
        <td class="view-message dont-show">
            <a href="ViewAnnouncement.php?view_id=<?php echo $rows['AnnouncementID']; ?>" style="text-decoration: none" class="text-dark">
                <div><?php echo $rows['Header']; ?></div>
            </a>
        </td>
        <td class="view-message">
            <a href="ViewAnnouncement.php?view_id=<?php echo $rows['AnnouncementID']; ?>" style="text-decoration: none" class="text-dark">
                <div><?php echo substr($rows['Body'], 0, 90); ?></div>
            </a>
        </td>
        <td class="view-message text-right">
            <a href="ViewAnnouncement.php?view_id=<?php echo $rows['AnnouncementID']; ?>" style="text-decoration: none" class="text-dark">
                <div><h6><?php echo $time . '<br>' . $date; ?></h6></div>
            </a>
        </td>
    </tr>
<?php
} 
?>

Quick note about your expected result #

Your sample expected result includes AnnouncementID 2, but that announcement has a Draft status—so it won't be included in the query since we're filtering for Publish. If you meant to include Drafts too, just remove the WHERE a.Status = 'Publish' clause. Also, your expected result swaps the Subject and Header columns compared to your table structure—if that's intentional, just swap a.Subject and a.Header in the SELECT statement.

Hope this clears up the JOIN confusion and gets your code working as expected!

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