CODEWITHSUNDEEP
phpmysql
Fero
Fero

Reputation: 13315

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

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?

Upvotes: 1

Views: 1905

Answers (2)

Anax
Anax

Reputation: 9362

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)

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425251

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:

, 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 :)

Upvotes: 0

Related Questions