php - 使用php连接两个表

问题描述

我不知道加入，我真的很难理解逻辑。谁能帮帮我吗？

这是我的表

AnnouncementID      Subject        Header      Status
---------------------------------------------------
1                    Peter        Header 2     Publish
2                     2x2         Header 3     Draft
3                 Resignation     Header 4     Publish

这是另一个表 ReadAnnouncements：

AnnouncementID      Username      Status
---------------------------------------------
1                    User 1        Read
2                    User 2        Read
2                    User 3        Read

我希望我的结果是

AnnouncementID      Username      Status    Header       Subject
---------------------------------------------------------------
1                    User 1        Read     Peter        Header 2
2                    User 2        Read     2x2          Header 3
2                    User 3        Read     2x2          Header 3

请教我已经尝试了两天了，我真的很困惑。

            <?php 
               $sql=" SELECT a.AnnouncementID,a.Created,r.Username,a.Status,a.Header,a.Body from Announcements a join ReadAnnouncements r using(AnnouncementID) WHERE a.Status = 'Publish'";
               $result = mysqli_query( $conn,$sql);

                 while($rows = mysqli_fetch_array($result)){
                  $time = date('h:i:s a',strtotime($rows['Created']));
                  $date = date('Y-m-d',strtotime($rows['Created']));
                    if($rows['ReadStatus'] == 'Unread'){
                    echo '
                    <tr class="'.$rows['Status'].'clickable-row" >
                      <strong><td class="view-message  dont-show"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none " class="text-dark"><div>'.$rows['Header'].'</div></a></td>
                      <td class="view-message "><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark" ><div>'.substr($rows['Body'],0,90).'</div></a></td>
                      <!--<td class="view-message  inbox-small-cells"><i class="fa fa-paperclip"></i></td>-->
                      <td class="view-message  text-right"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark"><div><h6>'.$time.''.'<br>'.''.$date.'</h6></div></a></td></strong></tr>                                        
                    ';                                
                    }else{
                       echo '<strong>
                    <tr class="'.$rows['Status'].'clickable-row" >
                      <strong><td class="view-message  dont-show"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none " class="text-dark"><div>'.$rows['Header'].'</div></a></td>
                      <td class="view-message "><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark" ><div>'.substr($rows['Body'],0,90).'</div></a></td>
                      <!--<td class="view-message  inbox-small-cells"><i class="fa fa-paperclip"></i></td>-->
                      <td class="view-message  text-right"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark"><div><h6>'.$time.''.'<br>'.''.$date.'</h6></div></a></td></strong></tr>                                        
                    </strong>';                             
                    }

                 } 
              ?>

我想从表公告中选择仅发布的所有行，并根据用户名和公告 ID 对它们进行分类（如果它们已读或未读）。

标签： phpsqlphpmyadmin

php - 使用php连接两个表

问题描述

解决方案

推荐阅读