Birthdays in DB

[ragtek]

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

 

[Mike]

The year isn't required.

 

[ragtek]

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

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?

 

[Lawrence]

    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?

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.

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 / 4 == 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 + 9 > $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.