Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers Forums > Other Technologies > Database Help
Reload this Page

subtract dates

Discuss subtract dates in the Database Help forum on Codewalkers.
subtract dates General SQL help. Help with MySQL, MSSQL, Oracle, mSQL, PostgreSQL, etc.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
  #1  
Old January 4th, 2004, 07:52 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
subtract dates
I want to make a "Todays Birthday" type of thing ( like here on Codewalkers ). I'm pulling a date from mysql stored in MM-DD-YYYY format. I want to subtract it from the current date to get the number of years between the 2 dates. The only example I found was like this...

$date = date('m-d-Y');
$x = $date; (for current date)
$y = $birthdate; (field from mysql)
$age = $x - $y;

All I get is a zero. What am I missing?
Reply With Quote
  #2  
Old January 4th, 2004, 08:42 PM
sliver's Avatar
sliver sliver is offline
Moderator
Codewalkers Novice (500 - 999 posts)
  
Join Date: Apr 2007
Location: WI, USA
Posts: 906 sliver User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 1 h 21 m 10 sec
Reputation Power: 2
Send a message via AIM to sliver Send a message via XFire to sliver
RE: subtract dates
php Code:
Original - php Code 
  1. $now=mktime();
  2. $birthday=strtotime($birthday); //If format is something like 4-12-1985
  3. $difference=date("m-d-Y",($now-$birthday));


Something like this should work.
Reply With Quote
  #3  
Old January 4th, 2004, 09:19 PM
honcho's Avatar
honcho honcho is offline
Contributing User
Codewalkers Beginner (1000 - 1499 posts)
  
Join Date: Apr 2007
Location: Cape Cod
Posts: 1,347 honcho User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 52 m 2 sec
Reputation Power: 3
RE: subtract dates
sliver's code isn't going to work since subtracting two timestamps doesn't give you a timestamp like you'd want. Since timestamps are based on 0 = 1/1/1970, you'll get something $age years greater than 1970. Even then you might have problems with birthdays before 1970.

Since you're getting the birthdate out of MySQL, you might want to do the age calculation in MySQL to. There are lots of comments on this page about method to calculate age.
Reply With Quote
  #4  
Old January 4th, 2004, 09:58 PM
CodeKadiya CodeKadiya is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: Colombo,Sri Lanka
Posts: 2,313 CodeKadiya User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
Send a message via Yahoo to CodeKadiya
RE: subtract dates
Here is a one way of doing this thing:
php Code:
Original - php Code 
  1.  
  2. $today = date("Y-m-d");
  3. $result=mysql_query("SELECT username,YEAR(birthday) FROM user WHERE MONTH(birthday)=MONTH('$today') 
  5. AND DAYOFMONTH(birthday)=DAYOFMONTH('$today')");
  6. while($row = mysql_fetch_array($result))
  7. {
  8.     $userName = $row[0];
  9.     $userAge = date("Y")-$row[1];
  10.     echo "$userName [$userAge]<br>";
  11. }


Hope you can develop your script on the above example.
Reply With Quote
  #5  
Old January 4th, 2004, 10:46 PM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: subtract dates
This is what I set up, however it isn't showing any results. The field names are "fname" and "birthdate", table name is "fpc_birthday".

php
connection string
$today = date("Y-m-d");
$result=mysql_query("SELECT fname,YEAR(birthdate) FROM fpc_birthday WHERE MONTH(birthdate)=MONTH('$today') AND DAYOFMONTH(birthdate)=DAYOFMONTH('$today')");
while($row = mysql_fetch_array($result))
{
$userName = $row[0];
$userAge = date("Y")-$row[1];
echo "$userName [$userAge]<br>$today";
}
/php

I added the $today in the echo, even this does not display unless I move the echo outside of the ending "}" bracket, then all that displays is this...

[]
2004-01-04

I also changed userName to fname just in case...nothing is showing up. I'm sure I', missing something fairly simple but I don't know what it is.
Reply With Quote
  #6  
Old January 5th, 2004, 12:39 AM
CodeKadiya CodeKadiya is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: Colombo,Sri Lanka
Posts: 2,313 CodeKadiya User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
Send a message via Yahoo to CodeKadiya
RE: subtract dates
Try this. Are you sure you have records which has the birthdays of today? Above code worked very well in my site. I feel strange in this case!
php Code:
Original - php Code 
  1.  
  2. $today = date("Y-m-d");
  3. $result=mysql_query("SELECT fname,YEAR(birthdate) FROM fpc_birthday WHERE MONTH(birthdate)=MONTH('$today') AND DAYOFMONTH(birthdate)=DAYOFMONTH('$today')") or die(mysql_error());
  4. echo "There are ".mysql_num_rows($result)." birthdays today.";
  5. while($row = mysql_fetch_array($result))
  6. {
  7.     $userName = $row[0];
  8.     $userAge = date("Y")-$row[1];
  9.     echo "$userName [$userAge]<br>";
  10. }
Reply With Quote
  #7  
Old January 5th, 2004, 12:53 AM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: subtract dates
The only thing showing is...

There are 0 birthdays today.

There are 3 entries with todays date...
01-04-1960
01-04-1990
01-04-2004

I know the field names and table name are correct, as well as the db connection. Does the order of d-m-y make any difference?
Reply With Quote
  #8  
Old January 5th, 2004, 01:42 AM
CodeKadiya CodeKadiya is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: Colombo,Sri Lanka
Posts: 2,313 CodeKadiya User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
Send a message via Yahoo to CodeKadiya
RE: subtract dates
It makes a difference, but not in this SQL query. This is strange, I used this same code in my site and worked well.
Reply With Quote
  #9  
Old January 5th, 2004, 02:10 AM
CodeKadiya CodeKadiya is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: Colombo,Sri Lanka
Posts: 2,313 CodeKadiya User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
Send a message via Yahoo to CodeKadiya
RE: subtract dates

I hope you are not dealing with huge amount of data in your site. This is a long method. But

try just to check what's going on here!
php Code:
Original - php Code 
  1.  
  2. $result=mysql_query("SELECT * FROM fpc_birthday") or die(mysql_error());
  3. while($row=mysql_fetch_array($result))
  4. {
  5.     $array=explode('-',$row['birthdate']);
  6.     $month=$array[0];
  7.     $day=$array[1];
  8.     if(($month==date("m"))&&($year==date("Y")))
  9.         echo $row['fname']."<br>";
  10. }
Reply With Quote
  #10  
Old January 5th, 2004, 02:35 AM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158 mdhall User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: subtract dates
This is something I'm testing for a program I'm putting together, and there are only 5 entries in this table so far. This still brings up no results. I hard coded the user/pass/db into the connection string to make sure there were no problems. If I do the following, it pulls the information fine, but displays all birthdays in the table, not the ones for just today.

php
include("connect.php");
$conn = mysql_connect(localhost,"$dbuser","$dbpass") or die ("Could not connect MySQL");
mysql_select_db("$db") or die ("Could not open database");
$query = ("SELECT * FROM fpc_birthday ORDER BY birthdate ASC");
$result = mysql_query($query);
$num=mysql_num_rows($result);
$i = 0;
while ($i < $num):
$id=mysql_result($result,$i,"id");
$fname=mysql_result($result,$i,"fname");
$birthdate=mysql_result($result,$i,"birthdate");
echo "$id, $fname, $birthdate<br>";
$i++;
endwhile;
/php
Reply With Quote
  #11  
Old January 5th, 2004, 02:55 AM
CodeKadiya CodeKadiya is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: Colombo,Sri Lanka
Posts: 2,313 CodeKadiya User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
Send a message via Yahoo to CodeKadiya
RE: subtract dates
I guessed that the problem was with your database connection, but you told that you are

really sure with that one! Ok, above code you posted giving all the records is not

surprising as I cannot see any condition to check birthdays only for today. You are grabbing

all the records from the table and just displaying it. That causes your problem!

Now run this script:
php Code:
Original - php Code 
  1.  
  2. include("connect.php");
  3. $conn = mysql_connect(localhost,"$dbuser","$dbpass") or die ("Could not connect MySQL");
  4. mysql_select_db("$db") or die ("Could not open database");
  5.  
  6. $today = date("Y-m-d");
  7. $result=mysql_query("SELECT fname,YEAR(birthdate) FROM fpc_birthday WHERE 
  9. MONTH(birthdate)=MONTH('$today') AND DAYOFMONTH(birthdate)=DAYOFMONTH('$today')") or 
  10.  
  12.  
  13. $num = mysql_num_rows($result)
  14.  
  15. echo "There are $num birthdays today.";
  16.  
  17. while($row = mysql_fetch_array($result))
  18. {
  19.     $userName = $row['fname'];
  20.     $userAge = date("Y")-$row[1];
  21.     echo "$userName [$userAge]<br>";
  22. }
Reply With Quote
  #12  
Old January 5th, 2004, 04:33 AM
mdhall mdhall is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 158