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

I already figured it out... I always post here right before I find the answer I'm looking for.
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