• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 923
  • Last Modified:

mySQL query to Select entries closest to current day starting with future entries (upcoming events)

I'm trying to implement a sql query that will retrieve 3 rows closest to the current date. Ideally I would also be pulling an entry with a date greater than or equal to today, however, this isn't always the case. If three entries are not found with current or future dates I need to pull the remaining entries in DESC order from the current date.

A clarification example:  I am displaying three calendar events on a homepage and I want there to always be three events. I run a query to get all upcoming events WHERE DATE(date_evnt)>=CURDATE() ORDER BY date_evnt ASC. If there is only one upcoming event I need to grab two events WHERE DATE(date_evnt)<= CURDATE() ORDER BY date_evnt DESC.

Is it possible that this can be done with a single query or do I have to use a loop?
$query_rs_event = "SELECT * FROM event_evnt WHERE DATE(date_evnt)>=CURDATE() ORDER BY date_evnt ASC LIMIT 3";

Open in new window

0
patrickHefner
Asked:
patrickHefner
1 Solution
 
OneiroidCommented:
Without knowing the way you are storing you date, why don't you just try:

$query_rs_event = "SELECT * FROM event_evnt ORDER BY date_evnt ASC LIMIT 3";
 

Then you can apply conditions to the results in your code later on if need be.
0
 
OneiroidCommented:
Ahh... I think you are asking for three results which, if there were 5 future events, would include the first three events closest to the current date. And if there was only one future event, include two of the most recently passed events.

I'll keep scheming. Immediately, I think it might be easiest to read in your event records then analyze them in your code... but there's got to be a better way...
0
 
OneiroidCommented:
Maybe something along this line...
SELECT * 
FROM event_evnt 
WHERE DATE(date_evnt) <= 
  (SELECT max(DATE(date_evnt)) 
   FROM event_evnt
   WHERE DATE(date_evnt)>=CURDATE() 
   ORDER BY date_evnt ASC
   LIMIT 3)
ORDER BY date_evnt DESC

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
OneiroidCommented:
Missed pasting in the last line:
SELECT * 
FROM event_evnt 
WHERE DATE(date_evnt) <= 
  (SELECT max(DATE(date_evnt)) 
   FROM event_evnt
   WHERE DATE(date_evnt)>=CURDATE() 
   ORDER BY date_evnt ASC
   LIMIT 3)
ORDER BY date_evnt DESC
LIMIT 3

Open in new window

0
 
patrickHefnerAuthor Commented:
Nice Work! This query is almost perfect, however, if there are not future events then no results are returned. Adding an OR statement before the subquery seems to work, does this look correct?
SELECT * 
FROM event_evnt 
WHERE DATE(date_evnt) <= CURDATE() ||
  (SELECT max(DATE(date_evnt)) 
   FROM event_evnt
   WHERE DATE(date_evnt)>=CURDATE() 
   ORDER BY date_evnt ASC
   LIMIT 3)
ORDER BY date_evnt DESC
LIMIT 3

Open in new window

0
 
patrickHefnerAuthor Commented:
A quick question, what is the purpose of using max() here? If we want to return events that are closest to the current date (ie an event tomorrow would be listed first) would min() be the right function to use here?
0
 
RightNLCommented:
try it..
 
0
 
meoconxCommented:
the max function here is for finding the maximum date of 3 closest date witch the current date

Open in new window

0
 
OneiroidCommented:
It would seem that IF/ELSE statements are only allowed in MySQL functions and stored procedures. But this should do the trick:
SELECT * 
FROM event_evnt 
WHERE DATE(date_evnt) 
BETWEEN
  (SELECT DISTINCT min(DATE(date_evnt)) 
   FROM event_evnt)
  AND 
  (SELECT max(DATE(date_evnt)) 
   FROM event_evnt
   WHERE DATE(date_evnt)>=CURDATE() 
   ORDER BY date_evnt ASC
   LIMIT 3)
ORDER BY date_evnt DESC
LIMIT 3

Open in new window

0
 
patrickHefnerAuthor Commented:
Thank you again for this query, this is exactly what I have been looking to do for quite sometime now!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now