Sorting by DATE and then by ID when dates are same

[Michael Black]

Hello to all,

Have a news index page sorted by article date (MySql format YYYY-MM-DD)
Now doing PREVIOUS and NEXT links, trying to get ID's before and after current record's date.

Initially used for NEXT link:

SELECT ID FROM $newstable WHERE ID>$id ORDER BY ID ASC LIMIT 1

but only gave me gave next id, which doesn't correspond to date order.

Hence, I have:

1)non-contiguous ID's in relation to date and
2)have multiple news articles with same date.

ie.
ID--------ART_DATE
=================
800------2008-08-30
900------2008-08-28
1100----2008-07-01
....
1095----2008-06-25
1092----2008-06-25
1091----2008-06-25
....
1200----2008-04-21

So I need to sort by ART_DATE, however if date equal to current page date ($art_date), then need to sort by ID.


Maybe conditional flow like this, if logic is right, but not sure how to structure query:

$nextResult = mysql_query("SELECT ID FROM $newstable WHERE

CASE
IF {ART_DATE = $art_date} THEN ID>$id
ELSE ART_DATE > $art_date
END

ORDER BY ART_DATE ASC LIMIT 1 ") or die(mysql_error());

Am hoping I can do this in mysql and not PHP

Many thx
Michael