CODEWITHSUNDEEP
phpmysql
Panama Jack
Panama Jack

Reputation: 24448

Get Upcoming Birthdays Mysql and PHP

I've been trying to get upcoming birthdays using mysql statement and php. Problem is i've seen some solutions but they use date fields. Unfortunately mine is stored as timestamps. The code below only yields results if the birthdate is after 1970. How can I get a query that will give me current month, next month, month after and so on of upcoming birthdays? And how can I get it to ignore the year as well?

    $sql_query = "
        SELECT mem_id, DATE_FORMAT(FROM_UNIXTIME( birthdate ),'%d/%m/%Y') 
)
        FROM profiles
        WHERE
            IF ( MONTH( NOW() ) < 12,
              MONTH( DATE_FORMAT(FROM_UNIXTIME( birthdate ),'%Y%m%d') ) = MONTH( NOW() ) + 1,
              MONTH( DATE_FORMAT(FROM_UNIXTIME( birthdate ),'%Y%m%d') ) = 1)
        ORDER BY birthdate";

Upvotes: 1

Views: 4041

Answers (2)

Ludo - Off the record
Ludo - Off the record

Reputation: 5533

I've been searching for this code, but I couldn't find a clean/simple query (that also works with leap-years (29th of february problem))

So i've made my own.

Here's the simplest code to get the upcoming birthdays for the next x days, (this query also displays the birthdays of yesterday (or you can change it to a x number of days in the past)

SELECT name, date_of_birty FROM users WHERE 
        DATE(CONCAT(YEAR(CURDATE()), RIGHT(date_of_birty, 6)))
                BETWEEN 
                DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                AND
                DATE_ADD(CURDATE(), INTERVAL 5 DAY)

Upvotes: 2

deceze
deceze

Reputation: 522081

$currentMonth = date('n'); // or date('n', strtotime('+1 month')) or whatever
$query = "SELECT ... WHERE MONTH(FROM_UNIXTIME(`birthday`)) = $currentMonth";

Note though that UNIX timestamps are a terrible choice for storing birthdays:

  • They can not store dates prior to 1970 (unless you use negative numbers, which may not work everywhere).
  • To query for them in the database you have to convert all entries FROM_UNIXTIME to compare them, which is quite expensive.

You should use native MySQL DATE columns to store dates, which allows you to store a much wider range of dates and is much more efficient for queries.

Upvotes: 0

Related Questions