Page 1 of 1

Selecting from database where date is same month and day

Posted: Thu Mar 08, 2007 4:52 pm
by Luke
Alright I've got a mysql table of users with a date field for their birth dates. How would I go about selecting users who's birthday is today? I was looking at EXTRACT() but I can't figure out how to use it to do what I want. Would this be a job for regex?

Posted: Thu Mar 08, 2007 4:53 pm
by nickvd
What format is their birthday stored as?

Posted: Thu Mar 08, 2007 4:53 pm
by veridicus
How is the birthday stored? I hope you answer unix timestamp. Regex would definitely be a performance hit. If it's unix timestamp it's simple to query against the current day's timestamp.

Posted: Thu Mar 08, 2007 4:58 pm
by Luke
:oops: I already figured it out... I always post here right before I find the answer I'm looking for. :x

Code: Select all

function getBirthdays($date = null)
    {
        if (is_null($date))
        {
            $date = time();
        }
        
        $birthdate = getdate($date);
        
        $query = "
			SELECT
				username, DATE_FORMAT( birth_date, '%m-%d' ) AS bday
			FROM
				users
			WHERE
				MONTH( birth_date ) = '" . $birthdate['month'] . "'
			AND
				DAYOFMONTH( birth_date ) = '" . $birthdate['mday'] . "'
		";

        // perform query, etc.
    }

Posted: Fri Mar 09, 2007 3:04 am
by mikeq
Nah, I've got a better way

Code: Select all

$query = " 
                        SELECT 
                                username, DATE_FORMAT( birth_date, '%m-%d' ) AS bday 
                        FROM 
                                users 
                        WHERE 
                                MONTH( birth_date ) = MONTH(NOW()) 
                        AND 
                                DAY( birth_date ) = DAY(NOW()) 
                "; 

        // perform query, etc.
No need to use any PHP variables, just let MySQL do the work.[/syntax]

Posted: Fri Mar 09, 2007 6:42 am
by Jenk
But what if Ninja needs to select a day other than today?

Posted: Fri Mar 09, 2007 7:19 am
by mikeq
Jenk wrote:But what if Ninja needs to select a day other than today?
Well Ninja's query was this
How would I go about selecting users who's birthday is today?
My answer does that