Get recent/upcoming birthdays in Laravel

birthdays.php

<?php

// Note: Adding 1 to the dates below accounts for
// diffences between PHP date('z') [starts at zero]
// and MySQL DAYOFYEAR [starts at 1]

// Want to use specific start and end date?
$start = date('z', strtotime('12/1/2021')) + 1;
$end = date('z', strtotime('1/30/2022')) + 1;

// Want to use a window around a certain date?
$referenceDate = date('z', strtotime('12/29/2021')) + 1;
$start = $referenceDate - 7; // window starts 7 days ago
$start = $start < 1 ? $start + 366 : $end; // adjust for start dates that push back to the previous year
$end = $referenceDate + 7; // window ends 7 days from now
$end = $end > 366 ? $end - 366 : $end; // adjust for end dates that push to a new year

$query = \App\User::whereRaw("DAYOFYEAR(birthday) BETWEEN ? AND ?", [$start, $end]);

if ($end < $start) {
  $query = $query->orWhereRaw("DAYOFYEAR(birthday) BETWEEN ? AND ?", [$start, 366])
    	         ->orWhereRaw("DAYOFYEAR(birthday) BETWEEN ? AND ?", [0, $end]);
}

$users = $query->get();

One of the best queries I found for this!
One thing it could need is adjustment when you search over a year's ending

Thank you!

I decided to update this a lot to handle search across the end of a year. Let me know if you have any feedback!

How do you handle leap year?

@77media-creations I believe this handles leap years just fine. Do you have an example of it not working for leap years? I would love to know how I can improve it.

what happen if $start = $referenceDate - 7; is equal than -1 or -2 ?

@dwiliandy I have updated this to account for start dates that end up in the negative. Thanks for pointing out this issue!