jkkenzie Posted October 30, 2008 Share Posted October 30, 2008 I would like to capture time when the record was saved and store it in the database the later use it to delete records older that 10 minute or less. Any guide would be appreciated. Is this the right syntax or how best can i do it? $query= "DELETE FROM tblcalc WHERE '$currenttime'-time >'10'" thanks Joseph Quote Link to comment Share on other sites More sharing options...
JasonLewis Posted October 30, 2008 Share Posted October 30, 2008 Store the time() of the entry in the database in a field called something like 'saved_time'. Then delete it like so: $deleteTime = time() - (10 * 60); // 10 is the number of minutes. $query = "DELETE FROM tblcalc WHERE saved_time<='" . $deleteTime . "'"; Pretty sure I got it the right way. Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted October 30, 2008 Author Share Posted October 30, 2008 when i used a variable that saved time like this: $saved_time=time(); then i save in my database, i find my database showing zeros and no value Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted October 30, 2008 Author Share Posted October 30, 2008 I had some variables issue. Its working BUT am getting time in my database showing : 838:59:59 does that look like time? My database field is set the field type to time . is that right? Quote Link to comment Share on other sites More sharing options...
jkkenzie Posted October 30, 2008 Author Share Posted October 30, 2008 my time() is returning 1225356813 what is wrong here? thanks Joe Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 30, 2008 Share Posted October 30, 2008 Nothing is wrong. As stated in manual time() function returns current UNIX timestamp, that is number of seconds passed since Jan 1st 1970. To insert it into database, you need to convert it to one of the formats accepted by mysql. You can do it on PHP side using date() function, or on MySQL side using FROM_UNIXTIME() function. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted November 2, 2008 Share Posted November 2, 2008 Or you could just store it in an integer field. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted November 2, 2008 Share Posted November 2, 2008 You either need to use a mysql DATETIME or a Unix timestamp. Just using a mysql TIME type won't address the rollover that happens at midnight. A mysql DATETIME data type would be your best option because it will allow you to directly use the mysql datetime functions to do anything you want. To store or update the current DATETIME, just use the mysql NOW() function in your query. To DELETE records older than some time INTERVAL - $limit = 10; $query = "DELETE FROM your_table WHERE your_datetime_column < DATE_SUB(NOW(),INTERVAL $limit MINUTE)"; 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.