#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    18
    Rep Power
    0

    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
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    18
    Rep 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
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    18
    Rep 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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    677
    Rep Power
    7
    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.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    105
    Rep Power
    51
    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
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    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.

IMN logo majestic logo threadwatch logo seochat tools logo