How to retrieve data from a mySQL database based on current month using the [DATE] function?

Question

Okay so am using this structure in my SQL database:

id  |  title  |   date_start   |   date_end|  time_start   |   time_end   |   location   |

I am wanting to sort data from that table, but only the upcoming dates or dates that are currently occurring.

I've used the following query in php:

$query = mysql_query("SELECT * FROM events3 ORDER BY date_start DESC LIMIT 5");

Then after the query I fetched the results, then echoed them as so:

http://pastebin.com/5wS7npsr

That would display the only two dates in the database as:

ID:   2
Title:   New Years Eve
Date Start:   2012-12-31 - 2012-12-31
Time Frame:   12:00:00 - 12:00:00
Location:   Earth
Description:   The last day of 2012.


ID:   1
Title:   Christmas Day
Date Start:   2012-12-25 - 2012-12-25
Time Frame:   12:00:00 - 12:00:00
Location:   Earth
Description:   Merry Christmas!!

But I want it to expire after the date has occurred and only show the next upcoming ones.

Sounds like you want this: http://stackoverflow.com/questions/5182275/datetime-equal-or-greater-than-today-in-mysql — Mike Purcell, Dec 07 '12 at 00:51
The function that date_start and date_stop use is DATE and time_start and time_end use TIME — Harrison Howard, Dec 07 '12 at 00:56
`SELECT * FROM events3 WHERE date_start >= CURDATE() ORDER BY date_start DESC LIMIT 5;` — Mike Purcell, Dec 07 '12 at 00:58
Thank you. But now thats returning them starting with for example new years before christmas — Harrison Howard, Dec 07 '12 at 01:00
Right, that's because you have a desc query modifier, try removing the desc from the query and xmas will appear before nye. — Mike Purcell, Dec 07 '12 at 01:02
You can keep the limit, just remove the `DESC`. So you want this: `SELECT * FROM events3 WHERE date_start >= CURDATE() ORDER BY date_start LIMIT 5;` — Mike Purcell, Dec 07 '12 at 01:23

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

As mentioned in comments, there is a similar post here: Datetime equal or greater than today in MySQL

Ultimately, after some back and forth, the resulting query for your specific implementation:

SELECT * FROM events3 WHERE date_start >= CURDATE() ORDER BY date_start LIMIT 5;

edited May 23 '17 at 10:28

Community

1
1

answered Dec 07 '12 at 22:05

Mike Purcell

19,847
10
52
89

How to retrieve data from a mySQL database based on current month using the [DATE] function?

1 Answers1