get the nearest date mysql

Question

I have the following dates in my table. How do I find a closest date from either today (if today's date is there) or if today's date is not there 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' is then the record will be a5434. But if 2012-10-07 is missing then the record will be de34 which belongs to 2012-10-06 since that would be the closest past day from today.

I am not sure where to start on this one, so I haven't tried anything yet. Need a sql solution to this.

Answer 1

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

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

Answer 2

Add an ORDER BY statement to the query. The following will order the rows by their date, with the latest at the top and oldest at the bottom.

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