Selecting the next date in MySQL

Question

I have a list of dates in a table in a MySQL database (the dates when a charity bookstall is to be held), which I want to display on a page. On one page I'm displaying the date of the next stall, and on another the dates of the stall in the next month. (Currently I'm using an unordered HTML list and selecting the dates with PHP, but it's a bit messy, and I also want to tie in the dates with the fundraising totals that are stored in the database).

I want to put the dates in a database though so that I can tie in the dates with the fundraising totals for each week. I'm thinking that once I can identify the date with the nearest up-coming date that I can use 'LIMIT 1' to select the next week's date for display, and 'LIMIT 4' say for where I need to display the dates for the next month, but what I can't figure out is how to identify the record with the nearest up-coming date - identifying the current date and then selecting the nearest date...I have a feeling there's probably one of the MySQL date functions that can be persuaded to help out in this, but can't figure out exactly how.

Any ideas on how I can do this?

Answer 1

If your column is a DATETIME field, you can identify the next by using SELECT...WHERE event_date > "2009-11-06" and ORDER BY event_date.

SELECT * FROM so_events 
WHERE event_date > "2009-11-06 15:36:00"
ORDER BY event_date ASC
LIMIT 4

MySQL will internally do the work for you and select rows where whose timestamp is greater than the one you specify in the WHERE clause.

Answer 2

If I understand correctly, you can just pick up next four dates that are after today. In MySQL you could use the CURDATE() function for the 'today' bit, then apply an order and limit to your select statement. For example,

SELECT stall_date
FROM   stall_dates
WHERE  stall_date >= CURDATE()  -- >= assumes you want today's to show too
ORDER BY
       stall_date
LIMIT 4

Use ORDER BY stall_date DESC to reverse the ordering if needed.