How can I order this?

[joshmpratt]

I've got this code below. Right now it is ordering everything by 'date1' in my mySQL database. But, in the database I am going to have date1, date2, date3, date4. When date1 expires, it is no longer displayed, in which case, I need it to then sort by the date2 for that row. Or if date 1 and 2 are expired, then I need ot to sort that row by date3. So, in every row, there will be expiring dates. It's as though I need another field in the database that somehow learns which date is should be sorted by. I just have no idea how to do that.

How can I accomplish this? Any ideas? If you need more explanation as to what I'm trying to do, please let me know. I need to get this working as soon as I can.

<?php
$db->query("SELECT * FROM gtraining WHERE start_date<NOW() AND end_date>NOW() ORDER BY date1");
while($db->fetch()) {
echo "<h4>" . $db->row['title'] . "</h4>\n";
echo "<p>";
for ($i=0; $i<4; $i++) {
if (($db->row['date'.($i + 1)] > date('YmdHis')) && ($db->row['location'.($i + 1)] != "")) {
echo $db->row['location'.($i + 1)] . ": " . $db->row['dates_times'.($i + 1)] . "<br />\n";
}
}
echo "<p>" . $db->row['description'] . "</p>\n";
if ($db->row['link_text'] != "") {
echo "<p><img src=\"/g/link.gif\" class=\"icon\" target=\"_blank\" /> <a href=\"" . $db->row['link_url'] . "\">" . $db->row['link_text'] . "</a></p><p><br /></p>";
} else {
echo "</p><p><br /></p>";
}
}
?>

[notaloser]

it seems like you should be updating your date1 field each time that it expires rather than adding another useless field every time your record ends.. which gets complicated and really wouldnt work.

if you would explain your system a little more and explain everything you are trying to do, we could get some code written

[joshmpratt]

It's not useless at all. Each entry has up to 4 possible dates that the class is offered. All of the dates need to be displayed right when I post it. It would be far more work to go into the database and change all the dates instead of just writing some code that automatically orders it. If you look at my code, you can see that it displays all 4 dates if they are not expired.

[ahundiak]

You might be able to create a stored procedure that returns the first unexpired date and then sorts.

But your best bet would be to query all your records, store them in an array then use usort()