Getting Upcoming Birthdays From MySQL

Assuming you have a table called `users` with birthday as `bday` for the column name, this will pull all users who are having a birthday in the next 7 days. This is very useful if you want to showcase your member’s birthdays on your website.

SELECT * FROM `users` WHERE  DAYOFYEAR(curdate()) <= dayofyear(`bday`) AND DAYOFYEAR(curdate()) +7 >= dayofyear(`bday`) LIMIT 30;

There is of course one small problem with this, it’s not sorted. If we sort by `bday` ASC it’ll still be off since user’s are born on different years. What we really want is to sort by month and day only. I’m sure this can be done in MySQL but here is a PHP solution to take care of it.

$sql = "SELECT * FROM `users` WHERE  DAYOFYEAR(curdate()) <= dayofyear(`bday`) AND DAYOFYEAR(curdate()) +7 >= dayofyear(`bday`) LIMIT 30;";
$query = mysql_query($sql);
$rows = mysql_num_rows($query);
if(!empty($rows)){
	while($resultFind = mysql_fetch_array($query)){
		$arr[] = array('bday' => $resultFind['bday'], 'bday2' => date("md", strtotime($resultFind['bday'])));
	}
}

function compare($x, $y) {
	if ( $x["bday2"] == $y["bday2"] )
	 return 0;
	else if ( $x["bday2"] < $y["bday2"] )
	 return -1;
	else
	 return 1;
}

usort($arr, "compare");

Using usort() we can sort based on the month and day. You now have your list sorted 🙂

, , ,