Member Avatar for altarek

I have database entrys that have a date column ( mySQL date type )
in this format(2011-5-30). I want to compare that date with the current server date and with 3 days before that day, to automatically send an email to a specified address.

Edited by altarek because: n/a
  • 2 Contributors
  • 3 Replies
  • 190 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by karthik_ppts

Dani AI

Generated

Good start from and a useful hint from — here’s a clear, safe workflow you can drop into a daily cron job to 1) detect rows that need a reminder (3 days before the stored DATE and on the date itself), 2) send the email, and 3) mark the row so you don’t resend the same notice.

A minimal, practical PHP + PDO example (adjust table/column names and DB credentials):

<?php
date_default_timezone_set('UTC'); // set to your server/app timezone

$today  = (new DateTime())->format('Y-m-d');
$plus3  = (new DateTime('+3 days'))->format('Y-m-d');

$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4','dbuser','dbpass',[
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('SELECT id,email,event_date,reminded_3,reminded_day FROM events WHERE event_date IN (?, ?)');
$stmt->execute([$today, $plus3]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if ($row['event_date'] === $plus3 && !$row['reminded_3']) {
        $subject = 'Reminder: event in 3 days';
        $body = "Your event on {$row['event_date']} is in 3 days.";
        mail($row['email'], $subject, $body, "From: no-reply@example.com\r\nContent-Type: text/plain; charset=UTF-8\r\n");
        $pdo->prepare('UPDATE events SET reminded_3 = 1 WHERE id = ?')->execute([$row['id']]);
    } elseif ($row['event_date'] === $today && !$row['reminded_day']) {
        $subject = 'Reminder: event is today';
        $body = "Your event is today ({$row['event_date']}).";
        mail($row['email'], $subject, $body, "From: no-reply@example.com\r\nContent-Type: text/plain; charset=UTF-8\r\n");
        $pdo->prepare('UPDATE events SET reminded_day = 1 WHERE id = ?')->execute([$row['id']]);
    }
}

Run the script from cron (example):

5 0 * * * /usr/bin/php /path/to/reminder.php >/dev/null 2>&1

Notes and troubleshooting: make sure server PHP timezone matches your expectations (or set it explicitly), add boolean flags/columns so reminders aren’t sent repeatedly, index the date column for performance, and log mail() results. For production, prefer an SMTP library (PHPMailer, etc.) for better deliverability and error handling.

Recommended Answers

All 3 Replies

Member Avatar for karthik_ppts

I have database entrys that have a date column ( mySQL date type )
in this format(2011-5-30). I want to compare that date with the current server date and with 3 days before that day, to automatically send an email to a specified address.

select * from tbl_name where date_field_name=DATE_ADD(NOW(), INTERVAL 3 DAY)

Get all the mail ids from result set and send mails

Member Avatar for altarek

sorry I don't know how to compare dates then send email

Member Avatar for karthik_ppts

sorry I don't know how to compare dates then send email

Post your code with code tags

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.