BBLJ84 BBLJ84 - 28 days ago 16
MySQL Question

trouble displaying mysqli database results in correct order

I have a table in my database which stores events and the date they expire. What i want to do is display all the events on my website but display the current events first then the expired events after?

i have tried the following sql

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


but that returns the expired results first due to the expiryDate being ordered ASC in my query.

My tbl_notice structure looks like this

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


my php code is

$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();?>


but like i say that is returning the expired records first because the expiryDate which being sorted Ascending would be first, how would i push the expired records to the end?

so effectively sort and display all the current notices then sort and display the expired notices.

hope this makes sense
any help is appreciated

Many Thanks
Luke

Answer

Use this form of the query:

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

GROUP BY in the original query is excessive.