php - 无法以正确的顺序显示 mysqli 数据库结果

Question

我的数据库中有一个表，用于存储事件及其过期日期。我想要做的是显示我网站上的所有事件，但先显示当前事件，然后显示过期事件？

我尝试了以下sql

$sql = "SELECT noticeID, notice, noticeDesc, noticeIMG, noticeDate,
expiryDate FROM tbl_notices GROUP BY noticeID ,expired ORDER BY
expiryDate ASC";

但是由于在我的查询中 expiryDate 按 ASC 顺序排列，因此首先返回过期结果。

我的 tbl_notice 结构如下所示

noticeID => int(4)
notice => VARCHAR(100)
noticeDesc => text
noticeDate => timestamp
noticeIMG => VARCHAR(40)
expiryDate => datetime
expired => int(1) 0 for current or 1 for expired

我的PHP代码是

$sql = "SELECT noticeID, notice, noticeDesc, noticeIMG, noticeDate,
expiryDate FROM tbl_notices GROUP BY noticeID ,expired ORDER BY expiryDate ASC";
$result = mysqli_query($conn,$sql);

        if ($result->num_rows > 0)
        {
            // output data of each row
            while($row = $result->fetch_assoc())
            {
                $id = $row['noticeID'];
                $title = htmlspecialchars($row['notice']);
                $urltitle = strtolower($title);
                $urltitle = str_replace(" ","-",$urltitle);
                $date = $row['noticeDate'];
                $datetime1 = new DateTime("2010-06-20");
                $expiryDate = $row['expiryDate'];
                $link = "announcements.php";
                $body = strip_tags($row['noticeDesc']);
                $filename = $row['noticeIMG'];
                if($filename != "")
                {
                    $imgLink = "images/".$filename;
                }
                else
                {
                    $imgLink = "images/announcement-default.jpg";
                }?>

                <div class="news-container-sm">
                    <img class="pin" src="images/thumbtack_pushpin_2_thumb.png" alt="News Pin" title="News Pin">
                    <div class="news-img"><img class="fluid" src="<?php echo $imgLink?>" alt="<?php echo $title?>" title="<?php echo $title?>" /></div>
                        <div class="fluid news-headline"><?php echo $title?>
                            <span class="fluid news-date"><?php echo "expires in ".humanTiming( strtotime($expiryDate) );?></span>
                        </div>
                    <div class="fluid news-desc"><?php echo $body;?></div>
                </div><?php
            }           
        }
        else
        {
            echo "No Announcements!";
        }
        $conn->close();?>

但就像我说的，首先返回过期记录，因为按升序排序的 expiryDate 将排在第一位，我如何将过期记录推到末尾？

如此有效地排序并显示所有当前通知，然后排序并显示过期通知。

希望这是有道理的感谢任何帮助

非常感谢卢克

Answer 1

使用这种形式的查询:

$sql = "SELECT noticeID, notice, noticeDesc, noticeIMG, noticeDate, expiryDate
FROM tbl_notices ORDER BY expired, expiryDate ASC";

原始查询中的 GROUP BY 过多。