bindiya Posted January 1, 2011 Share Posted January 1, 2011 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. Quote Link to comment Share on other sites More sharing options...
sooner Posted January 1, 2011 Share Posted January 1, 2011 can you show what error it shows... Quote Link to comment Share on other sites More sharing options...
BLaZuRE Posted January 1, 2011 Share Posted January 1, 2011 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. Quote Link to comment Share on other sites More sharing options...
bindiya Posted January 2, 2011 Author Share Posted January 2, 2011 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 Quote Link to comment Share on other sites More sharing options...
sooner Posted January 2, 2011 Share Posted January 2, 2011 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... Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 2, 2011 Share Posted January 2, 2011 DATE_ADD() <--- Here it is! Quote Link to comment Share on other sites More sharing options...
BLaZuRE Posted January 2, 2011 Share Posted January 2, 2011 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. Quote Link to comment Share on other sites More sharing options...
bindiya Posted January 3, 2011 Author Share Posted January 3, 2011 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.