Get closest mysql date

Question

I have the following dates in my table. How to find the nearest date either today (if today is a date), or if today does not exist, then the nearest past date?

2012-10-01 aa123 2012-10-02 aa43 2012-10-03 aa478 2012-10-04 aa40 2012-10-05 aa54 2012-10-06 de34 2012-10-07 a5434 2012-10-08 r4t 2012-10-09 x34 2012-10-10 q23 2012-10-11 b53 

So, if today is '2012-10-07' , then the entry will be a5434 . But if 2012-10-07 missing, the record will be de34 , which belongs to 2012-10-06 , since it will be the nearest last day from today.

I'm not sure where to start this, so I have not tried anything yet. You need sql solution.

Answer 1

It's simple, just get one of the last dates <= current date:

 $now = date("Ymd"); $sql = "SELECT * FROM date_table where date_field <= '$now' ORDER BY date_field DESC LIMIT 1 OFFSET 1"; 

Answer 2

Add the ORDER BY query to the query. The following will sort the rows by date, with the last at the top and the oldest at the bottom.

 SELECT `id`, `date` FROM `table` ORDER BY `date` DESC LIMIT 1;