Get upcomming birthdays

Dev Shed Forums Sponsor:

January 8th, 2013, 04:19 AM

Timmeyy

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 8 h 55 m 59 sec
Reputation Power: 0

PHP-General - Get upcomming birthdays

Hey, I hope someone can help me with the following:

I need to get a list of users from a mysql database with birthdays in the upcomming week. dateOfBirth in the database are saved as php time( );

Cheers
Tim

January 8th, 2013, 04:45 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,872

Time spent in forums: 1 Month 2 Weeks 2 Days 1 h 24 m 30 sec
Reputation Power: 813

Hi,

Quote:

Originally Posted by Timmeyy

dateOfBirth in the database are saved as php time( );

Why?? MySQL has a DATE type for exactly this purpose: to store dates. Using your own Unix seconds makes absolutely no sense -- unless you love to manually calculate dates.

So the first thing you should do is convert the seconds into actual date values. After that, you can use the date functions of MySQL.

The monday of next week is 9 - DAYOFWEEK(date) days after the current date.

Last edited by Jacques1 : January 8th, 2013 at 04:49 AM.

January 8th, 2013, 05:06 AM

Timmeyy

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 8 h 55 m 59 sec
Reputation Power: 0

Yes I know, I hate it too.. I also hate all this date conversion, gives me a big headache. The system works like this, I didn't make it, nor can I change it. I guess ill just go fetching all users and covert the birthdays one by one =_="

Thanks anyway

January 8th, 2013, 05:10 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,872

Time spent in forums: 1 Month 2 Weeks 2 Days 1 h 24 m 30 sec
Reputation Power: 813

You can create the date values on the fly with FROM_UNIXTIME().

But my "equation" is wrong, because the counting starts with sunday (so sunday is 1 instead of 7). I have to think about that a bit more.

January 8th, 2013, 05:34 AM

Timmeyy

Registered User

Join Date: Nov 2012

Posts: 18

Time spent in forums: 8 h 55 m 59 sec
Reputation Power: 0

Ill be honest, I'm incredibly bad at this date stuff, but right now I have the follow code to get birthdays:

The Query:

PHP Code:


		
			
 $result = $this->Database->prepare

("SELECT *,(FROM_UNIXTIME(`dateOfBirth`)) as 'bday' FROM `member` 

WHERE `dateOfBirth` != '' ")            

 ->execute();         

return $result->fetchAllAssoc();

The Convertion

PHP Code:


		
			
foreach( $users as $key => $data ){             

if( !empty($data['bday'])) {                 

   list($date, $time) = explode(' ', $data['bday']);                 

   list($year,$m,$d) = explode('-', $date);                 

   $new_date = date('Y') . '-' . $m . '-' . $d;                               

   $now = time();                 

   $end = $now + (60*60*24*7);                 

   $bday = strtotime($new_date);                                  

   if($bday < $end && $bday > $now )                 {                     

echo $user['firstname'] . ' - ' .$data['bday'] . <br/>';                 

}            

}         

}

If you know easier or better solution i'm all ears :P

January 8th, 2013, 08:15 AM

Triple_Nothing

Contributing User

Join Date: Jun 2009

Posts: 297

Time spent in forums: 3 Days 8 h 45 m 39 sec
Reputation Power: 5

It sounds your simply wishing to select a range...? The range can be done as one of follows:

Code:

SELECT *,(FROM_UNIXTIME(`dateOfBirth`)) as 'bday' FROM `member` WHERE `dateOfBirth` >= [start date] AND `dateOfBirth` <= [end date] 
SELECT *,(FROM_UNIXTIME(`dateOfBirth`)) as 'bday' FROM `member` WHERE `dateOfBirth` BETWEEN [start date] AND [end date]

As far as defining your start/end date, simply find current date, and if Day of Week is NOT equal to 1, then start date is the following day equaling 1, otherwise current date. End date is just add 6 days to that, so that should be no prob.

EDIT: Actually, I should ask this first. When you say birthdays in the UPCOMING week, do you mean the current day plus the next 6 days? Or if today is Tuesday, do you want birthdays for the next Sunday thru Saturday?

EDIT2: I figure I'd toss some options in to maybe help you speed this up and not have to wait...

PHP Code:


		
			
//if start day is current day

$start = time();

//if start day is set day of week

$start = strtotime("next Sunday");

//set 6 days later

$end =  $start + 518400;

Then use a simple while() loop to echo your items and format your time/date, if format is removed from SELECT statement...
Date formating: http://php.net/manual/en/function.date.php

Hope this helps.

Last edited by Triple_Nothing : January 8th, 2013 at 09:53 AM.

January 8th, 2013, 11:12 AM

Rhytz

Contributing User

Join Date: Jan 2011

Posts: 100

Time spent in forums: 1 Day 11 h 4 m 50 sec
Reputation Power: 50

Are you still using the time() function?

If so, you could simply do:

Code:

SELECT birthday FROM table WHERE birthday >= ".time()." AND birthday <= ".time() + 518400."

Shame on me, but I often use the time() function in mysql

January 8th, 2013, 11:27 AM

ManiacDan

Likely to be eaten by a grue.

Dev Shed God 10th Plane (9500 - 9999 posts)

Join Date: Oct 2006

Location: Pennsylvania, USA

Posts: 9,809

Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 2 Months 3 Weeks 18 h 6 m 48 sec
Reputation Power: 6112

Birthdays are not stored as "the person's next birthday," they're usually stored as "the person's day of birth," which means none of them are "upcoming" dates, you want the ANNIVERSARY of that date.

Luckily, we discussed this a while back and I provided a response.

Comments on this post

Rhytz agrees: Stupid me

Triple_Nothing agrees: Wow. Nice catch. Such a blonde moment for me. :-P

__________________
HEY! YOU! Read the New User Guide and Forum Rules

"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

"The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.

PHP-General - Get upcomming birthdays

Developer Shed Advertisers and Affiliates