MySQL Near date range

[bigheadedd]

Hi,

 

I've been trying to sort out some issues I've been having with a query to fetch dates from an events based web app I've done.

Currently, the query looks like this..

$listresult = mysql_query("SELECT * FROM events WHERE  (startdate >= NOW() OR enddate >= NOW()) ORDER BY events.startdate ASC LIMIT $amount")or die(mysql_error());

This works as it should do, by picking up events that are currently on. However, if there is an event that starts from 1st January 2013 and doesn't end until 1st October 2014 (Next year!), it places itself at the beginning of the list regardless.

 

What i'm trying to achieve is where the query orders them by the NOW() time, by bringing the nearest (starting or ending) to the top of the list, and the others further down.

 

Is this possible at all? I've been trying to rack my brains, but nothing has come up so far.

 

Any help would be great.

Thanks,

E

 

[Barand]

 

 

This works as it should do, by picking up events that are currently on.

 

I am surprised at that

 

 

 

WHERE (startdate >= NOW()

 

that will pick up future events. For current events you need

 

WHERE startdate <= NOW() AND enddate >= NOW()

 

Perhaps you need to sort by enddate

[bigheadedd]

Thats a good point. Apologies, that is actually what my code should be; I'd copied one of the other queries!

Sorting by enddate does the trick perfectly. It seems dumbly obvious now, though thanks for the help!