days until birthday thing

[corporateboy]

Okay - I have a database of all my family and friend's birthdays the field is

date_of_event

I want to display all the Birthday's that are coming up in the next month, are today, or were in the last week - all from one query if possible and showing how many days ago it was, or days until it will be.

So far I have written this:

SELECT title, date_of_event
FROM calender_system
WHERE month(date_of_event)
BETWEEN month(DATE_SUB(now(),INTERVAL 7 DAY))
AND month(DATE_ADD(now(),INTERVAL 1 MONTH))

However this gets all Birthdays for the last month, this month and the current month - so how do I

1) make it just for the last week and the next month; and
2) calculate the number of days until or from the birthday

thanks for your help

[barand]

Try

SELECT title, date_of_event ,
TO_DAYS(date_of_event)-TO_DAYS(curdate()) as daydiff
FROM calender_system
WHERE date_of_event)
BETWEEN DATE_SUB(curdate(),INTERVAL 7 DAY)
AND DATE_ADD(curdate(),INTERVAL 1 MONTH)

hth

[corporateboy]

hmmm doesn't seem to work

I get a blank return on that query and when I run it without the WHERE statements

(ie SELECT title, date_of_event ,
TO_DAYS(date_of_event)-TO_DAYS(curdate()) as daydiff
FROM calender_system )

Here are two sample lines from the output

---

Name | date_of_event | daydiff

Mirium Smith | 1981-09-18 | -7976

Luke Geary | 1980-07-03 | -8418 |

Note the daydiff appears to be the number of days ago the date_of_event is rather than the difference between today and when the next birthday will fall.

[corporateboy]

I made a work around by extracting the month and day from the date_of_event, attaching them to the current year, then testing against todays date with the TO_DAYS() function.

There has to be an easier way of doing it - plus I think this will have a problem in December when the new year birthdays won't be displayed correctly.

here is the code

$sql = "
		  SELECT 
		  title, 
		  date_of_event,
		  CONCAT(YEAR(now()),date_format(date_of_event,\"-%m-%d\")) as date_comp, 
		  date_format(now(),\"%Y-%m-%d\") as date_now 
		  FROM calender_system 
		  WHERE month(date_of_event) 
		  BETWEEN month(DATE_SUB(now(),INTERVAL 7 DAY)) 
		  AND month(DATE_ADD(now(),INTERVAL 1 MONTH))
		  AND category = 'Birthday'
		  ORDER by date_comp ASC";

	  if($result = mysql_query($sql))
	  {
	  	$num = mysql_num_rows($result);
		if($num != 0)
		{
	  		while ($myrow = mysql_fetch_array($result))
	 		{
	  		$name = $myrow[title];
			$date_of_event = $myrow[date_of_event];
			$date_comp = $myrow[date_comp];
			$date_now = $myrow[date_now];
			$sql2 = "SELECT (TO_DAYS(\"$date_now\") - TO_DAYS(\"$date_comp\")) AS daysuntil";
				if($result2 = mysql_query($sql2))
				{
				$mything = mysql_fetch_array($result2);
				$daysuntil = $mything[daysuntil];
				if($daysuntil < 0){$daysuntil = $daysuntil*-1;}
				if($daysuntil == 1){$days = "day";}else{$days = "days";}
				//echo"$sql2<br><br> - daysuntil: $daysuntil<br><br><br>";
				if($date_comp == $date_now) {$mess = "<b>".$name."'s Birthday Today!</b>";}
				elseif($date_comp > $date_now) {$mess = $name." (".$daysuntil." ".$days." away)";}
				elseif($date_comp < $date_now) {$mess = $name." (".$daysuntil." ".$days." ago)";}
				}
				else
				{
				echo "error in $sql<p>".mysql_error();
				}
			echo"$mess<br>";
	 	 	}
	  	}
	  }
	  else
	  {
	  echo "error in $sql<p>".mysql_error();
	  }

[corporateboy]

the only other way my over engineering newbie head can think is to do the TO_DAYS calculations, then work out the year difference and subtract / add that number of days to the daydiff, that would in theory eliminate the year leaving only the month and day difference - does this sound right ?