1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Birthdays in DB

Discussion in 'XenForo Development Discussions' started by ragtek, Dec 9, 2010.

  1. ragtek

    ragtek Guest

    Why do you save the birthday in 3 columsn (day, month, year) instead of using a date field?
     
  2. Mike

    Mike XenForo Developer Staff Member

    The year isn't required.
     
    Mikey likes this.
  3. ragtek

    ragtek Guest

    Ah, ok

    That sux a little bit, because i'm not able to get the birthdays:(

    The today birthdays are no problem, but the next days are over my sql knowledge^^

    PHP:
    <?php

    class Ragtek_BDSBB_Model_Birthdays extends XenForo_Model{

        public function 
    getTodayBirthdays(){
            
    $sql =     "SELECT user.username, user.user_id, profile.user_id FROM xf_user_profile AS profile "
                      
    "left join xf_user AS user ON (profile.user_id = user.user_id) "
            
    "WHERE dob_day = DAY(CURDATE()) "
            
    "AND dob_month = MONTH(CURDATE())";
            
    $users $this->_getDb()->fetchAll($sql);
            return 
    $users;
        }

        public function 
    getNextBirhtdays(){


            
    $sql "SELECT user_id FROM xf_user_profile "
            
    "WHERE dob_day >  DAY(DATE_ADD(CURDATE(),INTERVAL 10 DAYS)) "
            
    " AND dob_month >= MONTH(CURDATE())";

            
    $users $this->_getDb()->fetchAll($sql);
            return 
    $users;
        }

    }
    Anybody with great sql knowledge can help me here?:D
     
  4. Lawrence

    Lawrence Well-Known Member

    This won't work because if it is Dec 28th, for example, the next month is Jan (1), so your dob_month is less than the current month. The same will be for the days (30, 31, 1, 2,....)

    What you need to do is pre-calcute the actual 10 days and see if any fall in a new month. I wanted to see if I could get this to work. Here is one way. It is not pretty, I just threw it together to make it work as I didn't have much time to spend on it. Hopefully, it'll help. It takes leap year into account too.

    PHP:
    list($cYear$cMonth$cDay) = explode('-'XenForo_Locale::getFormattedDate(XenForo_Application::$time'Y-m-d'));

    $leapYear 0;

    $cYear intval($cYear);  // convert string to int

    if ($cYear == intval($cYear 4))
    {
           
    $leapYear++;
    }

    $cMonth intval($cMonth);

    switch (
    $cMonth)
    {
         case 
    2:
                
    $numberofDays 28 $leapYear;
         break;

         case 
    4:
         case 
    5:
         case 
    9:
         case 
    11:
                
    $numberofDays 30;
         break;

         default:
                
    $numberofDays 31;
         break;
    }

    $overlapDays 0;
    $nextmonthsDays = array();
    $thismonthsDays = array();
    $x 0;

    $nDay intval($cDay);

    $nDay++; // start at next day

    if ($nDay $numberofDays)
    {
         
    $nDay 1;
    }
     else if (
    $nDay $numberofDays)
    // 9 + position 0 = 10 days

         
    $nMonth $cMonth;
         
    $nMonth++;

         if (
    $nMonth 12)
         {
              
    $nMonth 1;
         }

         
    $overlapDays = ($nDay 9) - $numberofDays;

         for (
    $x 0$x $overlapDays$x++)
         {
              
    $nextmonthsDays[] = $x 1;
         }
    }

    if (
    $x 10)
    {
        
    $x 10 $x;

        for (
    $y 0$y $x$y++)
        {
             
    $thismonthsDays[] = $nDay $y;
        }
    }

    $db XenForo_Application::get('db');

    if (!empty(
    $thismonthsdays))
    {
    $thismonthsDays implode(','$thismonthsDays);

                
    $test $db->fetchAll('
                SELECT user_id
                FROM xf_user_profile
                WHERE dob_day IN (' 
    $thismonthsDays ')
                AND dob_month = ' 
    $cMonth '
                ORDER BY user_id
                '
    );

     }

    if (!empty(
    $nextmonthsdays))
    {
    $nextmonthsDays implode(','$nextmonthsDays);

                
    $nexttest $db->fetchAll('
                SELECT user_id
                FROM xf_user_profile
                WHERE dob_day IN (' 
    $nextmonthsDays ')
                AND dob_month = ' 
    $nMonth '
                ORDER BY user_id
                '
    );

    }
    I sorted by user_id but that is easily changed to sort by day. $test will contain a list of users with upcoming birthdays for this month, and $nexttest (if exists) contains a list for the first x days of the next month.
     

Share This Page