time left for expiry of an event

[omerbutt]

hi ,
i am making a post notices section where i have to show the notices that are still valid, at the time of posting the notice i save the time stamp and date in different columns now i have to check that if there is more than one days left the i have to show the days remaining and if it is the last date of the notice then i have to calculate the time left in H:M:S i have been messing around php.net and w3cschools but it all made a mess could not get anything that how should i do that,
for an example if i have posted a notice on date: 15/5/2008 and time:15:20:33 and the notice end date is 17/5/2008 and lets say if today is 17/5/2008 so now i have to show the time left in expiry what i am doing right now is that i am taking the current time and subtracting the timestamp stored in the database for that notice and display the time the code is here
[code=php]
if (mysql_num_rows ($rf)){
$num = 0;
while ($f2 = mysql_fetch_arr ay($rf)){
$notice_enddate 2 = $f2['notice_enddate '];
$timold=$f2['notice_stamp'];
$notice_enddate 3 =explode("-", $notice_enddate 2);
$date1=explode( "-", $date);
$validity=$noti ce_enddate3[2]-$date1[2]."days left in expiry";
if($validity<=1 ){
$current_time=d ate("H:i:s",tim e());
echo $time_left=$cur rent_time-$timold;
if($time_left){
$validity="time left :".$time_lef t;
}
}
}
[/code]
i have calculated the days difference but when it comes to time i am stuck, can any one help please it is giving me time in "minus"
thanks in advance for any help,
Reagrds,
omer

[ronverdonk]

Time calculations can (of course) be done in PHP, but doing it in your MySQL SELECT is a lot easier. See this sample that accomplishes the same as you do in your code:[php]$sql="SELECT *,
DATEDIFF(notice _enddate,CURDAT E()) AS days_left,
TIMEDIFF(notice _stamp,CURTIME( )) AS time_left
FROM table_name";
if (mysql_num_rows ($rf)){
$num = 0;
while ($f2 = mysql_fetch_arr ay($rf)){
if ($f2['days_left'] > 0)
$validity=$f2['days_left']." days left in expiry";
else
$validity="time left: ".$f2['time_left'];
}
}[/php]Ronald

[omerbutt]

Time calculations can (of course) be done in PHP, but doing it in your MySQL SELECT is a lot easier. See this sample that accomplishes the same as you do in your code:[php]$sql="SELECT *,
DATEDIFF(notice _enddate,CURDAT E()) AS days_left,
TIMEDIFF(notice _stamp,CURTIME( )) AS time_left
FROM table_name";
if (mysql_num_rows ($rf)){
$num = 0;
while ($f2 = mysql_fetch_arr ay($rf)){
if ($f2['days_left'] > 0)
$validity=$f2['days_left']." days left in expiry";
else
$validity="time left: ".$f2['time_left'];
}
}[/php]Ronald

thanks ron for the reply ,
i tried it at my end it did it right in th case of days left but the time it is giving is not the time left in expiry but it is the time since the notice has been posted i tried to reverse it like[code=mysql]TIMEDIFF(CURTIM E(),notice_stam p)[/code]
but it gave me negative value ,so what now should i switch back to php or is there any other way to do it in mysql
regards,
omer

[ronverdonk]

You calculate the difference between 'now' and the time of expiry in the database, i.e. now-expiry.

When the date difference is 0 and the timediff outcome is negative it means that the expiry time is in the past, so the event is already expired, i.e. no time left. When the time is positive expiry is today but in the future.

Ronald