CODEWITHSUNDEEP
mysqldatabaseeventsformat
Jesse Pfieffer
Jesse Pfieffer

Reputation: 85

Pull events from database in order from today's date on

I am building a community site for my fire department and they want to show upcoming events but I don't want to show events that have already passed so here is what I have ( I am using dreamweaver btw).

Database with tables (table = events1532):

-id (int)
-event_start (datetime) YEAR-MO-DA HR:MI:SE
-event_end (datetime) YEAR-MO-DA HR:MI:SE
-event_location (text)
-event_desc (varchar)

I need to get the event start date from the soonest event that has not passed for the front page but I need it in this format = 03/05/2013 at 12:00 AM.

Any ideas guys?

Upvotes: 2

Views: 176

Answers (1)

mcriecken
mcriecken

Reputation: 3297

You can use the CONCAT and DATE_FORMAT functions in order to manipulate the result as you want it. 

SELECT CONCAT(DATE_FORMAT(event_start, '%d/%m/%Y'), " at ", DATE_FORMAT(event_start, '%h:%m %p')), event_location, event_desc
FROM events1532
WHERE event_start > NOW()
ORDER BY event_start ASC
LIMIT 1;

MySQL Date Format Options

The NOW() function returns a datetime of the current time. You can test this in a MySQL query by simply doing:

SELECT NOW();

The rest is basically ordering the results in ascending order (meaning the soonest date that is greater than NOW() will be at the top of the results), and limiting the result to 1. You could of course limit it to more depending on how many you want returned.

Upvotes: 2

Related Questions