CODEWITHSUNDEEP
phpmysqldate
VORiAND
VORiAND

Reputation: 175

How to make this time window query?

I have a MySQL table with the following columns:

id, month, day, remind_days_before

It's for a simple recurring reminder function. The user can enter for example: "I have a very important thing on the 5. April every year, please remind be 15 days before that". There could be tens of thousands of entries in this table. Using this table I want to run a cron_job every morning, which is sending out this reminders in form of email messages. The problem is I don't know how to write this kind of SQL query... Is it even possible? I want to query only those rows where the "month" and "day" as DATE is between TODAY and TODAY+31 days (this is the maximum number for remind_days_before).

I'm trying with this right now, but the $end_month is giving me the same as the $today_month:

$today_month = date('m');

$end_month = date('m', strtotime('+31 days', $today_month));

What happens when the +31 days DATE is in the next YEAR or we have leap year?!

Can someone help me out here?

Thank you very much.

Upvotes: 1

Views: 271

Answers (2)

Oscar Zarrus
Oscar Zarrus

Reputation: 790

First of all, your fields must be a no-mysql function. So replace month/day with planning_month/planning_day, because the month and day words, are the functions of MySQL, and you may have errors if they are not quoted

Here's a working query. I've tested and works great:

select * from MyTable 
/*CHECK CURRENT YEAR*/
where date(concat(year(now()), planning_month, planning_day)) = 
date(date_add(now(), interval remind_days_before DAY))
OR
/*CHECK NEXT YEAR*/
date(concat(year(date_add(now(), INTERVAL 1 YEAR)), planning_month, planning_day)) = 
date(date_add(now(), interval remind_days_before DAY))

Live Example

Your, it is a very useful question for me too

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31832

Just check for both years:

select *
from your_table
cross join (select year(now()) as current_year, year(now())+1 as next_year) vars
where curdate() + interval remind_days_before day = concat(current_year,'-',month,'-',day)
   or curdate() + interval remind_days_before day = concat(   next_year,'-',month,'-',day)

Upvotes: 0

Related Questions