CODEWITHSUNDEEP
phpmysql
user2528959
user2528959

Reputation:

how to select rows from a database in php where the date field is 7 days in the future

I have an automatic checker that checks for domains that are going to expire within the next 7 days and it sends and email to the customer.

Im using this SQL Query:

 $sql="SELECT * from domain_names where (status = '' or status = 'valid') and date(expiry_date) = date(now() + interval 7 day) ";

it works fine but if it stops running and a day goes past it wont do the domains it has missed. whats the best way to make it do the domains its missed too?

for example if a domain expires on 7th and it runs on the 1st it will see that domain and email the customer then put a flag on the row to say the email has already been sent but if the checker it not running on the 1st and it starts running again on the 2nd it wont see that domain (row)

Upvotes: 7

Views: 1691

Answers (13)

Conor Ryan
Conor Ryan

Reputation: 169

have you considered adding a log of when yo last checked your expiry records and then using that date as your variable so you can check from that last date each time your code runs?

Upvotes: 0

Phoenix
Phoenix

Reputation: 1528

You can use this code.

$sql = SELECT * from domain_names where (status = '' or status = 'valid') and date(expiry_date) = DATE_ADD(date(now()), INTERVAL 7 DAY);

It may help you...

Upvotes: 0

Shivdhwaj Pandey
Shivdhwaj Pandey

Reputation: 157

Just a little modification in the code of yours(Putting '<'):

$sql="SELECT * from domain_names where (status = '' or status = 'valid') and date(expiry_date) <= date(now() + interval 7 day) ";

Upvotes: 1

user3762306
user3762306

Reputation: 9

Or you could set it up in php using a variable in place of the current date plus 7 days such as;

$nextWeek=date("Ymd")+7;
$sql="SELECT * from domain_names where (status = '' or status = 'valid') 
and date(expiry_date) <= $nextWeek) ";

whatever date format you use , you'll need to swap "Ymd" if different, which is 20140621 as I write this would translate to 20140628.

lower case m and lower case d allow for two digit days and months "06" or "01". upper case I believe will change it to single digits....2014621 and you can change the order and put "/" or "-" as in:

date("M-D-Y") +7 = 6-28-2014

date("d/M/Y")+7 = 28/6/2014

Upvotes: 0

Jitendra Prajapati
Jitendra Prajapati

Reputation: 101

Try with this query. It will use full for you..

$sql="SELECT * from domain_names where (status = '' or status = 'valid') and DATE_ADD( expiry_date, INTERVAL 7 DAY )";

Upvotes: 0

Gary Hayes
Gary Hayes

Reputation: 1788

Set up a CRON event on the server side, if your web host provides it. Then you can have it run as often as you want, without having to worry about power outages or internet outages.

Upvotes: 0

Anil Gupta
Anil Gupta

Reputation: 632

This should give the result as you need:

DATE_SUB(CURDATE(),INTERVAL 7 DAY) = expiry_date;
You can refer to the link below for more details:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 0

Micah
Micah

Reputation: 313

Add a processed date to the record. This is two fold, it gives you the processed flag, but it also gives you a historical reference for when the notification was sent.

In your query, add a condition that says processed_date is null, and the expiry_date is less than or equal to today plus 7 days.

(pseudo) where ...  and processed_date is null and expiry_date <= today + 7

When you're looking for records after a missed

Upvotes: 0

Alex
Alex

Reputation: 323

Maybe you need some date interval like:

$sql="SELECT * from domain_names where (status = '' or status = 'valid') and date(expiry_date) between date(now()) and date(now() + interval 7 day)";

Upvotes: 0

AlexP
AlexP

Reputation: 9857

Essentially, you need to send the mail if its within the 7 day period OR has an incomplete flag.

I would however change the column from a string to an integer as it really only has two values sent or not sent i.e 1 or 0.

SELECT 
  * 
FROM 
  domain_names 
WHERE 
  status = 0 
OR 
  expiry_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)

In this case, providing you update the status field to 1 you will always be able to send next time when the script fails.

Upvotes: 0

Abhishek Kannan
Abhishek Kannan

Reputation: 988

Since you have a flag to indicate if an email has been sent. Simply Check flag status for all domains that are going to expire within the next 7 days.

And your query returns only the domains expiring a week from present. Modify it to return domains expiring within the next 7 days.

Upvotes: 0

Rob W
Rob W

Reputation: 9142

You want to check where DATE is LESS THAN OR EQUAL TO (today + 7 days).

You should also have some sort of a "flag" in the table, such as reminderSent - so you don't send multiple reminders every time the query/script is ran.

$sql = "SELECT * from domain_names WHERE
  (status = '' or status = 'valid') AND 
  date(expiry_date) <= date(now() + interval 7 day) AND
  AND !reminderSent";

Upvotes: 0

Styphon
Styphon

Reputation: 10447

You are only checking for days exactly 7 days ahead. You want to check for up to 7 days ahead. Change your code to:

$sql="SELECT * from domain_names where (status = '' or status = 'valid') and date(expiry_date) <= date(now() + interval 7 day) ";

Since you have a flag any that have already been emailed within this period wont get e-mailed again.

Upvotes: 0

Related Questions