Birthday code

[Teach]

Hello

I was wondering how easy the following would be...

We have a 'user' table, and inside that is a field called 'birthday' with a date, stored as dd-mm

That field is not required, so it may be blank or populated with the date

Is it possible that I could use that field to display birthdays that are upcoming in the next week, or have been previously in the last week? Stored in the same user database is the field called 'account' with the person's name... so it could say:

Recent / upcoming birthdays: FooName (01 Aug), Foo2 (02 Aug), [b]Foo3 (04 Aug)[/b]

where the upcoming one is is in bold, the others not?

Thanks!

[Horizon88]

Gargh. I dunno how many times I've edited this post. 🙂

You'll want to use strtotime, but you'll need to format the date into an acceptable strtotime format (per http://www.gnu.org/software/tar/manual/html_node/tar_111.html#SEC111)

Something like this should get you started:

// mysql code to select user stuff here
if (!empty($birthdate)) {
   // they've supplied a date of birth
   // format it for strtotime()
   $birthdate = ereg_replace("-","/", $birthdate.'/'.date('y'));
   // get the timestamp
   $bdayTimestamp = strtotime($birthdate);
   // here you'd calculate timestamps from a week ago, and a week from now.
   // then you'll compare them to the bdayTimestamp and echo appropriately.
}

I'll keep playing with it.

[NogDog]

How are the birthdays stored in the DB (what type of column)?

[Horizon88]

okay, here's what I have so far. I can't seem to figure out how to compare the times and output the message, I just can't think right tonight. But...

<?PHP
// mysql stuff here, assuming $row['birthday']
if (!empty($row['birthday'])) {

$birthdate = ereg_replace("-","/", $row['birthday'].'/'.date('y'));
// formats $row['birthday'] to return as dd/mm/yy for strtotime()
$bdayTime = strtotime($birthdate);
// returns timestamp of $birthdate

$lastWeek = time() - 604800;    // timestamp from one week ago
$nextWeek = time() + 604800; //  timestamp for one week from now

// if statements that compare the timestamps. :\

}
?>

That might help.

[Teach]

Hello 🙂 thank you for the help

I am using this code:

<?php
$month_str = array('','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

$today=date("md");

$lastWeek = time() - 604800;    // timestamp from one week ago
$nextWeek = time() + 604800; //  timestamp for one week from now

$date1=date("md",$lastWeek);
$date2=date("md",$nextWeek);

echo "                <li><span>Recent / upcoming <strong>birthdays</strong>:&nbsp;";

$users=mysql_query("Select * from user");
while ($bday=mysql_fetch_array($users))
{
  $date3=trim($bday['birthday']);
  $day='';
  $month='';
  $found=0;
  for ($i=0; $i<strlen($date3); $i++)
  {
    if ($date3[$i]=='-')
      $found=1;
    else if ($found==0)
      $day=$day.$date3[$i];
    else
      $month=$month.$date3[$i];
  }
  if (strlen($day)<2)
    $day='0'.$day;
  if (strlen($month)<2)
    $month='0'.$month;

  $date3=$month.$day;

  if ($date3>=$date1 && $date3<=$date2)  // if the users birthday is between date1 and date2
  {
    if ($date3==$today)  // birthday is today
      echo "<span style=\"color: red;\">".$bday['account']." (".substr($bday['birthday'],0,2)." ".$month_str[substr($bday['birthday'],3,2)]." &#187; Today!)</span>&nbsp;";
    else if ($date3>$today)  // birthday still to come
      echo "<strong>".$bday['account']." (".substr($bday['birthday'],0,2)." ".$month_str[substr($bday['birthday'],3,2)]." &#187; Future)</strong>&nbsp;";
    else  // birthday last week
      echo "".$bday['account']." (".substr($bday['birthday'],0,2)." ".$month_str[substr($bday['birthday'],3,2)]." &#187; Past)&nbsp;";
  }
}
    echo "</span></li>\n";
 ?>

The problem I'm having is that if the date is stored as 17-8 (for 17th August), it works perfectly and will display as 17 Aug. However, if it's stored as 17-08 I just get 17 . I think this is to do with the month array recognising 8 as a month, but not 08. Of course, it works fine for two-digit months such as October onwards (10, 11, 12)

The other thing is, if the birthday is 1st January, and the date is stored as 1-1, it shows as 1-

Any ideas on how to fix these issues? 🙂

Thank you

[suntra]

You should use the MySQL date format : YYYY-MM-DD and the perform a search interval using mid() (= substr() in PHP) to only compare the month and day. It is not logical to do comparisons with the day in the first position !!

For example, let's use my brother's birthday (July 9th) and mine (June 21st)...

Mine should be before my brother's, don't you agree ?

But...

09-07 < 21-06
9-7 < 21-6 !

And then, if I use mm-dd...

06-21 < 07-09
6-21 < 7-9

Oh, by the way, ask MySQL DIRECTLY to return only users who have a their birthday in the required interval, don't loop through all the users in PHP !!!

[Teach]

Hi there 🙂
Thank you for your reply
Yes, I can see the logic of what you're saying

Would you be able to help me modify my current code to support what you're suggesting? The reason we didn't use a year in the birthday date is that we don't want to store the year (for personal reasons), so what I thought was having everyone set as the year 2000.

For example, our current "birthday" input asks the user to input their birthday as dd-mm (i.e. 17-08 for 17th August). If we could keep it like that, it would be great.

In the database, that could be stored as 2000-08-17

Any help with modifying this would be much appreciated
Regards!

[suntra]

Let's define some variable...

table is the name of you table in MySQL

field1 is you current date field (dd-mm)

field2 is the new date field (yyyy-mm-dd)

First thing to do is add field2...

ALTER TABLE `[I][COLOR="Red"]table[/COLOR][/I]` ADD `[I][COLOR="Red"]field2[/COLOR][/I]` DATE NULL ;

(I used "NULL", but I suggest you use "NOT NULL", this way, you'll have to define a date when inserting new values)

And then, we'll cut field1 in some parts and store them in field2... (here I assume you stored all the dates using dd-mm and not d-m (so 01-01, 02-02, ... shortly, with a zero in the number is lower than 10)

UPDATE `[I][COLOR="Red"]table[/COLOR][/I]` SET [I][COLOR="Red"]field2[/COLOR][/I] = CONCAT('2000-', SUBSTRING([I][COLOR="Red"]field1[/COLOR][/I], 4, 2), '-', SUBSTRING([I][COLOR="Red"]field1[/COLOR][/I], 1, 2))

And if you stored dates using d-m, you can use this :

UPDATE `[I][COLOR="Red"]table[/COLOR][/I]` SET [I][COLOR="Red"]field2[/COLOR][/I] = CONCAT('2000-', SUBSTRING([I][COLOR="Red"]field1[/COLOR][/I], (LOCATE('-', [I][COLOR="Red"]field1[/COLOR][/I]) + 1)), '-', SUBSTRING([I][COLOR="Red"]field1[/COLOR][/I], 1, (LOCATE('-', [I][COLOR="Red"]field1[/COLOR][/I]) - 1)))

And there you go for the transformation 😉

[suntra]

And then the PHP script should look like this... this one will loop through all results and simply print the entire array... BUT MySQL does filter the result and only returns those who have to be returned...

$resMySQL	= mysql_connect('localhost', 'user', 'password');
mysql_select_db('database_name', $resMySQL);
$strLast_Week	= date('m-d',time() - 604800);
$strNext_Week	= date('m-d', time() + 604800);
$strQuery		= 'SELECT * FROM `table` WHERE (SUBSTRING(field2, 6) >= \'' . $strLast_Week . '\') ' . (($strLast_Week < $strNext_Week) ? 'AND' : 'OR') . ' (SUBSTRING(field2, 6) <= \'' . $strNext_Week . '\')';
$resResults		= mysql_query($strQuery);
echo '<pre>';
while($arrResult = mysql_fetch_assoc($resResults)) {
	print_r($arrResult);
	echo "\n";
}
echo '</pre>';