Get next entry with closest date...

[jbachris]

I'm trying to run a query to return a result from the database that is enter with the next calendar date.

So, I have two entries in the database, entry 1 has a match_date of May 6 (20060506) and entry 2 has a match_date of May 13 (20060513). So right now, entry 1 should be displayed until May 7, then May 13 should be displayed. I hope that explains in fairly good detail what I am trying to achieve. Here is my query:

SELECT date_format(fixture_date, '%b.%e.%Y')as match_date, fixture_id, opponent, location, match_type FROM fixtures WHERE ????? ORDER BY fixture_date DESC LIMIT 1

I'm not sure about the WHERE clause, can someone please help?

[Shrike]

Add a WHERE clause which ensures the date of each record is in the future, e.g.

WHERE fixture_date > CURDATE() // CURDATE() is a MySQL function

The ORDER BY clause which you already have ensures that the results are in date order, but you might want ASC not DESC.

[jbachris]

Thanks! One more quick question pertaining to this query, if I want to run a seperate QUERY and display a daily countdown to the next match that displays

2 DAYS

-or-

1 DAY

based on the info, how would I do that?

[Shrike]

You'd need to subtract the fixture date from the current date. Timestamps would work.

[bradgrafelman]

You can also use the INTERVAL in your query, I believe. Something like...

CURDATE() - INTERVAL 1 DAY