CODEWITHSUNDEEP
mysql
Nicero
Nicero

Reputation: 4377

MySQL. Mix queries and display result only once

My web site publishes the events (music, theatre, exibitions...) that happen in my town (Italy). All the events have a start date and a end date. (Events that lasts only one day will have the same date).

All events may be displayed like this:

SELECT * FROM events WHERE end_date >= '".date("Y-m-d")."' ORDER BY end_date

The above statement will correctly list all the current events starting from today.

But there are some events that may happen at a certain point in the future and last more that one day (from-to event) for example:

start_date = 2014-08-12 and end_date = 2014-08-23

An event with the above dates will be listed at the end because the MySQL query says:

ORDER BY end_date

It is possible to query MySQL so that it lists all the current events AND all those 'from-to' events which have in their date range a date equal to today ONLY ONCE?

For example, let's say that today = 2014-08-06 and there are a number of events that happen the same day:

Event 1: start_date=2014-08-06 / end_date=2014-08-06
Event 2: start_date=2014-08-07 / end_date=2014-08-07
Event 3: start_date=2014-08-08 / end_date=2014-08-08
Event 4: start_date=2014-08-09 / end_date=2014-08-09

and there's an other event with the following dates range:

Event 5: start_date=2014-08-01 / end_date=2014-08-20

which dates include the dates of events 1-4

The result expected should be:

Event 5 - Event 1 - Event 2 - Event 3 - Event 4

Event 5 is listed at the first place because it's date range includes the same date of Event 1, BUT must be listed only ONCE to avoid to be repeated every time all the other events (2-4) occur.

This must be avoided: Event 5 - Event 1 - Event 5 - Event 2 - Event 5 - Event 3 - Event 5 - Event 1 - Event 5.

Upvotes: 0

Views: 63

Answers (2)

Manish Verma
Manish Verma

Reputation: 781

May be this will work.

SELECT * FROM events WHERE "date("%Y-%m-%d")"  BETWEEN start_date AND end_date

Upvotes: 0

Andrzej Reduta
Andrzej Reduta

Reputation: 767

SELECT * FROM events WHERE end_date >= '".date("Y-m-d")."' ORDER BY end_date

The above statement will correctly list all the current events starting from today.

No. This will return all events finishing from today. Some of them began in the past.

Upvotes: 0

Related Questions