getting error in the php code that makes a status inactive for a news posted

[bindiya]

My issue

 

in my php page where i need to show all the news that have the status only active.

 

in my mysql table i have 3 fiels news,posted date and expiry date.

on page load i check the status of the news and displays only the active news.For that i am doing the following code,which gives me error.

 

that is  on page load,

first i select all the news ,update the status inactive for the news which has crossed expiry time

 

i check the status of the news and displays only the active news.For that i am doing the following code,which gives me error.

 

$query1="select time(date_posted),time(expiry_time) from muh_title where status='Active'";

$res=mysql_query($query1);

$row=mysql_fetch_row($res);

$expiry_time=$row[0];$dateposted=$row[1];

$query11="select addtime(time(date_posted),time(expiry_time)) from muh_title";

$res11=mysql_query($query11);

$row11=mysql_fetch_row($res11);

$newtime=$row11[0];

 

$query2="SELECT TIMEDIFF(time(date_posted) ,time(NOW())) FROM muh_title";

$res2=mysql_query($query2);

$row2=mysql_fetch_row($res2);

$diff=$row2[0];

 

//while($newtime){

$query5="select *from muh_main_title where date_posted> date_sub(now(),interval (expiry_time) hour-minute )";

//$query3="update muh_title set status='Inactive' ";

$res3=mysql_query($query5);

 

 

}

$q="select * from muh_title where status='Active' ";

$r= mysql_query($q);

$row= mysql_fetch_row($r);

echo "<font size='6' color='white'><b><marquee direction='left' loop='-1' align='absmiddle' behaviour='scroll' scrolldelay='90'>$row[1]</marquee></b></font>";

?>

 

i am sure some where i have lost the flow of code.

[sooner]

can you show what error it shows...

[BLaZuRE]

You say you have a posted date and expiry date.  Please elaborate.  Date is different from datetime which is different from time.  Also, you have a syntax error for the interval of date_sub.  I'm not sure if date_sub will work the way you want it to.  Timediff is giving you a negative time.  Try troubleshooting yourself.  Make sure you're getting the values you want by 'echo'ing them throughout the code.

[bindiya]

What exactly  my issue is

I have 4 fields comments  (varchar), date_posted(datetime),expiry_time(time),status(varchar) in my database.

i wanted to retieve only comments whose status is active.By adding the date_posted and expiry time i make the status active or inactrive.

 

but when i do the addition  i am not getting the correct answer to make the status inactive. ie if i add

date_posted                            expiry_time

2010-12-31  20:05:00              5:0:0

that comment shuld expire by datetime  2011-01-01 1.05.00

this case i am not getting and so i am not able to do the updations

 

the query i used are

1)SELECT (dateadd( time( date_posted ) , time( expiry_time ) ) )FROM comments

---i get error  check the manual near ' ( time( date_posted ) , time( expiry_time ) ) )'

2)SELECT ADDTIME( 'date_posted', 'expiry_date' ) FROM comments WHERE id =1

LIMIT 0 , 30

------only the time get adde,but the next day will not come.

 

any other way to add hours to a datetime field

 

[sooner]

i can find a DATE_ADD() function or ADDDATE() function...i don't think there is a dateadd() function thats why it is showing error there...

[Pikachu2000]

DATE_ADD() <--- Here it is!

[BLaZuRE]

This worked for me

SELECT (DATE_ADD('2011-01-01 23:01:02' , INTERVAL '5:01:02' HOUR_SECOND ) ) FROM test

 

So try

SELECT (DATE_ADD(date_posted , INTERVAL expiry_time HOUR_SECOND ) ) FROM comments

 

Both should be used as strings, so you shouldn't need the single quotes.

[bindiya]

thanks for all answers.

it worked.I didnt notice that it was a string

by using this command also i got the ans

 

$q1="SELECT addtime( date_posted, expiry_time ) FROM `comments`";

 

and i have set the default time to my local time.,then it worked

 

thank u once again