Send alerts based on mysql date

[ampsman]

How would I go about creating a script to check a date in mysql and if that date is within X days it will send an email alert.

 

[ampsman]

I found this, http://stackoverflow.com/questions/1393516/how-to-compare-a-date-from-mysql-with-current-date-and-send-a-notification-email.

I have been playing around with it but havent had any luck.

Has anyone done something similar before?

[ampsman]

anybody?

[gizmola]

Yes, I have done things like this many times.  Typically you write the php script that you call from a cron job which runs it on a set schedule, using command line php:

 

php -f checkfornotify.php

 

I have written several articles on using the various date columns along with builtins to determine date ranges offset from right now:

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

http://www.gizmola.com/blog/archives/99-Finding-Next-Monday-using-MySQL-Dates.html

 

 

What is your specific question?

[ampsman]

thank you.

specifically I simply want to check if any dates in column X are within X days of todays date.

If yes then email the results.

[ampsman]

so I think I am getting closer.

something like this will find any records that have a date 3 days away.

 

$result = 'SELECT * FROM emp_info WHERE StartDate = DATE_ADD(curdate(), INTERVAL 3 DAY);'

 

 

[gizmola]

so I think I am getting closer.

something like this will find any records that have a date 3 days away.

 

$result = 'SELECT * FROM emp_info WHERE StartDate = DATE_ADD(curdate(), INTERVAL 3 DAY);'

 

Yes something like that will work if StartDate is a DATE column.  If it's a DATETIME, you have to be concerned with the time component -- if not looks like you're on your way.

 

 

[ampsman]

woohoo!!!

got it working!

Here is the final code for anyone with the same question.

<?php
$username = "username";
$password = "password";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
or die("Unable to connect to MySQL");

//select a database to work with
$selected = mysql_select_db("db_name",$dbhandle) 
  or die("Could not select examples");

//execute the SQL query and return records
$result = mysql_query("SELECT * FROM table WHERE date_field = DATE_ADD(curdate(), INTERVAL 3 DAY)");

//fetch tha data from the database 
while ($row = mysql_fetch_array($result)) {
   mail("xxxx@xxx.com", "Subject", "body");
}
//close the connection
mysql_close($dbhandle);
?>