Thursday, August 13, 2015

MySQL: Query by Month & Day in PHP — Find Today's Birthdays and Anniversaries

While we were working on a web portal, one of the use cases was to send greeting emails to members on their birthday and anniversaries.

Problem

We needed to get all email IDs from the database whose birthday or anniversary is today. The script runs from a cron job every morning at 9:00. Birth and anniversary dates in the database are stored in YYYY-MM-DD format. If you compare the full date with today (including year) you won't get matches because the years differ.

Simple idea

Query only by month and day. First, get today's month and day in PHP (without the year), then match the date column in the database by month-day.

Original code (as published)

The original article used this approach. (Note: this preserves the author's original example.)

//Setting the time zone
 date_default_timezone_set("Asia/Kolkata");

//Get today's date without year
 $today=date('m-d');
 echo  $today;

//add "%-" to the today's date in the place of year
 $today1= "%-".$today;
 echo $today1;

//Now write the SQL query using LIKE statement

//Note members is the table and dob&anniversary are cloumns.

$query_res="SELECT fname,email FROM `members` WHERE `dob` LIKE '$today1' or `anniversary` LIKE '$today1'";
$result_res=mysql_query($query_res);


while($row_res=mysql_fetch_array($result_res))
{
//code to send the email
}

Why this works

Dates stored as 'YYYY-MM-DD' will end with '-MM-DD'. By prepending '%-' to the month-day string you match the tail of the date string. Example: for today = '08-21', '%-08-21' matches '1987-08-21' or '2010-08-21'.

Important notes and improvements (do this instead)

  1. Deprecated API: The original code uses the old mysql_* API. These functions are removed in modern PHP versions. Use mysqli or PDO with prepared statements.
  2. Performance: Using a function or LIKE on a date column prevents MySQL from using an index. On large tables this can become slow. Consider storing a computed month-day column (or separate month and day columns) that you can index, or use a generated (virtual/stored) column in MySQL 5.7+.
  3. Correct SQL alternatives: Rather than string matching with LIKE, use DATE_FORMAT or the MONTH()/DAY() functions for clarity.
  4. Edge cases: Decide how to handle Feb 29 birthdays on non-leap years (send on Feb 28 or skip). Also confirm server timezone vs. PHP timezone.
  5. Security: Even though this query comes from server-side logic, always prefer prepared statements to avoid injection risks if you later introduce user input.

Recommended SQL approaches

Option A — DATE_FORMAT (clear and direct):

SELECT fname, email
FROM members
WHERE DATE_FORMAT(dob, '%m-%d') = '08-21'
   OR DATE_FORMAT(anniversary, '%m-%d') = '08-21';

Option B — MONTH() and DAY():

SELECT fname, email
FROM members
WHERE MONTH(dob) = 8 AND DAY(dob) = 21
   OR MONTH(anniversary) = 8 AND DAY(anniversary) = 21;

Both of these use functions on the column (so they will not use a plain index on the date column). To improve performance, add a computed/generated column or store month/day separately and index that column.

Modern, secure PHP example using PDO and DATE_FORMAT

// Set timezone
date_default_timezone_set('Asia/Kolkata');

// Get today's month-day
$md = date('m-d'); // e.g. '08-21'

// Using PDO and a parameterized query with DATE_FORMAT
$dsn = 'mysql:host=DB_HOST;dbname=DB_NAME;charset=utf8mb4';
$user = 'DB_USER';
$pass = 'DB_PASS';

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    $sql = "SELECT fname, email FROM members
            WHERE DATE_FORMAT(dob, '%m-%d') = :md
               OR DATE_FORMAT(anniversary, '%m-%d') = :md";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([':md' => $md]);

    while ($row = $stmt->fetch()) {
        // code to send the email
        // $row['fname'], $row['email']
    }
} catch (PDOException $e) {
    // handle error (log it)
    error_log($e->getMessage());
}

Example using a computed column for better performance

If you have MySQL 5.7+ you can create a generated column and index it:

ALTER TABLE members
  ADD COLUMN md CHAR(5) GENERATED ALWAYS AS (DATE_FORMAT(dob, '%m-%d')) VIRTUAL,
  ADD INDEX (md);

Then query with an equality on md (this can use the index):

SELECT fname, email FROM members WHERE md = '08-21';

Or maintain a small VARCHAR column populated at insert/update time with the month-day to get an indexable column on older MySQL versions.

Handling Feb 29

Decide whether Feb 29 birthdays should receive greetings on Feb 28 during non-leap years. Implement that logic in PHP if desired:

$md = date('m-d');
if ($md === '02-28' && !date('L')) { // not a leap year
    // also include '02-29' birthdays
}

Cron and timezone

  • Make sure the server timezone and PHP timezone are correct for your audience. The script will use PHP's timezone setting when calling date().
  • Cron runs in the system timezone by default — ensure alignment or use UTC.

Final notes

  • Keep the original simple approach if your table is small and traffic is low. For production systems, prefer PDO/mysqli and consider an indexed computed column for scalability.

Happy Coding..

No comments: