?
Solved

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

Posted on 2008-10-17
10
Medium Priority
?
914 Views
Last Modified: 2013-12-13
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
Comment
Question by:patrickHefner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 4

Expert Comment

by:Oneiroid
ID: 22746940
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
 
LVL 4

Expert Comment

by:Oneiroid
ID: 22746961
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
 
LVL 4

Expert Comment

by:Oneiroid
ID: 22746992
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 4

Expert Comment

by:Oneiroid
ID: 22746998
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
 

Author Comment

by:patrickHefner
ID: 22747139
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
 

Author Comment

by:patrickHefner
ID: 22747168
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
 
LVL 5

Expert Comment

by:RightNL
ID: 22747507
try it..
 
0
 

Expert Comment

by:meoconx
ID: 22747638
the max function here is for finding the maximum date of 3 closest date witch the current date

Open in new window

0
 
LVL 4

Accepted Solution

by:
Oneiroid earned 1000 total points
ID: 22750264
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
 

Author Closing Comment

by:patrickHefner
ID: 31507364
Thank you again for this query, this is exactly what I have been looking to do for quite sometime now!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question