CODEWITHSUNDEEP
mysqlsql
flukyspore
flukyspore

Reputation: 696

check if date exists BEFORE and AFTER a given date in ONE query

This is a Mysql/sql query question but allow me to explain the context: I would like to equip my PHP calendar with a '< PREV' and 'NEXT >' button allowing a user to easily go to the previous or next available month that has DATABASE ENTRIES AVAILABLE. If there are no database entries available, I want to disable the button (I never want the system to present an empty calendar).

Example: User has FEB in view. JAN and MAR do not contain entries but DEC and APR do. The 'NEXT >' button then needs to switch to the April calendar. The '< PREV' button needs to switch to the December calendar.

So basically I'm looking for a single efficient query that can check if entries exist prior to and after (but not part of) the month in view.

The table has a field 'logDate' which is of type date 'Y-m-d'.

I started fumbling with the following query to check first whether or not a date exists before and after the given date but I understand that there are some things wrong with this query. For one, if there's an entry in the table for '2013-03-19' my query will spit out a 1 for prev - which of course doesn't indicate if an entry exists for months prior to March.

SELECT IF( EXISTS(SELECT dayId FROM LogbookDay 
WHERE logDate < '2013-03-20' AND userId = 2), 1, 0) as prev, 
IF( EXISTS(SELECT dayId FROM LogBookDay WHERE logDate > '2013-03-20' 
AND userId = 2), 1, 0) as next

Upvotes: 1

Views: 6125

Answers (3)

Dave
Dave

Reputation: 4414

Modify the start date to the beginning and end of the month: For prev, you modify to the first day of the month, by subtracting 1 less than the current day of the month. For next, you do the same, then add 1 month:

SELECT 
(SELECT dayId FROM LogbookDay WHERE 
    logDate < ADDDDATE('2013-03-20', 1-DAY('2013-03-20')) AND userId = 2
    ORDER BY logDate DESC LIMIT 1
) as PREV,
(SELECT dayId FROM LogBookDay WHERE 
    logDate >= ADDDATE(ADDDDATE('2013-03-20', 1-DAY('2013-03-20')), INTERVAL 1 MONTH) AND userId = 2
    ORDER BY logDate ASC LIMIT 1
) as NEXT

(Or, do this in PHP, as it's likely to result in more readable code.)

Display the link differently depending on if the result is a null or a date.

It might be more elegant if you were pulling a date instead of an ID:

    SELECT 
(SELECT MAX(logDate) FROM LogbookDay WHERE 
    logDate < ADDDDATE('2013-03-20', 1-DAY('2013-03-20')) AND userId = 2
) as PREV,
(SELECT MIN(logDate) FROM LogBookDay WHERE 
    logDate >= ADDDATE(ADDDDATE('2013-03-20', 1-DAY('2013-03-20')), INTERVAL 1 MONTH) AND userId = 2
) as NEXT

But that depends on exactly what you're doing with the information you find. If all you're doing is creating a link to a new month, pulling the nearest date not in the same month should do the trick.

Upvotes: 4

flukyspore
flukyspore

Reputation: 696

With my own query and a pointer from one of the posted answers I have been able to come up with a simple query that checks whether or not entries exist with months prior and after to the month currently in view.

Assuming month in view is March 2013:

SELECT IF( EXISTS(SELECT dayId FROM LogBookDay 
WHERE logDate < '2012-03-01' AND userId = 1), 1, 0) as prev,
IF( EXISTS(SELECT dayId FROM LogBookDay 
WHERE logDate > CONCAT('2012-03-',LAST_DAY('2003-02-05')) 
AND userId = 1), 1, 0) as next

Upvotes: 1

georgecj11
georgecj11

Reputation: 1637

SELECT 
if(min(logDate) < '2013-03-20', 1, 0) 'NEXT',
if(max(logDate) > '2013-03-20', 1, 0) 'PAST'
FROM LogbookDay 
WHERE logDate < '2013-03-20' AND logDate > '2013-03-02' 
AND  userId = 2

Upvotes: 0

Related Questions