How to compare a date from mysql with current date and send a notification email

Question

I have database entrys that have a date column ( mySQL date type ). I want to compare that date with the current server date and with 3 days before that day, every month, to automatically send an email to a specified address.

Answer 1

Well this is just a mock but you could try something like this:

$localTime = `date +"%Y/%m/%d %H:%M:%S"`

// Query MySQL to get datetime

// compare the two times and send email

Answer 2

You can either make PHP or MySQL do the dirty work for you, either a script or a SELECT statement.

<?php
// We shall assume that you have a array called $send_date with values in the format YYYY-MM-DD (MySQL Date Format).
// These values will be the values in the date column, and this script will send the email 3 days before those dates.

$today = date("U");
// Work out, in seconds, the amount of time beforehand you would like to send the email.
$warning = 60 * 60 * 24 * 3;
foreach($send_date as $timestamp)
 {
  $sd = strtotime($timestamp);
  // If $sd is not an integer, it is not a valid timestamp. Move onto the next.
  if(!is_int($timestamp)) continue;
  $diff = $timestamp - $today;
  if($diff > 0 && $diff <= $warning)
   {
    function_to_send_your_email($row_id_of_this_particular_date);
   }
 }

Or a SELECT statement:

$warning = 60 * 60 * 24 * 3;
$today = date("U");
$sql = 'SELECT * FROM your_table WHERE TIMESTAMP(date_column) BETWEEN '.$today.' AND '.$today + $warning.';';
// Perform query, and act on results brought back.

The only problem with this script, and PHP in general, is that it will only check the three days (or the value stated in $warning) in advance only when the script is run. If you want it to keep checking I suggest you set up a CRON job. And I can't garantee that either of these will work as I haven't checked them.

Hope this gives you some inspiration on the matter!

Answer 3

you can also use the simpler MySQL TO_DAYS() function :

SELECT email FROM table WHERE TO_DAYS(date)=To_DAYS(NOW())-3;