help with date functions

[sakura_fujin]

hi there. is there a simpler way to work with date and timestamp functions of php? i need to increment the age field in my mysql table by 1 EVERY DAY to output its current age in my web page. somebody suggested working with timestamp but how do you know if a day has already passed by comparing the timestamp of the current date with the 'creation date' of the record? can somebody help me pls? im stuck with my project..

for reference, i found this forum and we're totally on the same boat.
http://www.webmasterworld.com/databases_sql_mysql/3370990.htm

[darkagn]

I would store the create_date in the database in a datetime field. Then you can easily work out the age by grabbing the current time (with the time() function) minus the timestamp of the create_date (using strtotime() function) and dividing by the number of seconds in one day. Integer division in PHP drops any remainder, so this algorithm will give you the number of whole days since the create_date.

[tomatocms]

Try this:

UPDATE your_table 
SET age_field = IF(age_field < DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), created_date), DATEDIFF(DATE_FORMAT(now(), '%Y-%m-%d'), created_date), age_field);

[darkagn]

I have to disagree with tomatocms on this one. There is no need to store the "age_field" in the database. If you had 100000 records in your table there would be 100000 unnecessary fields, expanding the memory used by your database. You can calculate the age of something very easily in code with the algorithm outlined in my previous post.

[sakura_fujin]

here's what i've come up so far:

$dateTime = new DateTime("now", new DateTimeZone('Asia/Singapore'));
$date=$dateTime->format("Y-m-d");
echo "Date today: ".$date."<br>";

$sql="SELECT * FROM table";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
	$batch=$row['batch_no'];
	$presentage=$row['present_age'];
	$date=$row['date'];

	$stringparts=explode("-",$date);

	$date1 = time();
	$month =$stringparts[1];
	$day=$stringparts[2];
	$year=$stringparts[0];

	$date2 = mktime(0,0,0, $month, $day, $year);

	$dateDiff = $date1 - $date2;

	$fullDays = floor($dateDiff/(60*60*24));

	if($fullDays>0){
		$presentage=$presentage+$fullDays;
	}
	
	$sql2="UPDATE table SET present_age = '$presentage' WHERE batch_no='$batch'";
	$query2=mysql_query($sql2);

}

the code above works perfectly but only for ONE run of the script. if i chose to view the same page several times in one day, it always calculates the age and increments it according to the calculated no. of days from the current date to the creation date. i need some sort of a flag to tell my script not to run if it has already calculated the age previously within 24 hours. any idea how to implement?

[darkagn]

$sql2="UPDATE table SET present_age = '$presentage' WHERE batch_no='$batch'";
$query2=mysql_query($sql2);

I don't understand why you are doing this. You shhouldn't need to store the age in the database, just calculate it when you are displaying the age.

However if you REALLY want to do this, then change the line:

$presentage=$presentage+$fullDays;

to

$presentage=$fullDays;

EDIT: Have I mentioned that this is very bad database design?

[sakura_fujin]

ah. i need to store the current age because its one of my required fields in my table. instead of asking the user to input the current age everyday, i decided to automatically calculate it for him based on the date.

also, for every new record added, my present age = starting age and my starting age value is around 20-25 days old. so it's necessary to add the $fulldays count to $presentage.

any suggestion on how to improve my database design? im still learning my way around all this. thanks

[darkagn]

Ok what I would do is store the create_date = now - starting_age. Then I would use the above algorithm to calculate the age on the fly.
Example:
Create a record now with a start_age of 5 days. create_date of the record is now - 5 days = 4 Feb 2010 (in Australia) - 5 days = 30 Jan 2010.
Tomorrow the age is current day - create_date = 5 Feb 2010 - 30 Jan 2010 = 6 days. You don't need to store this age in the database and constantly update it, just calculate it when the record is read.

That's what I would do anyway. Personally I think it is easier to implement, uses less storage and creates less load on the database.