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

7 New Member

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

 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:Tubula r 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@School s
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>curr entDate), IF (endDate>curren tDate) ....
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@School s
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:Tubula r 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

Subscribe Reply

3491

ronverdonk

4,258

Recognized Expert Specialist

Concatenate the 2 dates and sort on it, e.g.

Expand|Select|Wrap|Line Numbers

SELECT * FROM table ORDER BY CONCAT(startDate,endDate)

Ronald

Feb 22 '08 #2

hodgeman

New Member

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

 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(startDat e,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:Tubula r...
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

Recognized Expert Specialist

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

Recognized Expert Contributor

Hi hodgeman,

Are you looking for something like below?

Expand|Select|Wrap|Line Numbers

 SELECT startDate, endDate, name , IF(startDate >= NOW(), 1, 0) start_date_criteria, 

IF(endDate > '0000-00-00', 1, 0) end_date_criteria FROM events 

WHERE publish=1 AND expired=0 

ORDER BY start_date_criteria DESC, startDate, end_date_criteria DESC

Feb 25 '08 #5

hodgeman

New Member

Hi hodgeman,

Are you looking for something like below?

Expand|Select|Wrap|Line Numbers

SELECT startDate, endDate, name , IF(startDate >= NOW(), 1, 0) start_date_criteria,

IF(endDate > '0000-00-00', 1, 0) end_date_criteria FROM events

WHERE publish=1 AND expired=0

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

hodgeman

New Member

Finally got it solved...

Expand|Select|Wrap|Line Numbers

 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@School s
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:Tubula r 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

Recognized Expert Specialist

I am glad you solved this yourself. Hope to be of (some) assistance next time. See you.

Ronald

Feb 26 '08 #8

Similar topics

3882

Calendar recurring events logic

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 instance, user A creates an event that recurs every Tuesday, from 1/1/05 - 3/2/06. The developer has decided that it would be good to have another table that stores each individual recurring event in a separate record. His logic is that this will help...

.NET Framework

6114

Calendar - Repeating Events

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 for is some C# code that will calculate a date of "Every Other Week" based on a starting date and ending date. Example: If a user enters an event dated October 3, 2005 and would like it to repeat every other week until October 31, 2005, the code...

C# / C Sharp

7682

Find Missing Date Ranges

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 4/30/06 I am looking for suggestions on how to find the date ranges where there were no transactions.

Microsoft Access / VBA

2088

Startdate - Enddate dropdown not working ASP, IIS6

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 results. What should happen is that the start-end range should show the current week. Instead, the start-end range defaults to 2 months earlier, when the page was deployed. I have added some code below: <% dim begindate, enddate, startdate %>

ASP / Active Server Pages

1563

Query which groups months between startdate and enddate

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 and month for each SchID/Month combo for which there is not at least one associated AttendanceDate between that SchID's StartDate and EndDate. Table structure: tblSchedules ------------------ SchID StartDate

Microsoft Access / VBA

1943

startdate / enddate sum

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 method to sum how many weeks that is. Any suggestions?Where do i start? Cheers!

C# / C Sharp

2403

Add all dates between startdate and enddate to arraylist

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 dtmStartDate = Convert.ToDateTime(dr); DateTime dtmEndDate = Convert.ToDateTime(dr); TimeSpan ts = dtmEndDate - dtmStartDate;

C# / C Sharp

2685

Count number of events daily within a date range.....

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 date. I am trying to chart my workload for each day. So if a part is in the shop for 4 days, it gets counted for all 4 days. P/N StartDate EndDate 1 1/1/08 1/1/08 2 ...

Microsoft Access / VBA

4895

Adding Events to Calendar

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 events when click on that date that perticular event displyed in a text box.But my requirement is to when click on that date that related event displyed in same td row not the text box. and also i add the events to that calendar.plz advice how to...

Javascript

8444

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8869

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8781

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8551

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8639

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7386

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6198

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

4198

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2011

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP