2

I have this script which calculates days until someone's next birthday (there's probably a better way of doing this) but anyway, if someone's birthday was 3 days ago I want it to give me 362 days until next birthday but it gives -3 instead. No problem, add 365 days to that to get correct answer but it simply doesn't work and I can't work out why...the code:

$resultcurdate = mysql_query("select curdate()",$db);

    $curdate = mysql_fetch_array($resultcurdate);

    $curdate1 = explode('-', $curdate[0]);
    $day1   = $curdate1[2];
    $month1 = $curdate1[1];
    $year1  = $curdate1[0];

    $birthdate = explode('-', $databack10[birthday]);
    $day   = $birthdate[2];
    $month = $birthdate[1];
    $year  = $birthdate[0];

    $year = $year1;

    if ($month1>$month){$year=$year+1;}

    $birthdate5 = $year."-".$month."-".$day;

    $resultdate = mysql_query("select datediff('$birthdate5', now())",$db);

    $databackdate = mysql_fetch_array($resultdate);

    if($databackdate < '0'){$databackdate = (365 + $databackdate); }

Any ideas why this doesn't work?

Is there a way to do this with a SQL query? Birthday is date fieldtype.

Nicolas Kaiser
  • 1,628
  • 2
  • 14
  • 26
StudioTime
  • 22,603
  • 38
  • 120
  • 207
  • 1
    You might be better of by using the `DateTime()` class? – Nanne Dec 04 '11 at 18:29
  • Why are you doing that complexish operation with the dates before comparing them? What's wrong with `SELECT DATEDIFF( '$databack10[birthday]', NOW() )` ? – JJJ Dec 04 '11 at 18:33
  • 1
    possible duplicate of [How many days until XXX date?](http://stackoverflow.com/questions/654363/how-many-days-until-xxx-date) – hakre Dec 04 '11 at 18:34
  • 1
    Why are you using MySQL to calculate the date? You already have the birthday and current date. – Wipqozn Dec 04 '11 at 18:39
  • See my answer here using (reusable) SQL functions: http://stackoverflow.com/questions/13966942/how-selecting-birthdays-7-days-before-today-in-mysql/20956363#20956363 – Internal Server Error Jan 06 '14 at 18:16

2 Answers2

7
select 
date_field,
abs(if(right(curdate(),5) >= right(date_field,5),
datediff(curdate(),concat(year(curdate()+ interval 1 year),right(date_field,6))) ,
datediff(concat(year(curdate()),right(date_field,6)),curdate()))) as days
from table
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
2

An alternate which has a bit less code:

select 365.25 - ( TIMESTAMPDIFF(day, '1974-02-28', CURDATE()) mod 365.25 ) AS days_till_birthday

Explanation:
1. Get the current age in days:
TIMESTAMPDIFF(day, '1974-02-28', CURDATE())

2. Get how many days since the last birthday by dividing the current age in days by 365.25 and get the remainder:
(TIMESTAMPDIFF(day, '1974-02-28', CURDATE()) mod 365.25)

3. Subtract the remainder from 365.25 to see how many days left until the birthday:
365.25 - (TIMESTAMPDIFF(day, '1974-02-28', CURDATE()) mod 365.25)

Phil
  • 674
  • 7
  • 14