time left for expiry of an event

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: ( text )

if (mysql_num_rows($rf)){
$num = 0;
while ($f2 = mysql_fetch_array($rf)){
$notice_enddate2 = $f2['notice_enddate'];
$timold=$f2['notice_stamp'];
$notice_enddate3 =explode("-", $notice_enddate2);
$date1=explode("-", $date);
$validity=$notice_enddate3[2]-$date1[2]."days left in expiry";
if($validity<=1){
$current_time=date("H:i:s",time());
echo $time_left=$current_time-$timold;
if($time_left){
$validity="time left :".$time_left;
}
}
}

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

Moderator

4,138 Posts

May 17th, 2008
02:16 PM
#2

Re: time left for expiry of an event

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:

Code: ( text )

$sql="SELECT *,
DATEDIFF(notice_enddate,CURDATE()) AS days_left,
TIMEDIFF(notice_stamp,CURTIME()) AS time_left
FROM table_name";
if (mysql_num_rows($rf)){
$num = 0;
while ($f2 = mysql_fetch_array($rf)){
if ($f2['days_left'] > 0)
$validity=$f2['days_left']." days left in expiry";
else
$validity="time left: ".$f2['time_left'];
}
}

Ronald

__________________
RTFM is an almost extinct art form.

omerbutt

Familiar Sight

193 Posts

May 17th, 2008
03:21 PM
#3

Re: time left for expiry of an event

Quote:

Originally Posted by 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:

Code: ( text )

$sql="SELECT *,
DATEDIFF(notice_enddate,CURDATE()) AS days_left,
TIMEDIFF(notice_stamp,CURTIME()) AS time_left
FROM table_name";
if (mysql_num_rows($rf)){
$num = 0;
while ($f2 = mysql_fetch_array($rf)){
if ($f2['days_left'] > 0)
$validity=$f2['days_left']." days left in expiry";
else
$validity="time left: ".$f2['time_left'];
}
}

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: ( text )

TIMEDIFF(CURTIME(),notice_stamp)

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

Moderator

4,138 Posts

May 17th, 2008
05:31 PM
#4

Re: time left for expiry of an event

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

__________________
RTFM is an almost extinct art form.