PHP5 e-mail reminder when date field is one month before due date

Question

I'm trying to set up an e-mail notification as a reminder to let me know when a vehicle inspection is due, preferably a month in advance. When a date is one month prior to the date in the due_date inspection table, it should send the reminder. Your help would be greatly appreciated. Below is the php code and MySQL schema I have so far:

<?php
//calling PEAR Mailer
require_once "Mail.php";
?>
<?php function connect()
{
  require('includes/config.php');
  return $conn;
}
?>
<?php
// Make a MySQL query
$query = "SELECT * FROM inspection";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
$from = "Server Database <[email protected]>";
$to = "me <[email protected]>";
//$cc = "another person <[email protected]>";
$subject = "Vehicle Inspection Reminder";
$body = "echo "The following vehicle is due for inspection:;
echo $row['vehicle'];
if (!$conn)
  {
  die('Could not connect: ' . mysql_error());
  }

?>";

$host = "mail.server.com";
$username = "username";
$password = "password";

$headers = array ('From' => $from,
  'To' => $to,
  'CC' => $cc,
  'Subject' => $subject);
$smtp = Mail::factory('smtp',
  array ('host' => $host,
    'auth' => true,
    'username' => $username,
    'password' => $password));

$mail = $smtp->send($to, $headers, $body);

if (PEAR::isError($mail)) {
  echo("<p>" . $mail->getMessage() . "</p>");
 } else {
  echo("<p>Message successfully sent!</p>");
 }
?>

MySQL schema:

`CREATE TABLE IF NOT EXISTS `inspection` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `vehicle` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `last_date` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `due_date` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `vehicle` (`vehicle`,`last_date`,`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;`

Answer 1

You're on the correct path. The only important thing left to do is to create cron job for your PHP script to run it once everyday. Your script will have to check if the record is outdated and don't forget to modify your schema to contain the boolean field "NotificationSent" to avoid sending mail notification everyday. More on cron jobs: http://www.developertutorials.com/blog/php/running-php-cron-jobs-regular-scheduled-tasks-in-php-172/ A script draft:

$request = "SELECT due_date, NotificationSent FROM inspection WHERE due_date>$expected_date AND (NOT NotificationSent)";
$res = mysql_query($request);
while ($somerow = mysql_fetch_assoc($request))
{
    // ... here you call your mail notification script and set NotificationSent to true for the row
}