473,372 Members | 1,245 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Events order by startDate, EndDate - show single date events above date ranges

hodgeman
Hello thescripts forums users...

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
  1. SELECT startDate, endDate, name FROM `events` WHERE publish=1 AND expired=0 ORDER BY startDate
CURRENT RESULT - as of 2008-02-22 ====
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.
Feb 22 '08 #1
7 3456
ronverdonk
4,258 Expert 4TB
Concatenate the 2 dates and sort on it, e.g.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table ORDER BY CONCAT(startDate,endDate)
Ronald
Feb 22 '08 #2
Thanks Ronald,

But that still retrieves the same results in the same order. Just because the dates are joined together doesn't make them ORDER BY any differently.

UPDATED SQL ====================
================================
Expand|Select|Wrap|Line Numbers
  1. SELECT startDate,endDate,CONCAT(startDate,endDate) as dates,name FROM `events` WHERE publish=1 AND expired=0 ORDER BY dates
UPDATED RESULT (as of 25 Feb) ======
startDate - endDate - CONCAT(startDate,endDate) as dates - name
================================
2008-01-16 - 2008-03-02 - 2008-01-162008-03-02 - From Under the Southern Cross...
2008-01-16 - 2008-03-15 - 2008-01-162008-03-15 - Te Huringa -Turning Points: Pakeh...
2008-01-16 - 2008-03-30 - 2008-01-162008-03-30 - Nickel Plated Machines:Tubular...
2008-01-22 - 2008-04-04 - 2008-01-222008-04-04 - Women's Activator Serie...
2008-01-27 - 2008-05-25 - 2008-01-272008-05-25 - Rotorua Arts, Crafts and...
2008-02-27 - 0000-00-00 - 2008-02-270000-00-00 - Bike to Breakfast
2008-02-28 - 0000-00-00 - 2008-02-280000-00-00 - Rotorua Marae Line Da...
2008-02-29 - 0000-00-00 - 2008-02-290000-00-00 - Tykes on Trikes
2008-03-01 - 0000-00-00 - 2008-03-010000-00-00 - Karaoke Club Performa...
2008-03-01 - 0000-00-00 - 2008-03-010000-00-00 - Waikato Pistons vs Bay...
2008-03-01 - 0000-00-00 - 2008-03-010000-00-00 - Dutch Market Day
2008-03-01 - 0000-00-00 - 2008-03-010000-00-00 - Girl Guide biscuit sales
2008-03-01 - 0000-00-00 - 2008-03-010000-00-00 - More FM Triwoman Series
Feb 24 '08 #3
ronverdonk
4,258 Expert 4TB
But that still retrieves the same results in the same order. Just because the dates are joined together doesn't make them ORDER BY any differently.
I am very sorry. I completely misinterpreted your question.

Ronald
Feb 24 '08 #4
mwasif
802 Expert 512MB
Hi hodgeman,

Are you looking for something like below?
Expand|Select|Wrap|Line Numbers
  1. SELECT startDate, endDate, name , IF(startDate >= NOW(), 1, 0) start_date_criteria, 
  2. IF(endDate > '0000-00-00', 1, 0) end_date_criteria FROM events 
  3. WHERE publish=1 AND expired=0 
  4. ORDER BY start_date_criteria DESC, startDate, end_date_criteria DESC
Feb 25 '08 #5
Hi hodgeman,

Are you looking for something like below?
Expand|Select|Wrap|Line Numbers
  1. SELECT startDate, endDate, name , IF(startDate >= NOW(), 1, 0) start_date_criteria, 
  2. IF(endDate > '0000-00-00', 1, 0) end_date_criteria FROM events 
  3. WHERE publish=1 AND expired=0 
  4. ORDER BY start_date_criteria DESC, startDate, end_date_criteria DESC
Yeah, that's getting closer.
All events with an endDate!=0000-00-00 that have past their startDate are right at the bottom now instead of scattered through the results based on their endDate. It's a bit more usable now that they don't take up all the room at the top of the first page.

But is there anyway to tweak this query further to order just those results by endDate with startDates that have past? But still keeping them in the overall order of startDate.

Like.....

Make a tmp field:
if (startDate < NOW)
startDate
else if (endDate != 0000-00-00)
endDate

And order by that field - how could I do that? - would that work?
Feb 25 '08 #6
Finally got it solved...

Expand|Select|Wrap|Line Numbers
  1. SELECT startDate, endDate, IF(startDate >= NOW(), startDate, endDate) date_sort, name FROM events WHERE publish=1 AND expired=0 ORDER BY date_sort
RESULT based on original results (first post) ======
startDate - endDate - date_sort - name
=======================================
2008-02-22 - 0000-00-00 - 2008-02-22 - Hans Theessink
2008-02-22 - 0000-00-00 - 2008-02-22 - Searchlight Tattoo Street Parade
2008-02-19 - 2008-02-22 - 2008-02-22 - Learning@Schools
2008-02-23 - 2008-02-24 - 2008-02-24 - Searchlight Tattoo
2008-02-23 - 2008-02-24 - 2008-02-24 - NZO 24-hours N-Duro
2008-02-23 - 0000-00-00 - 2008-02-23 - Mayoral Bike Ride Challenge
2008-02-23 - 0000-00-00 - 2008-02-23 - Rotorua Community Hospice Garage...
2008-02-24 - 0000-00-00 - 2008-02-24 - Rotorua Tractor & Machinery Club Live...
2008-02-24 - 0000-00-00 - 2008-02-24 - Soundshell Market
2008-01-16 - 2008-03-15 - 2008-03-15 - Te Huringa -Turning Points: Pakeha ...
2008-01-16 - 2008-03-02 - 2008-03-02 - From Under the Southern Cross – An...
2008-01-16 - 2008-03-30 - 2008-03-30 - Nickel Plated Machines:Tubular Steel...
2008-01-22 - 2008-04-04 - 2008-04-04 - Women's Activator Series 2008
2008-01-27 - 2008-05-25 - 2008-05-25 - Rotorua Arts, Crafts and Produce Fair

Because all events that don't have an endDate set are automatically expired and unpublished once their startDate passes. I created the temp feild date_sort and set it's value to startDate if that was after NOW else set it to endDate, then ordered by that new field date_sort.

The result is exactly what I was after!
Thank you to all of you who helped and guided me in the right direction.

Results can be found at http://www.rotoruanz.com/events
Feb 25 '08 #7
ronverdonk
4,258 Expert 4TB
I am glad you solved this yourself. Hope to be of (some) assistance next time. See you.

Ronald
Feb 26 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Shabam | last post by:
I'm having a calendar feature developed whereby users can add recurring events. These recurring events can have start and end dates, meaning they will recur only within a date range. For...
4
by: Rob Johnson | last post by:
I have an ASP.Net calendar feature which allows users to add events and configure whether or not they repeat at various frequencies (i.e, daily, weekly, monthly, Sat/Sun, etc.). What I'm looking...
67
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ...
2
by: sd_eds | last post by:
I have a startdate-enddate dropdown in asp that works great on my development machine (XP Pro IIS5) but when I port it over to the web server (Win Server 2003, IIS6), I do not get the desired...
2
by: HoganGroup | last post by:
Hi fellows: I am at a complete lost as to how to construct a query or report to answer this question. Any help is greatly appreciated. The question I need to answer is this: Identify the SchID...
4
by: nologo | last post by:
all, I have a startdate and enddate, i wish to be able to calculate how many weeks have been selected. so for example, using the startdate and enddate, i select 11/02/2008 and 25/02/2008 i need a...
2
by: Mike P | last post by:
How do you take 2 dates (a startdate and an enddate), and then add all the dates between them to an ArrayList? This is as far as I have got so far, I'm not sure what to do next : DateTime...
31
by: freeflyer30339 | last post by:
In my Access '03 table I have three columns. P/N, StartDate,EndDate. Most of the time the start date and the end date are the same. Occasionally the end date can than a couple of days from the start...
1
by: swethak | last post by:
Hi, I am desiging the calendar application for that purpose i used the below code. But it is for only displys calendar. And also i want to add the events to calendar. In that code displys the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.