How would I get the birthdays of friends who are celebrating their birthday this week, this month and next month using MYSQL and PHP?

Question

I am currently working in an site which needs to get the birthdays of friends who are celebrating their birthday this week, this month and next month using MYSQL and PHP.

How would I go about this?

Answer 1

Provided you store birthdates in DATE (or DATETIME) format in MySQL you can use the following Queries:

// This week
SELECT * FROM person WHERE WEEK( birthdate ) = WEEK( NOW() )

// This month
SELECT * FROM person WHERE MONTH( birthdate ) = MONTH( NOW() )

// Next month

SELECT * FROM person WHERE MONTH( birthdate ) = MONTH( NOW() ) + 1;

SELECT * FROM person WHERE IF
  ( MONTH( NOW() ) < 12, MONTH( birthdate ) = MONTH( NOW() ) + 1,
  MONTH( birthdate ) = 1)

Answer 2

SELECT  *
FROM    users
WHERE   birthday + INTERVAL YEAR(CURRENT_DATE) - YEAR(birthday) YEAR BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 3 DAY

Note that this condition is not sargable and requires full indes scan.

In SQL Server, Oracle and PostgreSQL this can be improved using this approach:

• Selecting birthdays

, but MySQL lacks a way to generate a random resultset.

However, you can create a dummy table and store years from 1900 to 2100 in it.

Add a cron task to update it in 2100 since you can forget easily :)