I've found a lot of answers to problems I've had in the past from this forum, and this is the first mySQL query issue I haven't been able to find an answer from anywhere so thought my time first post should be here.
With that out the way...
I have a website I've built for my day job rotoruaNZ.com/events that I'd like to improve upon.
We have numerous events posted on our website that take place around the region and I've come across an issue that wasn't considered while development was taking place.
All events have a compulsory start date field set and an optional end date for multiple consecutive day events.
Problem comes around when I display the events ordered by start date.
We are getting some long running exhibitions at our local museum and they run for months on end. Therefore they are staying at the top of the list until they expire or the end date has past.
Current sql query (basically)
Expand|Select|Wrap|Line Numbers
- SELECT startDate, endDate, name FROM `events` WHERE publish=1 AND expired=0 ORDER BY startDate
startDate - endDate - name
================================
2008-01-16 - 2008-03-15 - Te Huringa -Turning Points: Pakeha Colonisation & ...
2008-01-16 - 2008-03-02 - From Under the Southern Cross – An Exhibition of...
2008-01-16 - 2008-03-30 - Nickel Plated Machines:Tubular Steel Furniture in ...
2008-01-22 - 2008-04-04 - Women's Activator Series 2008
2008-01-27 - 2008-05-25 - Rotorua Arts, Crafts and Produce Fair
2008-02-19 - 2008-02-22 - Learning@Schools
2008-02-22 - 0000-00-00 - Hans Theessink
2008-02-22 - 0000-00-00 - Searchlight Tattoo Street Parade
2008-02-23 - 2008-02-24 - Searchlight Tattoo
2008-02-23 - 2008-02-24 - NZO 24-hours N-Duro
2008-02-23 - 0000-00-00 - Mayoral Bike Ride Challenge
2008-02-23 - 0000-00-00 - Rotorua Community Hospice Garage Sale
2008-02-24 - 0000-00-00 - Rotorua Tractor & Machinery Club Live Day
2008-02-24 - 0000-00-00 - Soundshell Market
What I'd like returned is any single day events returned from the current date onwards before events that have a endDate set even if there startDate is prior to the current date
So something like:
ORDER BY IF (startDate>currentDate), IF (endDate>currentDate) ....
this is where my brain starts melting....lol
Not sure how to structure it, this seams to be one of the most complex queries I've ever had to construct.
But this is what we would want returned based on the same rows as above, ideally...
IDEAL RESULT for 2008-02-22==========
startDate - endDate - name
================================
2008-02-22 - 0000-00-00 - Hans Theessink
2008-02-22 - 0000-00-00 - Searchlight Tattoo Street Parade
2008-02-19 - 2008-02-22 - Learning@Schools
2008-02-23 - 2008-02-24 - Searchlight Tattoo
2008-02-23 - 2008-02-24 - NZO 24-hours N-Duro
2008-02-23 - 0000-00-00 - Mayoral Bike Ride Challenge
2008-02-23 - 0000-00-00 - Rotorua Community Hospice Garage Sale
2008-02-24 - 0000-00-00 - Rotorua Tractor & Machinery Club Live Day
2008-02-24 - 0000-00-00 - Soundshell Market
2008-01-16 - 2008-03-15 - Te Huringa -Turning Points: Pakeha Colonisation & ...
2008-01-16 - 2008-03-02 - From Under the Southern Cross – An Exhibition of...
2008-01-16 - 2008-03-30 - Nickel Plated Machines:Tubular Steel Furniture in ...
2008-01-22 - 2008-04-04 - Women's Activator Series 2008
2008-01-27 - 2008-05-25 - Rotorua Arts, Crafts and Produce Fair
I've been scratching my head for weeks trying to figure this out in between other projects.
So any help with nutting this one out would be amazingly appreciated.